Statistical

MAXIFS Function in Excel

Returns the maximum value among cells that meet multiple criteria.

Syntax

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

Arguments

  • max_range (required): Range to find maximum in
  • criteria_range1 (required): First range to evaluate
  • criteria1 (required): First criteria

Examples

  • =MAXIFS(C2:C10, B2:B10, "East") - Max for East region - Result: Maximum value

Excel MAXIFS function documentation

  • MAXIFS returns the maximum value among cells meeting one or more criteria.
  • Syntax: =MAXIFS(max_range, criteria_range1, criteria1, ...).
  • Pair with [MINIFS](/functions/minifs/) for conditional range extremes.
  • Related: [MAX](/functions/max/), [SUMIFS](/functions/sumifs/), [LARGE](/functions/large/).

MAXIFS syntax

  • max_range numeric values to maximize.
  • Criteria pairs AND together — same rules as SUMIFS wildcards and dates.
  • If no match may return 0 — validate with COUNTIFS.
  • Range sizes must align.

Step-by-step: highest sale in Q2 for rep

  • Step 1 — Amount, Rep, Date columns.
  • Step 2 — =MAXIFS(Amount, Rep, "Ada", Date, ">="&DATE(YEAR(TODAY()),4,1), Date, "<="&DATE(YEAR(TODAY()),6,30)).
  • Step 3 — Who made it: XLOOKUP on amount if unique max.
  • Step 4 — Tie handling — MAXIFS returns value not all rows.
  • Step 5 — Compare pivot Max of Amount by filters.

MAXIFS vs MAX+FILTER vs LARGE

  • MAXIFS — criteria native.
  • MAX(FILTER()) — 365 flexible.
  • LARGE — k-th largest without criteria — combine FILTER first.
  • Problem: zero results vs error — document team convention.

Worked examples to copy

  • One crit: =MAXIFS(C:C, A:A, "East").
  • Two crit: =MAXIFS(price, cat, E1, stock, ">0").
  • Date window: =MAXIFS(val, dt, ">="&start, dt, "<="&end).
  • With cell crit: =MAXIFS(B:B, A:A, G2).

People also ask

  • MAXIFS #VALUE!? — Size mismatch on ranges.
  • MAXIFS text? — max_range must be numeric.
  • MAXIFS no rows? — Returns 0 in Excel — verify.
  • MAXIFS vs DMAX? — Database function with criteria range.

Common errors

  • Returns 0 if no matches
  • Ranges must be same size

Use cases

  • Conditional maximum
  • Regional peaks
  • Category analysis

Frequently asked questions

  • How do I find the highest sale for a specific product? Use =MAXIFS(SalesAmount, ProductName, "Widget"). Add more criteria as needed: =MAXIFS(SalesAmount, ProductName, "Widget", Region, "North") finds the highest Widget sale in the North region.
  • Why does MAXIFS return 0 when there should be a maximum? MAXIFS returns 0 when no cells match the criteria (unlike MAX which would error). Check your criteria for typos, extra spaces, or data type mismatches. Use COUNTIFS with same criteria to verify matches exist.
  • Is MAXIFS available in older Excel versions? MAXIFS was introduced in Excel 2019 and Microsoft 365. For older versions, use array formula: =MAX(IF((criteria_range=criteria), max_range)) entered with Ctrl+Shift+Enter.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use MAXIFS

  • Conditional maximum — common Statistical scenario for MAXIFS.
  • Regional peaks — common Statistical scenario for MAXIFS.
  • Category analysis — common Statistical scenario for MAXIFS.

MAXIFS in the Statistical category

  • Browse all Statistical functions at /categories/statistical/ for related formulas.
  • MAXIFS syntax: =MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)
  • max_range (required): Range to find maximum in
  • criteria_range1 (required): First range to evaluate
  • criteria1 (required): First criteria
  • Confirm MAXIFS arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm MAXIFS arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • MINIFS (/functions/minifs/): Returns the minimum value among cells that meet multiple criteria.
  • MAX (/functions/max/): Returns the largest value in a set of values.
  • SUMIFS (/functions/sumifs/): Adds cells that meet multiple criteria across multiple ranges.
  • AVERAGEIFS (/functions/averageifs/): Returns the average of cells that meet multiple criteria.

Errors to watch for

  • Returns 0 if no matches — review causes on linked error pages in the directory.
  • Ranges must be same size — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter MAXIFS on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

MAXIFS worked examples to copy

  • =MAXIFS(C2:C10, B2:B10, "East") — Max for East region. Expected result: Maximum value.

MAXIFS reference summary for crawlers and offline review

  • MAXIFS belongs to the Statistical category in Excel. Returns the maximum value among cells that meet multiple criteria.
  • Full syntax: =MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...). Open /functions/maxifs/ for parameters, FAQs, and related pages.
  • Common mistakes: Returns 0 if no matches; Ranges must be same size
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.