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+.
MAX(IF()) array formula fallback below.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.
| Argument | Type | What 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. |
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.
| Region | Count | Biggest deal |
|---|---|---|
| North | 8 | $13,350.00 |
| South | 6 | $9,250.00 |
| East | 5 | $10,680.00 |
| West | 5 | $7,525.00 |
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.
$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.
| Month | Count | Biggest |
|---|---|---|
| Jan 2026 | 8 | $11,570.00 |
| Feb 2026 | 8 | $8,600.00 |
| Mar 2026 | 8 | $13,350.00 |
04 Wildcards — biggest per product family
Find the biggest deal in a grouped product family.
* 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.
Interactive playground
Try it Live MAXIFS demonstration
Mirrors live cells from the workbook. Change the yellow inputs → the blue answer updates.
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:
| Result | Why it happens | Broken → 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:
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")).
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,394 | 8 | $6,424.27 | $13,350.00 | $2,249.55 |
| South | $27,648 | 6 | $4,608.02 | $9,250.00 | $1,499.70 |
| East | $31,600 | 5 | $6,319.95 | $10,680.00 | $1,625.00 |
| West | $14,800 | 5 | $2,959.93 | $7,525.00 | $1,300.00 |
| Multi-criteria | |||||
| North + Widget A | $6,250 | 2 | $3,124.80 | $3,749.85 | $2,499.75 |
| North + Gizmo Pro | $24,920 | 2 | $12,460.00 | $13,350.00 | $11,570.00 |
| South + Widget A | $4,999 | 2 | $2,499.50 | $3,124.75 | $1,874.25 |
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,100 | 83.1% |
| South | $9,250 | $1,500 | $7,750 | 83.8% |
| East | $10,680 | $1,625 | $9,055 | 84.8% |
| West | $7,525 | $1,300 | $6,225 | 82.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:
| Platform | Supports MAXIFS? | Notes |
|---|---|---|
| Excel 365 (Windows & Mac) | ✓ Yes | Full support |
| Excel 2021 | ✓ Yes | Full support |
| Excel 2019 | ✓ Yes | Introduced here |
| Excel 2016 / 2013 / 2010 / 2007 | ✗ No | Use MAX(IF()) array formula |
| Excel 2003 and earlier | ✗ No | Use MAX(IF()) array formula |
| Excel for the web | ✓ Yes | Full support |
| Excel on iPad & iPhone | ✓ Yes | Full support |
| Google Sheets | ✓ Yes | Same syntax, same behavior |
| LibreOffice Calc | ✓ Yes | Version 5.2+ |
| Apple Numbers | ✓ Yes | Same 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
-
Check your Excel version
MAXIFS needs Excel 2019 or later. On 2016 or earlier, jump to the MAX(IF()) array formula fallback above.
-
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.
-
Write your first criteria pair
criteria_range+criteria. Simple text and numbers work as-is; comparisons go in quotes. -
Add more criteria as needed
Each additional filter is another range/criteria pair. All must match — AND logic. Up to 127 pairs.
-
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
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 →