Maximum Value with Criteria (MAXIFS)

Excel 2019+MAXIFSArray alt

To find the largest value that meets a condition — the biggest West-region order, the top score in a class — use MAXIFS. It’s MAX with built-in filtering, no array formula required.

Quick formula: for the max of column C where region (B) equals E2:

=MAXIFS(C2:C8, B2:B8, E2)

The max range comes first, then one or more range, criteria pairs — just like SUMIFS.

Functions used (tap for the full reference guide):

MAXIFS MAX

The example

Orders by region. We want the biggest West order.

	A	B	C	E	F
1	Rep	Region	Amount	Region	Max
2	Ana	West	$120	West	$180
3	Ben	East	$300
4	Cy	West	$180
5	Dot	East	$70
6	Eve	West	$150

The formula

The largest West order — note East’s $300 is correctly ignored:

=MAXIFS(C2:C6, B2:B6, "West") // max of {120,180,150} = 180

How it works

MAXIFS filters, then takes the maximum:

The first argument, C2:C6, is the range to take the maximum from.
The pair B2:B6, "West" keeps only West rows.
MAXIFS returns the largest value among those matching rows — 180 — ignoring East’s larger $300.
Add more range, criteria pairs for additional conditions (AND logic).

Before Excel 2019? Use an array formula: =MAX(IF(B2:B6="West", C2:C6)) entered with Ctrl+Shift+Enter.

Try it: interactive demo

Live demo

Pick a region; see the largest order in it.

Region (E2)

Max:

Variations

Max with two conditions

Largest West Widget order:

=MAXIFS(C2:C6, B2:B6, "West", D2:D6, "Widget")

Max within a number range

Biggest order that’s under $200:

=MAXIFS(C2:C6, C2:C6, "<200")

Legacy array version

For Excel 2016 and older (Ctrl+Shift+Enter):

=MAX(IF(B2:B6="West", C2:C6))

Pitfalls & errors

MAXIFS needs Excel 2019+. Older versions show #NAME? — use the MAX(IF()) array formula instead.

Returns 0 when nothing matches. If no row meets the criteria, MAXIFS gives 0, not an error — which can be mistaken for a real value. Check your criteria if you see an unexpected 0.

Ranges must be the same size. The max range and every criteria range need identical dimensions, or you get #VALUE!.

Practice workbook

📊

Download the free Maximum Value with Criteria (MAXIFS) practice workbook

The orders table with MAXIFS (result shown live), the two-condition and legacy array versions, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I find the maximum value with a condition in Excel?

Use =MAXIFS(max_range, criteria_range, criteria), e.g. =MAXIFS(C2:C8, B2:B8, "West") returns the largest West value. Available in Excel 2019 and later.

How do I do MAXIFS in older Excel?

Use an array formula: =MAX(IF(B2:B6="West", C2:C6)), entered with Ctrl+Shift+Enter. It filters to the matching rows and takes the maximum.

Why does MAXIFS return 0?

No rows matched the criteria. MAXIFS returns 0 rather than an error when nothing qualifies, so double-check the criteria values and ranges.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: MAXIFS · MAX