TL;DR —
MAXIFS(max_range, criteria_range1, criteria1, …)returns the largest value inmax_range, looking only at rows that pass every condition;MINIFSreturns the smallest. They areMAX/MINwith a bouncer at the door — only qualifying rows get counted. They replace the awkward{=MAX(IF(…))}array formula with one ordinary function.
=MAXIFS(C2:C100, A2:A100, "West") ' highest sale in the West
=MINIFS(C2:C100, A2:A100, "West", B2:B100, ">="&E1) ' lowest that also meets a date/threshold
=MAXIFS(C2:C100, D2:D100, "Closed") ' biggest closed deal
If you've ever reached for a Ctrl+Shift+Enter array formula to get "the highest value where region = West," MAXIFS is the function that made that obsolete. It belongs to the same family as SUMIFS, COUNTIFS, and AVERAGEIFS — same criteria syntax, same AND logic — so if you know one, you nearly know all of them.
What you'll learn
- The mental model: MAX/MIN filtered by conditions
- The argument order that trips up
SUMIFusers (result range comes first) - How criteria work — the same string rules as COUNTIF/SUMIFS
- Why they replace the old
{=MAX(IF(…))}array formula - The silent 0 trap when no rows match, and how to catch it
The mental model: MAX/MIN with a filter in front
Plain MAX looks at every number in a range. MAXIFS puts a filter in front of it: "find the largest value — but only in the rows that satisfy these conditions." Everything else about it follows from that one idea.
=MAXIFS(C2:C100, A2:A100, "West", B2:B100, ">100")
' ▲ find the max here ▲ but only where region="West" AND amount>100
Multiple conditions are combined with AND — a row must pass all of them to be considered, exactly like SUMIFS and COUNTIFS. There's no OR option built in; for OR you'd take the MAX of two separate MAXIFS calls, or restructure the data.
The argument order that trips people up
Here's the detail that catches everyone coming from SUMIF or COUNTIF: in the
-IFS family, the range you're measuring comes first, then the
criteria pairs:
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
' ▲ result ▲ where… ▲ equals ▲ and where… ▲ equals
Compare that to the older single-condition functions, where the range to sum is the last, optional argument:
=SUMIF(criteria_range, criteria, [sum_range]) ' result range LAST
=MAXIFS(max_range, criteria_range, criteria) ' result range FIRST
That inconsistency between SUMIF and the -IFS functions is a genuine Excel wart,
and it's the number-one MAXIFS mistake: putting the criteria range where the max
range should be. Memorize it once — -IFS functions always start with the range
you want an answer from — and it stops biting.
Criteria work exactly like COUNTIF and SUMIFS
There's nothing new to learn about the conditions themselves. Criteria are written
as strings, operators go inside the quotes, and a cell reference is joined with &
— the same rules as COUNTIF and
SUMIFS:
=MAXIFS(C2:C100, C2:C100, ">0") ' largest positive value
=MAXIFS(C2:C100, B2:B100, ">"&E1) ' threshold from cell E1
=MAXIFS(C2:C100, A2:A100, "West*") ' wildcard on text criteria
=MINIFS(D2:D100, A2:A100, "West", C2:C100, ">="&DATE(2026,1,1)) ' date-safe
If you already build criteria for COUNTIF or SUMIFS, you build them the same way
here — quotes around operators, & to inject a cell value, DATE() for
locale-safe date comparisons.
Why they replaced the array formula
Before Excel 2019, "conditional max" meant an array formula entered with Ctrl+Shift+Enter:
{=MAX(IF(A2:A100="West", C2:C100))} ' old way — CSE array formula
=MAXIFS(C2:C100, A2:A100, "West") ' new way — one ordinary function
The old form works but carries real baggage: you must remember the curly-brace entry, it's opaque to the next reader, and it's easy to break by editing without re-entering as an array. MAXIFS collapses all of that into a plain function anyone can read and edit. This is the whole point of the function — a result you could compute the clever way, written the boring, durable way instead.
One version note: MAXIFS and MINIFS require Excel 2019 or Microsoft 365. On
Excel 2016 and earlier they don't exist, and you fall back to the {=MAX(IF(…))}
array formula or an AGGREGATE-based approach.
The silent 0 trap
This is the failure mode that quietly corrupts reports. When no rows match the
conditions, MAXIFS and MINIFS return 0 — not #N/A, not blank, not an error.
=MAXIFS(C2:C100, A2:A100, "Antarctica") ' no such region -> 0
If your data can contain negatives (temperatures, profit/loss, adjustments), that
0 is a lie: it looks like a legitimate maximum sitting above your real negative
values, or a legitimate minimum below your positives. Nobody sees an error, so
nobody checks. Guard against it by testing whether any rows matched first:
=IF(COUNTIFS(A2:A100,"West")=0, "No data", MAXIFS(C2:C100, A2:A100, "West"))
Use COUNTIFS with the same conditions to ask "did anything match?" before trusting the number. It's the one check that separates a robust conditional-max formula from one that silently reports 0 as a real value.
The judgment call
MAXIFS and MINIFS are the right answer almost any time you're tempted to write
{=MAX(IF(…))}. The array formula still has exactly two justifications: you're on
Excel 2016 or earlier, or you need OR logic across conditions that the AND-only
-IFS family can't express. Everything else — single or multiple AND conditions,
thresholds from cells, date ranges — belongs to MAXIFS/MINIFS, because a plain
function that the next person can read and edit beats a clever array formula every
time. Just never trust the raw result on data that can go negative until you've
confirmed a match exists. Get those two habits right — result range first, guard
the no-match 0 — and conditional extremes stop being a source of quiet, unflagged
errors.
How ExcelMaster helps
The two ways MAXIFS goes wrong — the reversed argument order and the silent 0 on
no match — are exactly the things a formula never warns you about.
ExcelMaster gets both right automatically: it puts the result range first,
builds criteria with the same & and DATE() rules as your SUMIFS, and wraps the
formula in a match check so an empty result reads "No data" instead of a misleading
0. Ask for "the biggest order from the West region this year" or "the lowest price
per supplier," and it returns a conditional max you can put straight into a report —
including the guard you'd probably have forgotten.
Frequently asked questions
How do I use MAXIFS in Excel?
=MAXIFS(max_range, criteria_range1, criteria1, …) returns the largest value in
max_range from rows meeting all the conditions. For example,
=MAXIFS(C2:C100, A2:A100, "West") gives the highest value in column C where
column A equals "West". MINIFS works identically for the smallest value.
Why does MAXIFS return 0?
Because no rows matched your conditions — MAXIFS and MINIFS return 0 (not an
error) when nothing qualifies. On data with negative values this looks like a real
result. Test with COUNTIFS first:
=IF(COUNTIFS(…)=0,"No data",MAXIFS(…)).
What's the argument order for MAXIFS versus SUMIF?
MAXIFS (like all -IFS functions) puts the result range first:
MAXIFS(max_range, criteria_range, criteria). The older SUMIF puts the result
range last: SUMIF(criteria_range, criteria, [sum_range]). Mixing them up is
the most common mistake.
Can MAXIFS use multiple conditions?
Yes. Add more criteria_range, criteria pairs — they combine with AND, so a
row must satisfy every condition to be considered:
=MAXIFS(C:C, A:A, "West", B:B, ">100"). There's no built-in OR; for OR, take the
MAX of separate MAXIFS calls.
What can I use instead of MAXIFS in Excel 2016?
MAXIFS needs Excel 2019 or Microsoft 365. In Excel 2016 use the array formula
{=MAX(IF(condition, range))} entered with Ctrl+Shift+Enter, or an
AGGREGATE-based formula.
Tested in
Tested in: Excel 365 (Windows 11) — last verified 2026-07-08.
Related guides: Excel SUMIFS · Excel COUNTIFS · Excel AVERAGEIFS · Excel AGGREGATE