MAX and MIN look at the whole range. Often you need the highest sale for one region only, or the earliest date for one customer. MAXIFS and MINIFS add conditions, the same way SUMIFS does.
Syntax
=MAXIFS(max_range, criteria_range1, criteria1, ...)
=MINIFS(min_range, criteria_range1, criteria1, ...)
Highest sale in one region
Regions in A, sales in C:
=MAXIFS(C2:C500, A2:A500, "North")
Lowest price for one product after a date
=MINIFS(D2:D500, B2:B500, "Pen", C2:C500, ">="&F1)
F1 holds the start date. Join the operator and the cell with &.
Latest date for each customer
Dates are numbers, so MAXIFS gives the most recent one: =MAXIFS(Date, Customer, A2). Format the cell as a date.
Common mistakes
- All ranges must be the same size.
- If nothing matches, the result is 0, not an error. Check with COUNTIFS first if 0 is a valid value in your data.
- MAXIFS and MINIFS need Excel 2019 or Microsoft 365.
Worked example
Region in A2:A100 and sales in B2:B100. =MAXIFS(B2:B100,A2:A100,"North") returns the highest sale in North. =MINIFS(B2:B100,A2:A100,"North") returns the lowest.
Common mistakes
- Using it in Excel 2016 or older. It needs 2019 or Microsoft 365.
- Putting the max range after the criteria. It comes first.
- Getting 0 when no row matches and thinking 0 is real.
Frequently asked questions
What if there is no match?
MAXIFS and MINIFS return 0.
Can I use multiple conditions?
Yes. Add more range and criteria pairs.
What can I use in older Excel?
=MAX(IF(A2:A100="North",B2:B100)) confirmed with Ctrl+Shift+Enter.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: MAXIFS, SUMIFS, COUNTIFS.
Start Excel practice → Case studies
