Math
The largest amount in one group
Definition
Finding the largest number for one group means giving MAXIFS the numbers, the labels, and the group to match.
Syntax
=MAXIFS(max_range, criteria_range1, criteria1)
Arguments
| Argument | Meaning |
|---|---|
max_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 "high". |
Worked example
A quay log stores tide labels in A2:A5 and depths in B2:B5.
| A: Tide | B: Depth |
|---|---|
| high | 3 |
| low | 11 |
| high | 6 |
| high | 2 |
Formula: =MAXIFS(B2:B5,A2:A5,"high")
Result: 6. high sits in rows 2, 4, and 5, so the largest depth among 3, 6, and 2 is 6.
Practice
Cells A2:A5 hold a, b, a, and a. Cells B2:B5 hold 2, 9, 8, and 4. In C2, write a formula that returns the largest number in B2:B5 where A2:A5 equals a.
- In C2, write a MAXIFS formula that returns the highest value in B2:B5 where A2:A5 equals a.
- C2 should show the highest value in B2:B5 where A2:A5 equals a.
Step 1 of 2
Enter =MAXIFS(B2:B5,A2:A5,"a") in C2.
a appears in A2, A4, and A5, so the highest of 2, 8, and 4 is 8.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
C2 uses MAXIFS.
C2 now shows 8.
The next lesson is Reading a cell from a text address.
Related functions
MAXIFS is the function that returns the largest number for the matching group.
Common mistake
Putting the labels first, as in =MAXIFS(A2:A5,"high",B2:B5): MAXIFS then tries to take the maximum of the labels and cannot return 6.