Math & Statisticsintermediate
Average values by multiple criteria with AVERAGEIFS
=AVERAGEIFS(D2:D100,B2:B100,"East",C2:C100,"Q1")
Excel how-to guide
How to maxifs conditional max in Excel
Find the highest sale in a specific region, the top score for one department, or the maximum order amount for a given customer.
Step-by-step instructions
How to maxifs conditional max — 3 steps
1
Enter the range of values you want to find the maximum in.
2
Add a criteria range and criteria value pair for each condition.
3
MAXIFS returns the largest value where all conditions are true.
Example data
Worked example
| Rep | Region | Sale |
|---|---|---|
| Alice | East | 1200 |
| Bob | West | 950 |
| Carol | East | 1800 |
| David | North | 2100 |
Common mistakes
Errors to watch out for
MAXIFS returns 0 when no match is found
Unlike AVERAGEIFS, MAXIFS returns 0 (not an error) when no rows match. Use IF(MAXIFS(...)=0,"No match",MAXIFS(...)) to distinguish zero values from no match.
MAXIFS is not available
MAXIFS requires Excel 2019 or Excel 365. In older versions, use an array formula — =MAX(IF(B2:B100="East",D2:D100)) entered with Ctrl+Shift+Enter.
FAQ
Frequently asked questions
Is there a MINIFS function as well?
Yes — MINIFS works identically to MAXIFS but returns the smallest matching value. Both functions were introduced in Excel 2019.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Math & Statisticsintermediate
SUMIFS for monthly revenue by category
=SUMIFS($D:$D,$B:$B,$G$2,$C:$C,">="&DATE(2026,1,1),$C:$C,"<"&DATE(2026,2,1))