Math
MAXIFS
Definition
MAXIFS returns the largest number among the cells whose matching rows pass every test.
Syntax
=MAXIFS(max_range, criteria_range1, criteria1)
Arguments
| Argument | Meaning |
|---|---|
max_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 medal sheet lists metal names and scores. Names are in A2:A5 and scores are in B2:B5.
| A: Metal | B: Score |
|---|---|
| gold | 11 |
| silver | 20 |
| gold | 8 |
| gold | 14 |
Formula: =MAXIFS(B2:B5,A2:A5,"gold")
Result: 14. The gold scores are 11, 8, and 14, and 14 is the highest.
Practice
Cells A2:A5 hold red, blue, red, and red. Cells B2:B5 hold 4, 9, 2, and 7. In C2, write a formula that returns the largest B value where A equals red.
- In C2, write a MAXIFS formula that returns the highest value in B2:B5 where A2:A5 equals red.
- C2 should show the highest value in B2:B5 where A2:A5 equals red.
Step 1 of 2
Enter =MAXIFS(B2:B5,A2:A5,"red") in C2.
red appears in A2, A4, and A5, so the highest of 4, 2, and 7 is 7.
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 7.
Related functions
MAX has no test. MINIFS is the matching lowest-value function with a test.
Common mistake
Putting the numbers to maximise last, as SUMIF does: MAXIFS needs those numbers in the first argument.