MAXIFS function
MAXIFS returns the largest value in a range, counting only the rows that meet every condition you set, such as the top sale for one region. If no rows match it returns 0, not an error.
Example
Read it as a sentence: find the largest Sales value in O2:O7 where the Region column in M2:M7 says North, which gives 200.
Syntax
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Arguments in [square brackets] are optional.
-
max_range(required) - The cells to take the maximum from.
-
criteria_range1(required) - Range tested against the first condition; same size as max_range.
-
criteria1(required) - The condition, such as "a", ">10" or a cell reference.
-
criteria_range2, criteria2(optional) - Optional extra range and condition pairs; all must be met.
Returns: The largest value in max_range among rows meeting every condition.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=MAXIFS(Data!O2:O7,Data!M2:M7,"North") | 200 | One criterion |
=MAXIFS(Data!O2:O7,Data!M2:M7,"North",Data!N2:N7,"B") | 200 | Two criteria |
=MAXIFS(Data!O2:O7,Data!M2:M7,"West") | 0 | No match returns 0 |
=MAXIFS(Data!O2:O7,Data!M2:M7,{"North";"South"}) | 200 | Array criteria spill |
=MAXIFS(Data!O2:O7,Data!M2:M7,"*th") | 200 | Wildcard |
=MAXIFS(Data!O2:O7,Data!M2:M5,"North") | #VALUE! | Mismatched sizes |
The Data sheet these formulas read
A cell that starts with = holds that formula.
Watch out for
- Ranges must have identical shapes or you get #VALUE!.
- Returns 0 when nothing matches, which can look like a real result.
- Not available in Excel 2016 or earlier perpetual versions.
- Excel refuses the formula outright if you type an array constant such as {1,2,3} where a range is expected; point it at cells instead (measured in Excel 16.0.20527).
Where it works
- Excel
- MAXIFS arrived in Excel 2019. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .