Conditional function
Excel MAXIFS function
MAXIFS returns the largest number in a range, counting only the rows that meet your conditions: the biggest West sale, the longest open ticket, the latest date for one customer. It works like SUMIFS, with MAX in place of SUM.
=MAXIFS(max_range, criteria_range1, criteria1, ...)Arguments
max_range- The numbers to take the largest from.
criteria_range1- The first range to test. It must be the same size as max_range.
criteria1- The condition, such as "West", ">=100" or ">"&F1.
MAXIFS examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Product | Units | Revenue |
| 2 | West | Desk | 14 | 4200 |
| 3 | East | Chair | 30 | 2850 |
| 4 | West | Chair | 22 | 2090 |
| 5 | North | Desk | 9 | 2700 |
| 6 | East | Desk | 18 | 5400 |
| 7 | West | Lamp | 40 | 1600 |
Download this sheet as an Excel practice file (.xlsx)
Result: 4200
The largest West sale: 4,200.
Result: 5400
Two conditions, both must hold: the biggest East desk sale, 5,400.
Result: 40
The test can be on another column: the most units in a sale under 3,000 is 40.
Result: 0
No row is South, so MAXIFS returns 0, not an error.
How to use MAXIFS
- Click the cell for the result.
- Type =MAXIFS( and select the numbers to compare.
- Add a comma, the range to test, a comma and the condition.
- Add more range, condition pairs if needed, close the bracket and press Enter.
Common MAXIFS mistakes
Reading 0 as a real result
With no matching rows MAXIFS returns 0. Check first: =IF(COUNTIFS(A2:A7,"South")=0,"No sales",MAXIFS(D2:D7,A2:A7,"South")).
Ranges of different sizes
max_range and every criteria range must be the same size, or MAXIFS returns #VALUE!. Select D2:D7 with A2:A7, not A2:A8.
A cell in the condition without &
">F1" tests against the text F1. Join the cell on: ">"&F1.
Using it in Excel 2016 or earlier
MAXIFS needs Excel 2019, 2021, 2024, Microsoft 365 or Excel for the web. In older versions use =MAX(IF(A2:A7="West",D2:D7)), confirmed with Ctrl+Shift+Enter.
MAXIFS FAQ
How do I find the max value with criteria in Excel?
Use =MAXIFS(D2:D7,A2:A7,"West"): the largest value in D2:D7 where column A says West. Add more range, condition pairs for more conditions.
Why does MAXIFS return 0?
No row meets every condition, often because of a typo, an extra space or numbers stored as text in the criteria column. MAXIFS returns 0 instead of an error.
How do I find the latest date for a customer?
Dates are numbers, so =MAXIFS(C2:C50,A2:A50,"Acme") returns the latest date in column C for Acme. Format the cell as a date.
Related functions
MINIFSSmallest value that matchesSUMIFSAdds with several conditionsMAXLargest numberCOUNTIFSCounts with several conditions
Updated