Excel MAX IF: Max with Criteria Explained

Formulas & functions · Updated November 22, 2026

The direct answer: use the array formula =MAX(IF(range=criteria, max_range)) entered with Ctrl+Shift+Enter in older Excel, or the dynamic-array version =MAX(IF(range=criteria, max_range)) entered normally in Excel 365 and Excel 2021. For a simpler non-array approach, use =MAXIFS(max_range, criteria_range1, criteria1) which works in Excel 2019 and later.

Why MAX with Criteria Isn’t Straightforward

The plain MAX function in Excel ignores text and logical values but has no built-in way to filter rows. If you try =MAX(A2:A10) it returns the highest number in that entire range, regardless of any condition. To find the maximum sales for a specific product, or the latest date for a particular client, you need to combine MAX with a condition. Excel offers several methods, each with its own version requirements and quirks.

Method 1: MAXIFS – The Modern Solution

Introduced in Excel 2019 and also available in Excel 365 and Excel 2021, MAXIFS is the most straightforward. Its syntax is:

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

Example: with product names in A2:A20 and sales in B2:B20, to find the max sales for “Widget”:

=MAXIFS(B2:B20, A2:A20, "Widget")

This returns the highest value in B2:B20 where the corresponding cell in A2:A20 equals “Widget”. You can add multiple criteria pairs, like product and region. Note: MAXIFS is not available in Excel 2016 or earlier, and it does not work in Google Sheets (which uses a different function).

Method 2: Array Formula with MAX and IF

For older Excel versions, the classic array formula works. With the same layout, enter:

=MAX(IF(A2:A20="Widget", B2:B20))

In Excel 2019 and earlier, you must press Ctrl+Shift+Enter to confirm it as an array formula. Excel will wrap it in curly braces { } automatically. In Excel 365 and Excel 2021, dynamic arrays mean you can just press Enter – the formula spills if needed, but here it returns a single value. The IF part evaluates each cell in A2:A20, returns the corresponding B value when true, and FALSE otherwise. MAX then ignores the FALSE values.

Method 3: SUMPRODUCT as a Non-Array Alternative

Before MAXIFS, many users preferred SUMPRODUCT because it doesn’t require Ctrl+Shift+Enter. The formula:

=SUMPRODUCT(MAX((A2:A20="Widget")*B2:B20))

This multiplies the boolean array (TRUE/FALSE) by the sales values, turning TRUE into 1 and FALSE into 0. So the product is either the sales value or 0, and MAX picks the largest. It works in all Excel versions, but be careful: if all sales are negative, the result will be 0 because of the zero multiplication. In that case, use the array formula or MAXIFS instead.

Using MAX with Multiple Criteria

Both MAXIFS and the array formula handle multiple conditions. For MAXIFS, just add more criteria ranges and values:

=MAXIFS(B2:B20, A2:A20, "Widget", C2:C20, "North")

For the array formula, multiply the conditions inside the IF:

=MAX(IF((A2:A20="Widget")*(C2:C20="North"), B2:B20))

Remember to confirm with Ctrl+Shift+Enter in older versions. The multiplication acts as an AND operator – both conditions must be true.

Common Pitfalls and Errors

  • #VALUE! error – Occurs if you forget to press Ctrl+Shift+Enter in older Excel with the array formula. The formula will show a wrong result or error.
  • Text or blank cells – MAX ignores text and blanks in the max_range, but if a criteria cell is blank, it matches empty strings, not blanks. Use "" to match truly empty cells.
  • Wildcards – MAXIFS supports wildcards: * (any sequence), ? (single character), and ~ (escape). For example, =MAXIFS(B2:B20, A2:A20, "W*") finds max for any product starting with W.
  • Case sensitivity – MAXIFS is case-insensitive by default. If you need case-sensitive matching, use the array formula with EXACT: =MAX(IF(EXACT(A2:A20,"widget"), B2:B20)).

MAX vs. LARGE and Other Functions

Sometimes you need the second or third highest value, not the maximum. Use LARGE with criteria:

=LARGE(IF(A2:A20="Widget", B2:B20), 2) (array formula)

Or in Excel 365, use FILTER to get all matching values, then apply MAX or LARGE:

=MAX(FILTER(B2:B20, A2:A20="Widget"))

FILTER is available in Excel 365 and Excel 2021. It’s more flexible because you can see the intermediate array, but if you only need the max, MAXIFS is faster.

Frequently Asked Questions

Can I use MAXIFS with a date range?

Yes, dates are numbers in Excel. For example, to find the latest order date for a customer, use =MAXIFS(C2:C20, A2:A20, "Customer A") where C contains dates. Format the result as a date.

Why does my array formula return 0 instead of the max?

This usually happens when the criteria don’t match any cell, or when using SUMPRODUCT with negative numbers. If no match, MAXIFS returns 0, but the array formula returns 0 as well. To see a more informative result, wrap in IFERROR: =IFERROR(MAXIFS(...), "No match").

Is there a way to use MAX with criteria in Google Sheets?

Google Sheets has MAXIFS as well, but the syntax is the same. However, the array formula with IF works too, but you don’t need Ctrl+Shift+Enter – just press Enter. Note that Google Sheets uses semicolons in some locales, but the comma works in US settings.

Can I use MAXIFS with text criteria that is a cell reference?

Yes, you can reference a cell containing the criteria. For example, with “Widget” in D1, use =MAXIFS(B2:B20, A2:A20, D1). This makes the formula dynamic – change D1 and the result updates.

What if I need the maximum value for each unique item in a list?

Use the UNIQUE function (Excel 365) to list unique items, then use MAXIFS for each. For example, put =UNIQUE(A2:A20) in E2, then in F2: =MAXIFS(B2:B20, A2:A20, E2) and drag down. This creates a summary table automatically.

Performance and Best Practices

For large datasets (tens of thousands of rows), MAXIFS is faster than array formulas because it’s optimized. Array formulas calculate all intermediate arrays, which can slow down the workbook. If you use array formulas, avoid whole-column references like A:A – limit to the actual data range. Also, consider using Excel Tables (Insert > Table) so ranges auto-expand, and use structured references like Table1[Sales] which are clearer and less error-prone.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.