MAXIFS in Excel: Syntax, 5 Examples, Fallback for Older Excel | Sheets & Cells
Function · Math & Statistical

MAXIFS — Find the Biggest Value with Multiple Criteria

The fourth piece of the IFS family. When SUMIFS gives you totals and AVERAGEIFS gives you means, MAXIFS surfaces the extremes: biggest deal per rep, largest expense per category, longest project. The go-to function for top-performer analysis, worst-case exposure, and peak-value queries. Excel 2019+.

⚠️
Requires Excel 2019, Excel 365, or Excel for the web
MAXIFS shipped with Excel 2019, later than the SUMIFS/COUNTIFS/AVERAGEIFS trio (which are 2007+). Excel 2016 and earlier show a NAME error. For those users, we document a working MAX(IF()) array formula fallback below.
Quick answer
MAXIFS returns the largest number in a range that meets one or more criteria. Each row is considered only if ALL criteria match (AND logic). Same syntax pattern as SUMIFS with "max" instead of "sum."
Syntax
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
Working example
=MAXIFS(Amount, Region, "North", Product, "Widget A") → Returns the LARGEST Amount where Region = "North" AND Product = "Widget A". With our sample data: $3,749.85 — Michael Chen's Feb 27 transaction.
✓
Universal support since Excel 2019
Excel 2019, 2021, 365, Excel for the web, Google Sheets, LibreOffice, Apple Numbers. Works identically across every modern platform.
📗 Free MAXIFS & MINIFS example workbook
9 sheets · same 24-row sales log as SUMIFS/COUNTIFS/AVERAGEIFS · includes MAXIFS + MINIFS side-by-side · unique Full IFS Family Reconciliation sheet
Download .xlsx (free) Open in Sheets
Category
Math & Statistical
Difficulty
Beginner → Intermediate
Excel version
2019+
Max criteria
127 pairs
127
Max criteria pairs
5
Worked examples
4th
IFS family piece
8/10
Templates using it

Syntax breakdown

MAXIFS has five arguments to start (three at minimum), then any number of additional criteria pairs. Same structure as SUMIFS/AVERAGEIFS with max_range as the first argument.

ArgumentTypeWhat it does
max_range REQUIRED The column to search for the maximum value. First argument. Non-numeric cells in this range are ignored (not treated as 0).
criteria_range1 REQUIRED First column to filter on. Must be the same size as max_range.
criteria1 REQUIRED Value or expression to match. Text ("North"), number (100), cell reference (A2), or comparison (">100").
criteria_range2 OPTIONAL Second filter column. Add up to 126 more pairs (127 total).
criteria2 OPTIONAL Second criteria expression. All criteria must be true for a row to be considered.
The zero-match gotcha: MAXIFS returns 0 when no rows match — not an error like AVERAGEIFS. Which is fine IF your data can't include 0. But if $0 is a legitimate value (a discount, a refunded deal), you can't distinguish "no matches" from "matched with value 0." Wrap in a COUNTIFS check when this matters.

Five working examples

Every example uses the same 24-row sales log as the other four IFS functions. Q1 2026 data — grand max = $13,350 (Michael's March Gizmo Pro), grand min = $1,300.

01 Basic MAXIFS — biggest deal per region

Find the largest single transaction in each region. Reveals ceiling by geography.

RegionCountBiggest deal
North8$13,350.00
South6$9,250.00
East5$10,680.00
West5$7,525.00
=MAXIFS(Amount, Region, "North")
→ Returns $13,350.00 — the biggest deal was in North (Michael Chen's March Gizmo Pro)

The insight: West is capped at $7,525 while North reaches $13,350 — a 77% ceiling gap. Combined with region counts, MAXIFS surfaces where the biggest deals happen, guiding rep territory decisions.

02 Multiple criteria — biggest deal per segment

Find the largest transaction in a specific segment. Multiple filters combined with AND logic.

=MAXIFS(Amount, Region, "North", Product, "Widget A")
→ Returns $3,749.85 — biggest North+Widget A deal (Michael, Feb 27)
=MAXIFS(Amount, Region, "North", Product, "Gizmo Pro")
→ Returns $13,350.00 — the champion segment
=MAXIFS(Amount, Salesperson, "Emma Thompson")
→ Returns $11,570.00 — Emma's biggest single deal all quarter
🔗 The IFS family in perfect agreement
For North + Widget A: SUMIFS says $6,249.60 total across COUNTIFS = 2 transactions, AVERAGEIFS = $3,124.80 average. MAXIFS returns $3,749.85 (biggest), MINIFS returns $2,499.75 (smallest). And $3,749.85 + $2,499.75 = $6,249.60 — reconciles perfectly to SUMIFS. All five IFS functions agree.

03 Date range — biggest deal per month

Track peak deal size over time. Reveals when the ceiling shifts up or down.

MonthCountBiggest
Jan 20268$11,570.00
Feb 20268$8,600.00
Mar 20268$13,350.00
=MAXIFS(Amount, Date, ">="&DATE(2026,2,1), Date, "<="&DATE(2026,2,28))
→ Returns $8,600.00 — Feb's biggest deal (Emma's Bundle X)
The story only MAXIFS tells: Feb had 8 transactions (same as Jan and Mar), but its ceiling dropped 26% below January's. This is the "we lost the whales" signal — worth investigating whether a specific customer went dormant or a rep left. AVERAGEIFS would show the shift as a smaller average; only MAXIFS confirms the top of the funnel actually shrank.
=MAXIFS(Amount, Date, ">="&EOMONTH(TODAY(),-1)+1, Date, "<="&EOMONTH(TODAY(),0))
→ Biggest deal in the current month, self-updating.

04 Wildcards — biggest per product family

Find the biggest deal in a grouped product family.

=MAXIFS(Amount, Product, "Widget*")
→ Returns $3,749.85 — biggest Widget-anything deal
=MAXIFS(Amount, Product, "Gizmo*")
→ Returns $13,350.00 — Gizmos hit the highest ceiling
=MAXIFS(Amount, Product, "*Pro*")
→ Returns $13,350.00 — anything with "Pro" in the name

* matches any characters, ? matches one. Wildcards work only on TEXT criteria_ranges.

05 Comparison operators — biggest within a bucket

Find the biggest deal above or below a threshold. Useful for tiered analysis.

=MAXIFS(Amount, Amount, "<5000")
→ Returns $3,749.85 — biggest deal in the small-tier bucket
=MAXIFS(Amount, Region, "<>North")
→ Returns $10,680.00 — biggest deal outside North (Aisha's March Gizmo Pro)
=MAXIFS(Amount, Amount, ">"&$C$22)
→ Dynamic threshold from cell — put a number in C22, filter updates live

Interactive playground

Try it Live MAXIFS demonstration

Mirrors live cells from the workbook. Change the yellow inputs → the blue answer updates.

Input · Region
North
Output · Biggest deal
$13,350.00
=MAXIFS(XAmount, XRegion, "North")
Input · Salesperson
Emma Thompson
Output · Biggest deal
$11,570.00
=MAXIFS(XAmount, XPerson, $C$24)

Download the workbook to try the live dropdown filters and the Full IFS Family Reconciliation sheet.

Common errors and how to fix them

MAXIFS is straightforward but has a few gotchas. Six common scenarios:

ResultWhy it happensBroken → Fix
NAME error You're on Excel 2016 or earlier. MAXIFS didn't exist until Excel 2019. MAXIFS(Amount, Region, "North") // NAME error {=MAX(IF(Region="North", Amount))} // Ctrl+Shift+Enter
0 (silent) No rows match the criteria — MAXIFS returns 0 rather than an error. MAXIFS(Amount, Region, "Nowhere") // returns 0 =IF(COUNTIFS(Region,"Nowhere")=0, "No data", MAXIFS(Amount, Region, "Nowhere"))
VALUE error max_range and criteria_range are different sizes. MAXIFS(G6:G29, C6:C15, "North") MAXIFS(G6:G29, C6:C29, "North")
Wrong max Missing & concatenation causing zero matches on a comparison. MAXIFS(Amount, Amount, ">C2") // literal text MAXIFS(Amount, Amount, ">"&C2)
0 (silent) Trailing spaces in criteria or data (invisible but breaks match). MAXIFS(Amount, Region, "North ") MAXIFS(Amount, Region, TRIM("North "))
VALUE error Reference to a closed workbook (MAXIFS cannot read closed external files). MAXIFS('[Closed.xlsx]Sheet1'!A:A, ...) Open the source workbook, or import via Power Query

Fallback for Excel 2016 and earlier

If you're stuck on Excel 2007-2016, you don't have MAXIFS. But you can achieve the same result with a legacy MAX(IF()) array formula:

{=MAX(IF(Region="North", Amount))} ' Single criterion
{=MAX(IF((Region="North")*(Product="Widget A"), Amount))} ' Multiple criteria (AND)
How to enter an array formula: type the formula WITHOUT the curly braces, then press Ctrl+Shift+Enter (not just Enter). Excel adds the braces automatically. In Excel 365 the braces aren't needed — dynamic arrays handle this natively — but the formula still works.

The (...) * (...) multiplication trick creates AND logic: it multiplies boolean arrays where TRUE=1, FALSE=0, so only rows matching BOTH conditions get through. For OR logic, use addition instead: ((Region="North") + (Region="South")).

📗 Every example above, in one workbook
Same 24-row log as the whole IFS family · MAXIFS/MINIFS side-by-side · Spread pattern · Full IFS reconciliation.
Download maxifs-minifs-examples-2026.xlsx

The full IFS family, reconciled

MAXIFS completes a complete statistical picture when combined with its four siblings. Here's the same segments run through all five IFS functions — the killer view every dashboard should aspire to:

All 5 IFS functions on the same filters

SUMIFS answers "how much?" · COUNTIFS answers "how many?" · AVERAGEIFS answers "what size each?" · MAXIFS answers "biggest?" · MINIFS answers "smallest?" — together they describe any segment completely.

Filter SUMIFS COUNTIFS AVERAGEIFS MAXIFS MINIFS
By region
North$51,3948$6,424.27$13,350.00$2,249.55
South$27,6486$4,608.02$9,250.00$1,499.70
East$31,6005$6,319.95$10,680.00$1,625.00
West$14,8005$2,959.93$7,525.00$1,300.00
Multi-criteria
North + Widget A$6,2502$3,124.80$3,749.85$2,499.75
North + Gizmo Pro$24,9202$12,460.00$13,350.00$11,570.00
South + Widget A$4,9992$2,499.50$3,124.75$1,874.25
The identity check: for any 2-row segment, MAXIFS + MINIFS should equal SUMIFS. North+Widget A: $3,749.85 + $2,499.75 = $6,249.60 ✓. North+Gizmo Pro: $13,350 + $11,570 = $24,920 ✓. This is the family reconciliation for pair-sized segments — if it doesn't match, one of your five formulas has a subtle criteria bug.

The Spread pattern — MAX minus MIN

Deal-size volatility per category. Big spread = high variability; small spread = consistent deal sizes. This one metric — impossible without MAXIFS AND MINIFS together — drives forecast accuracy:

Region MAX MIN Spread % of MAX
North$13,350$2,250$11,10083.1%
South$9,250$1,500$7,75083.8%
East$10,680$1,625$9,05584.8%
West$7,525$1,300$6,22582.7%

Every region has ~83% spread — meaning the smallest deal is only ~17% of the biggest. Highly variable revenue everywhere. Products with lower spread (like Bundle X at 33%) are more predictable for forecasting.

The IFS family — five functions, one pattern

MAXIFS is the fourth member of the IFS family. All five share the same criteria model:

Meet the family

Every function below uses the same (aggregate_range, criteria_range1, criteria1, ...) pattern. Only the aggregation verb changes.

SUMIFS, COUNTIFS, and AVERAGEIFS ship with Excel 2007+. MAXIFS and MINIFS require Excel 2019 or 365.

Related functions

Excel version compatibility

MAXIFS is the newer half of the IFS family — Excel 2019 or later. Older versions get the NAME error:

PlatformSupports MAXIFS?Notes
Excel 365 (Windows & Mac)✓ YesFull support
Excel 2021✓ YesFull support
Excel 2019✓ YesIntroduced here
Excel 2016 / 2013 / 2010 / 2007✗ NoUse MAX(IF()) array formula
Excel 2003 and earlier✗ NoUse MAX(IF()) array formula
Excel for the web✓ YesFull support
Excel on iPad & iPhone✓ YesFull support
Google Sheets✓ YesSame syntax, same behavior
LibreOffice Calc✓ YesVersion 5.2+
Apple Numbers✓ YesSame syntax

When to use MAXIFS vs. alternatives

Use MAXIFS when…

  • You need the biggest value in a filtered subset. Top performer per team, largest expense per category, longest project.
  • You're on Excel 2019+. Otherwise, use the MAX(IF()) array formula fallback.
  • You want clean, readable formulas. MAXIFS reads like English; array formulas hide their logic.

Use MAX instead when…

  • You want the biggest value in a whole range with no filters. =MAX(A2:A1000).

Use LARGE instead when…

  • You want the 2nd, 3rd, or Nth biggest — not just the top. =LARGE(range, 2) gives you the runner-up.

Use MAX(IF()) array formula when…

  • You're on Excel 2016 or earlier where MAXIFS doesn't exist.
  • You need OR logic across criteria (MAXIFS is AND-only).

How MAXIFS actually works

The algorithm

MAXIFS walks through each row of your criteria_ranges in parallel. For every row, it checks: do ALL criteria pairs match? If YES, compare that row's max_range value to a running maximum. At the end, return the largest value it saw. If no rows matched, return 0.

Because it's AND-only, the order of criteria pairs doesn't affect the result. Put your most restrictive criteria first for readability; Excel's optimizer handles evaluation order.

Text criteria — case and whitespace

  • Case-insensitive: "NORTH", "north", and "North" all match.
  • Wildcards: * for any characters, ? for one character.
  • Whitespace is significant — trailing spaces silently break matches. Use TRIM() to clean data.

Numeric criteria — operators inside quotes

  • Operators go INSIDE the criteria string: ">100", not >100.
  • To reference a cell in a comparison, use &: ">"&C2.

Non-numeric cells in max_range

Empty cells, text values, and errors in the max_range are IGNORED — they don't participate in the max calculation. This means MAXIFS won't crash if your amount column has a stray "TBD" string; it just skips those rows.

Performance notes

MAXIFS is fast — comparable to SUMIFS in speed. Three drivers:

  • Range size. Narrow ranges ($A$2:$A$5000) beat whole-column references ($A:$A).
  • Number of criteria pairs. Each additional pair is another comparison per row.
  • Data-type consistency. Mixed types in criteria_range force Excel to coerce on every comparison.

When to switch to a PivotTable

If you're computing dozens of MAXIFS in a grid (max by region × product × month), a PivotTable is faster and more maintainable. Right-click a value field → Value Field Settings → Max.

How to write a MAXIFS from scratch

  1. Check your Excel version

    MAXIFS needs Excel 2019 or later. On 2016 or earlier, jump to the MAX(IF()) array formula fallback above.

  2. Identify the max_range

    What column has the numbers you want the maximum from? That's your FIRST argument — same slot as SUMIFS's sum_range.

  3. Write your first criteria pair

    criteria_range + criteria. Simple text and numbers work as-is; comparisons go in quotes.

  4. Add more criteria as needed

    Each additional filter is another range/criteria pair. All must match — AND logic. Up to 127 pairs.

  5. Guard against the zero-match trap

    If your data could legitimately include 0, wrap in a COUNTIFS check: =IF(COUNTIFS(...)=0, "No data", MAXIFS(...)). Distinguishes empty results from real zeros.

Functions used with MAXIFS

🎁 Grab the free MAXIFS & MINIFS workbook
Same 24-row sales log · MAXIFS/MINIFS side-by-side · The Spread pattern · Full IFS Family Reconciliation.
Download maxifs-minifs-examples-2026.xlsx

Frequently asked questions

Why does MAXIFS give a NAME error?

You're on Excel 2016 or earlier. MAXIFS was introduced in Excel 2019 — older versions don't recognize the function name and return #NAME?. Upgrade to Excel 2019/365, or use the {=MAX(IF(...))} array formula fallback shown above.

What's the difference between MAX and MAXIFS?

MAX takes a single range and returns the biggest value in it — no filters. MAXIFS takes an aggregation range PLUS one or more filter conditions and returns the biggest value in rows that meet all conditions. If you don't need filters, use MAX; it's faster and simpler.

How do I get the 2nd biggest instead of the biggest?

MAXIFS only returns the top. For the 2nd/3rd/Nth biggest, use LARGE. Combined with an array formula: {=LARGE(IF(Region="North", Amount), 2)} returns the 2nd-biggest North transaction.

Why is my MAXIFS returning 0?

Either no rows match your criteria (the "zero-match" behavior — MAXIFS returns 0 when no rows qualify) OR the biggest matching value truly is 0. To distinguish, run =COUNTIFS(...) with the same criteria; if it returns 0, you have no matches.

Can MAXIFS handle OR logic?

No — MAXIFS is AND-only. For OR logic, use the array formula fallback: {=MAX(IF((Region="North")+(Region="South"), Amount))}. The + creates OR, and the outer MAX returns the biggest matching value.

How do I find the biggest deal in a date range?

Same pattern as SUMIFS — two criteria on the Date column: =MAXIFS(Amount, Date, ">="&DATE(2026,1,1), Date, "<="&DATE(2026,3,31)). The & concatenation is essential.

Does MAXIFS work in Google Sheets?

Yes, with identical syntax. Google Sheets has supported MAXIFS since 2018 — reliably works across both platforms.

Is MAXIFS case-sensitive?

No — MAXIFS is case-insensitive for text comparisons. "NORTH", "north", and "North" all match. For case-sensitive filtering, use the MAX(IF()) approach with EXACT.

What if my max_range has text or errors?

Non-numeric cells in max_range are IGNORED. MAXIFS won't crash on stray strings, dates, or errors — it just skips them. If ALL matching rows are non-numeric, MAXIFS returns 0.

Can MAXIFS pull from a closed workbook?

No — same limitation as the whole IFS family. MAXIFS returns #VALUE! when its ranges reference a closed external workbook. Open the source workbook, or import via Power Query.

How does MAXIFS reconcile with the other IFS functions?

For any 2-row segment, MAXIFS + MINIFS = SUMIFS. Example: North+Widget A has 2 transactions — MAXIFS returns $3,749.85, MINIFS returns $2,499.75, sum = $6,249.60 which matches SUMIFS exactly. This identity check surfaces criteria bugs across your dashboard.

Which templates use MAXIFS?

The same 8 templates that use the rest of the IFS family — dashboards routinely need max values (biggest expense, top deal, longest project). Budget Tracker, Expense Report, Inventory Tracker, Sales Dashboard, KPI Dashboard, Employee Attendance, Timesheet, and Project Timeline all use MAXIFS for peak-value queries.

Templates that use MAXIFS

The same 8 of 10 templates that use the rest of the IFS family — MAX values complete the story alongside totals, counts, and averages:

Skip the syntax. Ask in plain English.

The Sheets & Cells AI Add-in writes MAXIFS, MINIFS, and the whole IFS family — plus the MAX(IF()) fallback for older Excel — right inside Excel. Type "biggest Q1 deal for Widget A in North" and get the working formula, ready to paste.

Try the AI Add-in →