Math & aggregation
How to Use MAXIFS and MINIFS in Excel (with Examples)
MAXIFS and MINIFS return the largest or smallest number in a range that meets one or more conditions: the biggest order in the West region, the lowest score for a "Paid" customer, the highest sale within a date range.
They follow the same pattern as SUMIFS and COUNTIFS: the value range comes first, then each criteria range and its condition. This guide covers single and multiple conditions, date ranges, and the common pitfalls.
MAXIFS and MINIFS syntax
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Argument | Required | What it does |
|---|---|---|
max_range / min_range | Required | The cells to find the maximum (or minimum) value in. |
criteria_range1 | Required | The first range to test against a condition. |
criteria1 | Required | The condition for criteria_range1: "West", ">100", or a cell reference. |
criteria_range2, criteria2 | Optional | Further range/criteria pairs. All conditions must be true (AND logic). |
Note: MINIFS takes the same arguments. Both need Excel 2019 or Microsoft 365; older versions use an array MAX(IF(...)). Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Highest value for one condition
=MAXIFS(C:C, A:A, "West") Looks only at rows where column A is "West" and returns the biggest value from column C. The value range (C) always comes first.
2. Lowest value for one condition
=MINIFS(C:C, A:A, "West") MINIFS works identically to MAXIFS but returns the minimum, so this gives the smallest West-region amount.
3. Highest value with two conditions
=MAXIFS(C:C, A:A, "West", B:B, "Paid") Each extra criteria range/condition pair narrows the rows further. Here only rows that are both West and Paid are considered.
4. Highest value within a date range
=MAXIFS(C:C, D:D, ">="&DATE(2026,1,1), D:D, "<"&DATE(2026,2,1)) Using the same range D twice with two date conditions builds a window: on or after Jan 1 and before Feb 1. Join the operators to DATE() with &.
How to write MAXIFS and MINIFS step by step
- 1
Click the result cell and type =MAXIFS( (or =MINIFS().
- 2
Select the range of numbers you want the largest or smallest value from, then a comma.
- 3
Select the first criteria range (e.g. the region column), then a comma.
- 4
Type the condition in quotes ("West" or ">100"), then a comma.
- 5
Add more criteria range/condition pairs as needed; for a cell-based comparison join it with &, e.g. ">="&E1.
- 6
Close the bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#NAME? | You are on a version older than Excel 2019, which does not have MAXIFS or MINIFS. | Upgrade to Excel 2019/365, or use an array formula: =MAX(IF((A2:A100="West"), C2:C100)) entered with Ctrl+Shift+Enter. |
Returns 0 | No rows match the conditions, so MAXIFS returns 0 instead of an error. | Check the criteria and data types; wrap in IF to show a message, e.g. =IF(COUNTIFS(...)=0, "No match", MAXIFS(...)). |
Operator ignored | A comparison like >E1 was written without joining the cell reference. | Concatenate the operator and reference with &, e.g. ">"&E1. |
#VALUE! | The value range and a criteria range are different sizes. | Make max_range and every criteria range the same number of rows and columns. |
Frequently asked questions
What is the difference between MAX and MAXIFS?
MAX returns the largest number in a range with no conditions. MAXIFS returns the largest number among only the rows that meet your criteria, like the biggest sale in one region.
Do MAXIFS and MINIFS work in older Excel?
No. They were added in Excel 2019 and are in Microsoft 365. In Excel 2016 or earlier, use an array formula such as =MAX(IF(condition, range)) entered with Ctrl+Shift+Enter.
Can I use MAXIFS with dates?
Yes. Use the same date column twice with two conditions to build a window: =MAXIFS(C:C, D:D, ">="&DATE(2026,1,1), D:D, "<"&DATE(2026,2,1)).
Why does MAXIFS return 0 instead of an error?
When no rows match the conditions, MAXIFS returns 0 by design. Use COUNTIFS with the same conditions to detect zero matches and show a custom message instead.