Excel MAXIFS and MINIFS: conditional max and min values

You have a sales sheet with thousands of rows and one question: what’s the biggest deal we closed for the West region last quarter? Plain MAX gives you the largest number in a column — the wrong answer here, because it ignores region and date. That’s what MAXIFS and MINIFS are for. They return the largest or smallest value from a range, but only from the rows that pass every condition you list. Same shape as SUMIFS and COUNTIFS, same rules, one answer per formula.

The syntax borrows from SUMIFS

If you already write SUMIFS and COUNTIFS, MAXIFS is the same shape with a different first argument. The value column comes first, then the criteria pairs.

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

max_range (or min_range) is the column you want the biggest or smallest number from. Each criteria_range/criteria pair filters the rows before Excel picks the extreme value. You can chain up to 126 pairs, per the Microsoft MAXIFS reference, but two or three is where most real formulas land.

Both functions arrived in Excel 2019 and ship in every version since — Microsoft 365, Excel 2021, Excel 2024, and the Mac equivalents. Older builds don’t have them; the fallback lives at the end of this article.

A first example: biggest and smallest sale by region

Say row 1 holds the headers Region, Salesperson, Amount, Date, and rows 2 through 200 hold individual deals. A miniature version:

To find the largest West-region deal:

=MAXIFS(C2:C200, A2:A200, "West")

C2:C200 is the amount column, A2:A200 is the region column, and "West" is the criterion. Swap MAXIFS for MINIFS to get the smallest. Add a second pair to narrow down to one rep:

=MAXIFS(C2:C200, A2:A200, "West", B2:B200, "Priya")

Both criteria have to pass on the same row for that row’s amount to enter the pool. Text matches are case-insensitive, so "west" and "West" return the same result.

Text, wildcards, and dates as criteria

The third argument accepts more than a literal string. Three patterns cover almost every real formula.

Wildcards. Use * for any run of characters and ? for a single character. To grab the biggest deal from any region starting with “West”:

=MAXIFS(C2:C200, A2:A200, "West*")

Comparison operators. Wrap the operator in quotes and glue in a reference with &. Filtering to deals above a threshold in cell K2:

=MAXIFS(C2:C200, C2:C200, ">"&K2)

Date ranges. Combine two comparison criteria over the same date column. Biggest West-region sale in Q1 2026:

=MAXIFS(C2:C200, A2:A200, "West", D2:D200, ">="&DATE(2026,1,1), D2:D200, "<"&DATE(2026,4,1))

The one syntax rule everyone forgets: the operator goes inside the quotes, the value stays outside, and & joins them.

=MAXIFS(C:C, D:D, ">K2")

=MAXIFS(C:C, D:D, ">"&K2)

The zero-on-no-match trap

When no row matches, MAXIFS and MINIFS both return 0 — not #N/A, not blank. That is fine when zero is impossible in your data (a positive-only sales amount, for instance), and it silently poisons every dashboard where zero is a valid answer: temperature deltas, KPI variances, refund adjustments, portfolio returns.

Wrap the call so the two cases don’t look the same. Add a COUNTIFS gate:

=IF(COUNTIFS(A2:A200,"West",B2:B200,"Priya")=0,
     "No match",
     MAXIFS(C2:C200,A2:A200,"West",B2:B200,"Priya"))

On Microsoft 365, LET keeps the max and the count in one pass so you don’t repeat the criteria:

=LET(
   m, MAXIFS(C2:C200,A2:A200,"West"),
   c, COUNTIFS(A2:A200,"West"),
   IF(c=0,"No match",m))

The specific formula matters less than the habit. Any time MAXIFS feeds a dashboard cell, ask whether a real zero and a “nothing matched” zero should look the same. If not, guard the formula.

Fetching the row that produced the max

MAXIFS returns the value, not the invoice number, the rep, or the date behind it. For that you pair it with INDEX and XMATCH:

=INDEX(B2:B200,
       XMATCH(1,
              (A2:A200="West") * (C2:C200 = MAXIFS(C2:C200,A2:A200,"West")),
              0))

Read it inside-out: MAXIFS finds the biggest West amount; the multiplication builds a 1/0 array flagging the row where region is West and amount equals that max; XMATCH finds the first 1; INDEX returns the salesperson’s name on that row.

On Excel 2019 there’s no XMATCH. Use MATCH entered with Ctrl+Shift+Enter — the array-formula workaround does the same job with older syntax.

When ranges don’t line up — the #VALUE! fix

The most common MAXIFS bug is a range mismatch. C2:C200 and A2:A201 return #VALUE!. So does C2:C200 paired with A:A, because Excel refuses to compare 199 cells against a full column of 1,048,576.

Three ways to keep them synchronized, in order of preference:

1. Convert the source to an Excel Table (Ctrl+T). Deals[Amount] and Deals[Region] are guaranteed the same length and grow with new rows.
2. If you must use raw ranges, keep every reference literal and identical: C2:C200 on the value side, A2:A200 on every criteria side.
3. If you use full-column references, use them everywhere: C:C and A:A, never A:A paired with B2:B200.

The Table option is the one that survives new data. The other two are one refactor away from breaking.

If you’re stuck on Excel 2016 or older

MAXIFS doesn’t exist before Excel 2019, and the same is true for MINIFS. The classic fallback is MAX or MIN wrapped around an IF array formula:

{=MAX(IF((A2:A200="West")*(D2:D200>=DATE(2026,1,1)), C2:C200))}

Enter it with Ctrl+Shift+Enter; the curly braces appear automatically. IF returns the amount when both conditions are true and FALSE otherwise, and MAX ignores logical values, so only the qualifying amounts contribute.

For MIN, swap the outer function — same array-formula shape. If you find yourself writing multi-criteria lookups every week on an old build, the honest recommendation is to upgrade. MAXIFS, FILTER, and the dynamic array family are the reason to move.

Wrapping up

Start with the SUMIFS shape you already know: value column first, criteria pairs second. Be defensive about two things — the silent zero when nothing matches, and the #VALUE! when your ranges drift out of sync. Guard every dashboard cell with a COUNTIFS check the first time you drop MAXIFS into it. When you need the row behind the number, pair with INDEX and XMATCH. Everything else — wildcards, date windows, multi-column filters — is the same pattern with a different third argument.

• ✓ Value column is the first argument, criteria pairs follow
• ✓ Every criteria range matches the size and shape of the max range
• ✓ Comparison operator inside quotes, cell reference concatenated with &
• ✓ Dashboard cells wrap MAXIFS with COUNTIFS to distinguish a real zero from no match
• ✓ Row-behind-the-number lookups use INDEX + XMATCH against the max