Excel function · Statistics
MAXIFS
Returns the largest number whose row meets one or more conditions.
What it does
MAXIFS finds the highest value among the rows that meet every condition. Its partner MINIFS finds the lowest in the same way.
Syntax
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,
Arguments
- max_range
- The numbers to take the largest from.
- criteria_range
- A range to test, the same size as the max range.
- criteria
- The condition for that range.
Example
What was the biggest single food bill?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Biggest food bill | |
| 2 | Food | 18 | 25 | |
| 3 | Hotel | 90 | ||
| 4 | Food | 25 | ||
| 5 | Train | 40 |
The formula in D2:
=MAXIFS(B2:B5,A2:A5,"Food")Result: 25
The Food rows have 18 and 25, so the result is 25. The hotel and train rows are bigger but don't match.
Common mistakes
- Expecting
#N/Awhen nothing matches. It returns 0, which can look like a real answer. - Using it in a file that must open in old Excel versions, which don't have it.
In other Excel languages
Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.
| Excel language | Function name | Separator |
|---|---|---|
| English | MAXIFS | , |
| Deutsch | MAXWENNS | ; |
| Français | MAX.SI.ENS | ; |
| Español (España) | MAX.SI.CONJUNTO | ; |
| Español (México) | MAX.SI.CONJUNTO | , |
| 日本語 | MAXIFS | , |
| 한국어 | MAXIFS | , |
| 繁體中文 | MAXIFS | , |
| ไทย | MAXIFS | , |
| ខ្មែរ | MAXIFS | , |
| Tiếng Việt | MAXIFS | ; |