MAXIFS
Finds the largest number among the rows that match the conditions: the biggest order in each city.
MAXIFS(max_range, criteria_range1, criteria1, ...)- max_range
- The numbers among which to find the largest, for example amounts in B2:B8.
- criteria_range1
- Where to check the first condition, for example cities in A2:A8.
- criteria1
- The first condition. You can then add a second range and a second condition the same way.
Return value
The largest number among the rows that matched all conditions. If there are no such rows, the function returns 0.
Examples
The biggest order in a city
fx
A
B
C
D
E
F
1
2
3
4
5
6
7
8
9
10
City
Amount, $
City
Maximum, $
Boston
7200
Boston
Denver
3400
Denver
Boston
2600
Austin
Austin
8100
Denver
5100
Boston
9800
Austin
4300
Sheet1
Details
MAXIFS takes the range in which to look for the maximum first, and then pairs of “where to check and what condition”. There can be one condition or several.
If no row matches the conditions, the function returns 0, not an error. It’s available in Excel 2019, 2021 and Microsoft 365.
Common mistakes
- A typo in the condition gives 0, not an error. It’s easy to mistake that zero for a real result.
- The order of arguments: the range of numbers comes first, as in SUMIFS.