FormuladrillMAXIFS and MINIFS
MAXIFS(max_range, criteria_range1, criteria1, ...) returns the largest value in max_range for rows that meet all the conditions; MINIFS does the same for the smallest. They return 0 when no row matches. Both need Excel 2019 or Microsoft 365.
Syntax
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) MINIFS(min_range, criteria_range1, criteria1, ...)| Part | What it is |
|---|---|
max_range / min_range | The numbers to pick from. |
criteria_range1 | The first range to test, same size as max_range. |
criteria1 | The condition: "North", ">0", "<>" or a cell reference. |
Examples
=MAXIFS(C2:C100, A2:A100, "North")Result: The largest sale in column C from the North region
=MINIFS(C2:C100, A2:A100, "North", C2:C100, ">0")Result: The smallest positive North sale
The second condition keeps zeros and blanks out.
=MAXIFS(D2:D100, B2:B100, "Shipped")Result: The most recent shipped date in column D
Dates are numbers, so MAXIFS gives the latest one. Format the cell as a date.
=MAX(IF(A2:A100="North", C2:C100))Result: The same as the first example in Excel 2016 or earlier
Confirm with Ctrl+Shift+Enter in versions without dynamic arrays.
Common mistakes
- A result of 0 means nothing matched, not that the maximum is 0. Check the criteria spelling and spaces.
- #NAME?: Excel 2016 or earlier does not have MAXIFS. Use MAX(IF(...)) as an array formula.
- #VALUE!: criteria ranges are not the same size as max_range.
- Numbers stored as text are ignored, so the result may skip the real maximum.
Practice MAXIFS and MINIFS
These drills check your answer on live data, free, no account needed.
- Best sale in a regionPut the highest sale for the region in E2 into E3.
Questions
How do I find the max with a condition in Excel?
=MAXIFS(values, criteria_range, criterion). In Excel 2016 or earlier use =MAX(IF(criteria_range=criterion, values)) entered with Ctrl+Shift+Enter.
How do I find the latest date for a customer?
=MAXIFS(date_column, customer_column, "Acme"). Format the result as a date.
Related
- SUMIFSHow to use SUMIFS in Excel to add values that meet two or more conditions, with examples for region and status, date ranges, not-equal and wildcards.
- AVERAGEIFHow to use AVERAGEIF and AVERAGEIFS in Excel to average only the cells that match a condition, with examples for one region, positive values and two criteria.
- COUNTIFSHow to use COUNTIFS in Excel to count rows where two or more conditions are all true, with examples for text plus number, date ranges and not-equal.
- RANKHow to rank values in Excel with RANK.EQ and RANK.AVG, descending or ascending, with examples, how ties work, a tie-breaker and LARGE for the nth value.