ExcelFunctions.net

Search Site:

The Excel Minifs function returns the minimum value from a subset of values that are specified according to one or more criteria.

The syntax of the function is:

MINIFS( min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Where the function arguments are:

min_range | - | An array of numeric values (or a range of cells containing numeric values), from which you want to return the minimum value if the criteria are satisfied. |

criteria_range1 | - | An array of values (or range of cells containing values) to be tested against criteria1. (This array must all have the same length as the min_range). |

criteria1 | - | The condition to be tested against the values in criteria_range1. |

[criteria_range2, criteria2], [criteria_range3, criteria3], ... | - | Optional further arrays of values to be tested and the respective conditions to test. |

Notes:

- The Minifs function can handle up to 126 pairs of criteria_range and criteria arguments.
Each of the supplied criteria can be either:

- a numeric value (which may be an integer, decimal, date, time, or logical value) (e.g. 5, 01/01/2017, TRUE)

- a text string (e.g. "Address", "Friday")

- an expression (e.g. ">1", "<>0").

In text-related criteria, you can use the wildcards:

? - to match any single character

* - to match any sequence of characters.

- If a criteria is a text string or an expression, this
__must__be supplied to the Minifs function in quotes. - The Minifs function is
__not__case-sensitive. So, for example, when comparing the values in the criteria_range against the criteria, the text strings "TEXT" and "text" will be considered to be a match. - The Minifs function was first introduced in Excel 2016 and so is not available in earlier versions of Excel. It is also not available in Excel 2016 for Mac.

The spreadsheet below shows the quarterly sales figures for 3 sales representatives.

The Minifs function can be used to find the minimum sales figure for any quarter, area or sales rep.

This is shown in the examples below.

A | B | C | D | |
---|---|---|---|---|

1 | Quarter | Area | Sales Rep. | Sales |

2 | 1 | North | Jeff | $223,000 |

3 | 1 | North | Chris | $125,000 |

4 | 1 | South | Carol | $456,000 |

5 | 2 | North | Jeff | $322,000 |

6 | 2 | North | Chris | $340,000 |

7 | 2 | South | Carol | $198,000 |

8 | 3 | North | Jeff | $310,000 |

9 | 3 | North | Chris | $250,000 |

10 | 3 | South | Carol | $460,000 |

11 | 4 | North | Jeff | $261,000 |

12 | 4 | North | Chris | $389,000 |

13 | 4 | South | Carol | $305,000 |

To find the minimum sales figure during quarter 1:

=MINIFS( D2:D13, A2:A13, 1 )

which gives the result *$125,000*.

In this example, the Excel Minifs function identifies rows where the value in column A is equal to 1, and returns the minimum value from the corresponding values in column D.

I.e. the function finds the minimum of the values $223,000, $125,000 and $456,000 (from cells D2, D3 and D4).

Again, using the data spreadsheet above, we can also use the Minifs function to find the minimum sales figure for "Jeff", during quarters 3 and 4:

=MINIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

This formula returns the result *$261,000*.

In this example, the Excel Minifs function identifies rows where:

- The value in column A is greater than 2

- The entry in column C is equal to "Jeff"

and returns the minimum of the corresponding values in column D.

I.e. this formula finds the minimum of the values $310,000 and $261,000 (from cells D8 and D11).

For further examples of the Excel Minifs function, see the Microsoft Office website.

If you get an error from the Excel Minifs function, this is likely to be one of the following:

Common Errors

#VALUE! | - | Occurs if the supplied min_range and criteria_range arrays do not all have equal length. |

#NAME? | - | Occurs if you are using an older version of Excel (pre-2016), that does not support the Minifs function. |