
MAXIFS and MINIFS: Conditional Maximums and Minimums (Multiple Criteria, Ties, and Related Values)
“What is the highest sales amount for the Seoul Keyboard category?” “What is the lowest unit price for that month?” — Use MAXIFS / MINIFS to find the answer cleanly, then retrieve the row’s details at the same time.
Basic Syntax
| Function | Syntax | Description |
|---|---|---|
| MAXIFS | =MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Returns the maximum among values that meet the criteria |
| MINIFS | =MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Returns the minimum among values that meet the criteria |
TIP: All ranges must be the same size. For date and number comparisons, join an operator string and a value with &, as in ">="&cell.
8 Practical Examples
1) Maximum sales for Region = Seoul
=MAXIFS(Sales[Amount], Sales[Region], "Seoul")
2) Minimum unit price for Category = Keyboard
=MINIFS(Price, Category, "Keyboard")
3) Maximum sales for a date range (current month)
=MAXIFS(C:C, B:B, ">="&EOMONTH(F2,-1)+1, B:B, "<="&EOMONTH(F2,0))
4) Maximum amount where text contains “Pro”
=MAXIFS(C2:C100, A2:A100, "*Pro*")
5) Minimum lead time with multiple criteria (Region = Seoul AND Status = Delivered)
=MINIFS(LeadTime, Region, "Seoul", Status, "Delivered")
6) Minimum value excluding 0 and blanks
=MINIFS(C2:C100, C2:C100, "<>", C2:C100, "<>0")
7) List all rows tied for the maximum value (Microsoft 365)
=LET(mx, MAXIFS(C:C, B:B, "Seoul"),
FILTER(A:C, (B:B="Seoul")*(C:C=mx)))
8) Top N values (pseudo-LARGEIFS) — Microsoft 365
=TAKE(SORT(FILTER(C2:C100, B2:B100="Seoul"),,-1), N)
Retrieve Other Information from the Extreme-Value Row
An extreme value alone is not always enough. Use XLOOKUP/INDEX as shown below to retrieve the product name, order number, or other information from the same row.
Product name for the maximum value (first matching row)
=LET(mx, MAXIFS(Sales[Amount], Sales[Region], "Seoul"),
XLOOKUP(mx, Sales[Amount], Sales[Item], , 0))
Order number for the minimum lead time (including criteria)
=LET(mn, MINIFS(LeadTime, Region,"Seoul", Status,"Delivered"),
XLOOKUP(1, (LeadTime=mn)*(Region="Seoul")*(Status="Delivered"), OrderID))
Use FILTER when you need all tied rows.
Alternatives for Older Versions
In versions without MAXIFS or MINIFS, use array formulas or AGGREGATE.
MAXIFS alternative
=MAX(IF(Region="Seoul", Amount)) // Ctrl+Shift+Enter
MINIFS alternative with multiple criteria
=MIN(IF((Region="Seoul")*(Category="Keyboard"), Price))
Ignore errors and blanks with AGGREGATE
=AGGREGATE(14, 6, Amount/(Region="Seoul"), 1) // 14=Large, 6=Ignore errors
Mistakes, Performance, and Accuracy Checks
- Mismatched range sizes → Keep the number of rows and columns the same in every range.
- Numbers stored as text → Convert with VALUE or standardize the source format.
- Handling ties → Decide whether you need only the value or a list of rows (XLOOKUP vs. FILTER).
- Overusing full-column references → In large files, use tables or precise ranges for better speed.
- Clean criteria first → Remove noise with TRIM, CLEAN, and SUBSTITUTE before comparing to improve accuracy.
Summary
| Goal | Typical Formula |
|---|---|
| Conditional maximum | MAXIFS(value_range, criteria_range, criteria...) |
| Conditional minimum | MINIFS(value_range, criteria_range, criteria...) |
| Details from the extreme-value row | XLOOKUP(extreme_value, value_range, return_range) |
| Older versions | MAX/MIN(IF(criteria,value)) array formula |
FAQ
How are blank cells handled?
MAXIFS and MINIFS ignore blank cells by default. To exclude 0, add "<>0" as a criterion.
Are text criteria case-sensitive?
No. If you need case-sensitive matching, use EXACT with FILTER or an array formula.