How to Use the MAXIFS Function in Google Sheets
=MAXIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]).
What MAXIFS does
MAX finds the largest number in a range, with no conditions. MAXIFS adds conditions: "the highest sale in the East region", or "the highest score among rows with quantity under 3". Like AVERAGEIFS and SUMIFS, it starts with the range to evaluate and then takes pairs of criteria range and criterion. A MINIFS function works the same way for the smallest value.
MAXIFS syntax
=MAXIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
range: the cells from which the maximum is determined.criteria_range1: the cells over whichcriterion1is evaluated.criterion1: the pattern or test applied to that range.criteria_range2, criterion2, ...: optional further pairs. The result uses the intersection of all criteria.
Per Google's help page, MAXIFS returns 0 if none of the criteria are satisfied, and all ranges must be the same size, otherwise the function returns a #VALUE error. A 0 result therefore does not always mean the real maximum is 0.
A step-by-step example
Here are four rows of region, sales and quantity in cells A1:C5.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | Qty |
| 2 | East | 10 | 1 |
| 3 | West | 20 | 2 |
| 4 | East | 30 | 3 |
| 5 | West | 40 | 4 |
=MAXIFS(B2:B5, A2:A5, "East")
This returns 30, the larger of the two East sales (10 and 30). With a second condition:
=MAXIFS(B2:B5, A2:A5, "East", C2:C5, "<3")
This returns 10. Here is why: only the first row is East with a quantity below 3, so 10 is the only candidate.
Common mistakes
- Ranges of different sizes. The result is #VALUE.
- Reading 0 as a real maximum. It is also what you get when nothing matches.
- Forgetting quotation marks around text and comparison conditions.
Try it yourself
Use the table above (A1:C5). Type your formula into the box, starting with =, then press Check.
Exercise 1: Biggest East sale
Write a formula that returns the largest value in B2:B5 for the rows where the region in A2:A5 is East.
Exercise 2: Two conditions
Write a formula that returns the largest value in B2:B5 for East rows where the quantity in C2:C5 is less than 3.
What to learn next
- AVERAGEIFS: average with several conditions.
- SUMIFS: add up values that meet several conditions.
- MIN and MAX: find the smallest and largest values.
Syntax and rules in this guide were checked against Google's MAXIFS help page. See all guides.