Math
The smallest amount in one group
Definition
Finding the smallest number for one group means giving MINIFS the numbers, the labels, and the group to match.
Syntax
=MINIFS(min_range, criteria_range1, criteria1)
Arguments
| Argument | Meaning |
|---|---|
min_range |
The column of numbers. This argument comes first. |
criteria_range1 |
The column of group labels to scan. |
criteria1 |
The group to match. Text needs double quotes, for example "north". |
Worked example
A depot logs bay labels in A2:A5 and wait times in B2:B5.
| A: Bay | B: Wait |
|---|---|
| north | 9 |
| south | 2 |
| north | 4 |
| north | 7 |
Formula: =MINIFS(B2:B5,A2:A5,"north")
Result: 4. north sits in rows 2, 4, and 5, so the smallest wait among 9, 4, and 7 is 4.
Practice
Cells A2:A5 hold yes, no, yes, and yes. Cells B2:B5 hold 8, 1, 3, and 5. In C2, write a formula that returns the smallest number in B2:B5 where A2:A5 equals yes.
- In C2, write a MINIFS formula that returns the lowest value in B2:B5 where A2:A5 equals yes.
- C2 should show the lowest value in B2:B5 where A2:A5 equals yes.
Step 1 of 2
Enter =MINIFS(B2:B5,A2:A5,"yes") in C2.
yes appears in A2, A4, and A5, so the lowest of 8, 3, and 5 is 3.
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 3.
The next lesson is The largest amount in one group.
Related functions
MINIFS is the function that returns the smallest number for the matching group.
Common mistake
Putting the labels first, as in =MINIFS(A2:A5,"north",B2:B5): MINIFS then tries to take the minimum of the labels and cannot return 4.