Excel MAX with Criteria: Formula & Examples

Formulas & functions · Updated November 22, 2026

The quickest way to find the maximum value in Excel based on one or more criteria is to use the MAXIFS function: =MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). For example, =MAXIFS(B2:B20, A2:A20, "West") returns the largest sales figure in B2:B20 where the region in A2:A20 is “West”. This function is available in Excel 2019, Excel 2021, and Microsoft 365; if you use an older version, you can build the same result with an array formula using MAX and IF.

Why MAXIFS Is the Go-To Function

Before Excel 2019, finding a maximum with conditions required an array formula like {=MAX(IF(A2:A20="West", B2:B20))}, entered with Ctrl+Shift+Enter. That still works in modern Excel, but MAXIFS is simpler, easier to read, and recalculates faster. It follows the same logic as SUMIFS and AVERAGEIFS, so if you already know those, the syntax feels familiar.

The function accepts up to 126 pairs of criteria ranges and conditions. Each condition can be a number, text, a cell reference, or an expression like ">"&D1. The max range and all criteria ranges must be the same size and shape, otherwise Excel returns a #VALUE! error.

How to Use MAXIFS: Step-by-Step

Assume you have a table with headers in row 1: Product names in A2:A20, Region in B2:B20, and Sales in C2:C20. To get the highest sale in the East region:

  1. Click an empty cell, e.g., E2.
  2. Type =MAXIFS(.
  3. Select C2:C20 as the max_range.
  4. Select B2:B20 as criteria_range1.
  5. Type "East" (or reference a cell containing the region).
  6. Close the parenthesis and press Enter.

The result is a single number. If no cells meet the criteria, MAXIFS returns 0 (not an error).

Using MAXIFS with Multiple Criteria

You can add more conditions. For instance, to find the maximum sale for the “Widget” product in the “East” region:

With products in A2:A20, regions in B2:B20, and sales in C2:C20:

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

This returns the largest value in C2:C20 where column A equals “Widget” AND column B equals “East”. Both conditions must be true for a row to be considered.

Alternatives for Older Excel Versions

If you are on Excel 2016 or earlier, MAXIFS doesn’t exist. Use an array formula instead:

With the same layout, select a cell and type:

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

Then press Ctrl+Shift+Enter. Excel wraps it in curly braces {}. The multiplication * acts as an AND operator. This formula works in all versions, but it requires the special key combination. On a Mac, press Command+Shift+Enter.

Common Errors and How to Fix Them

  • #VALUE! – Criteria ranges are not the same size as the max range. Check dimensions.
  • #NAME? – The function name is misspelled or your Excel version doesn’t support MAXIFS. Use the array alternative.
  • Returns 0 unexpectedly – No rows match the criteria. Check for typos, extra spaces, or number stored as text.
  • Wrong result with text criteria – Excel is case-insensitive but exact-match by default. Use wildcards like "E*" for partial matches.

MAXIFS with Wildcards and Logical Operators

You can use wildcards in text criteria: ? matches any single character, * matches any sequence. For example, =MAXIFS(C2:C20, A2:A20, "Wid*") finds the max for any product starting with “Wid”.

For numeric conditions, use comparison operators inside quotes: =MAXIFS(C2:C20, B2:B20, ">1000") returns the highest sale where the value in B2:B20 is greater than 1000. To reference a cell, concatenate: =MAXIFS(C2:C20, B2:B20, ">"&D1).

MAXIFS vs. Other MAX Approaches

You might also see SUMPRODUCT used to find a max with criteria, but it’s clunky and requires array handling. The MAXIFS function is the cleanest. A pivot table can also show the max per group, but it’s not a formula and requires manual refresh. If you need the second largest value, use LARGE with IF in an array formula, or the AGGREGATE function which supports options to ignore errors.

For example, to get the second highest sale in the East region (Excel 2010+):

=AGGREGATE(14, 6, C2:C20/(B2:B20="East"), 2)

Here, 14 is the LARGE function, 6 ignores errors, and the division creates errors for non-matching rows.

Frequently Asked Questions

Can MAXIFS handle blank cells or errors? Yes, MAXIFS ignores blank cells in the max range. If a criteria cell is blank, it matches empty cells. If the max range contains errors, MAXIFS will return an error unless you use AGGREGATE. For a robust solution, use =AGGREGATE(14, 6, C2:C20/(B2:B20="East"), 1) which ignores errors.

How do I find the max with OR logic (e.g., East OR West)? MAXIFS only does AND logic. For OR, use an array formula: =MAX(IF((B2:B20="East")+(B2:B20="West"), C2:C20)) entered with Ctrl+Shift+Enter. The plus sign acts as OR.

Does MAXIFS work in Excel for the web or Mac? Yes, MAXIFS is available in Excel for Microsoft 365 on Windows, Mac, and the web, as well as Excel 2019 and 2021. Syntax is identical.

Why does my MAXIFS return 0 when there are matches? If the max range contains text or blanks, MAXIFS treats them as 0. Also, if your criteria range contains numbers stored as text, they won’t match numeric criteria. Use the VALUE function or clean your data.

Can I use a cell reference for the criteria? Absolutely. Instead of typing “East”, use =MAXIFS(C2:C20, B2:B20, E1) where E1 contains the region name. This makes the formula dynamic.

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.