Math
MINIFS
Definition
MINIFS returns the smallest number among the cells whose matching rows pass every test.
Syntax
=MINIFS(min_range, criteria_range1, criteria1)
Arguments
| Argument | Meaning |
|---|---|
min_range |
The numbers to compare when the test on that row passes, such as B2:B5. |
criteria_range1 |
The cells compared with the criterion. |
criteria1 |
The value that counts as a match. Wrap text in double quotes. |
Worked example
A harbour log lists berth names and wait times. Names are in A2:A5 and times are in B2:B5.
| A: Berth | B: Wait |
|---|---|
| dock | 22 |
| pier | 9 |
| dock | 18 |
| dock | 15 |
Formula: =MINIFS(B2:B5,A2:A5,"dock")
Result: 15. The dock times are 22, 18, and 15, and 15 is the lowest.
Practice
Cells A2:A5 hold east, west, east, and east. Cells B2:B5 hold 10, 3, 14, and 6. In C2, write a formula that returns the smallest B value where A equals east.
- In C2, write a MINIFS formula that returns the lowest value in B2:B5 where A2:A5 equals east.
- C2 should show the lowest value in B2:B5 where A2:A5 equals east.
Step 1 of 2
Enter =MINIFS(B2:B5,A2:A5,"east") in C2.
east appears in A2, A4, and A5, so the lowest of 10, 14, and 6 is 6.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
C2 uses MINIFS.
C2 now shows 6.
Related functions
MIN has no test. IF can flag a row, but MINIFS applies the test inside the function.
Common mistake
Putting the numbers to minimise last, as SUMIF does: MINIFS needs those numbers in the first argument.