Excel MAXIFS Function
MAXIFS returns the largest value whose matching cells satisfy one or more conditions.
Use it for conditional maximums, such as highest sale by region and product, latest qualifying date, or top score for one team and status.
MAXIFS syntax & arguments
Syntax
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
-
1
max_range
RequiredThe cells to evaluate for the maximum when all criteria pass.
-
2
criteria_range1
RequiredThe first range of cells to test.
-
3
criteria1
RequiredThe condition that cells in
criteria_range1must meet. It can be a number, text, cell reference, expression such as">100", or wildcard pattern. -
4
criteria_range2, criteria2
Optional RepeatableAdditional range-and-criteria pairs. Each extra criteria range must line up with
max_range, and only values that meet every criterion are considered for the maximum.
Example
=MAXIFS(D2:D50, B2:B50, "East", C2:C50, "Complete")
Return the largest value in D2:D50 where B2:B50 is East and the matching cell in C2:C50 is Complete.
MAXIFS caveats
-
The max range comes first
Like SUMIFS and AVERAGEIFS, MAXIFS starts with the result range, then uses range-and-criteria pairs.
-
Criteria ranges must align
Every criteria range should cover the same rows or columns as
max_rangeso each test lines up with the value being compared. -
All conditions are AND logic
A value can become the maximum only when every criteria pair passes for the same row or position.
-
Operators usually need quotes
Criteria such as
">100"or"<>Cancelled"must be written as text. -
No matching numeric values can return 0
If no qualifying numeric value is found, MAXIFS can return
0, which may look like a real maximum.
Need the lowest matching value? Use MINIFS when the same conditions should return a minimum instead.
Intro MAXIFS practice problem
Solve the intro problem directly here, or open it on its own page.
Advanced MAXIFS practice problems
Use MAXIFS alongside other Excel functions in realistic, less-prescriptive challenges.
