To find the maximum value in Excel based on one or more criteria, use MAXIFS (for Excel 2019 and Microsoft 365) or an array formula with MAX and IF (for older versions). For example, =MAXIFS(B2:B100, A2:A100, "East") returns the largest sales figure in column B where column A equals “East”.
Why MAXIFS Is the Modern Default
MAXIFS was introduced in Excel 2019 and is available in Microsoft 365. It works like SUMIFS or AVERAGEIFS: you give it a range to evaluate, then pairs of criteria ranges and conditions. The syntax is:
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
For instance, with product names in A2:A20 and quantities in B2:B20, to find the largest quantity for “Widget”:
=MAXIFS(B2:B20, A2:A20, "Widget")
This returns 85 if the largest Widget quantity is 85. You can add more criteria: =MAXIFS(B2:B20, A2:A20, "Widget", C2:C20, ">10") finds the max quantity for Widgets with a price over 10. MAXIFS supports wildcards (* for any sequence, ? for a single character) and logical operators like ">=" or "<".
How to Use MAXIFS Step by Step
- Select the cell where you want the result.
- Type
=MAXIFS(. - Select the range that contains the values you want to find the max of (e.g., B2:B100).
- Select the first criteria range (e.g., A2:A100) and type the criteria (e.g.,
"East"or a cell reference likeE1). - Add more criteria ranges and conditions as needed, separating each pair with a comma.
- Close the parenthesis and press Enter.
On Mac, the steps are identical. In Excel for the web, MAXIFS works the same way. If you use Excel 2016 or earlier, MAXIFS is not available; use the array formula alternative below.
Array Formula Alternative for Older Excel
In Excel 2016 and earlier, you can use an array formula with MAX and IF. With the same ranges, enter:
=MAX(IF(A2:A20="Widget", B2:B20))
After typing, press Ctrl+Shift+Enter (Windows) or Command+Shift+Enter (Mac) to make it an array formula. Excel will show curly braces around it in the formula bar. This formula returns the maximum B value where A equals "Widget". For multiple criteria, nest IFs: =MAX(IF(A2:A20="Widget", IF(C2:C20>10, B2:B20))).
Array formulas can be slower on large datasets because they calculate over every cell in the range. If you have tens of thousands of rows, MAXIFS is more efficient. Also, array formulas require the Ctrl+Shift+Enter step; if you forget, you get a #VALUE! error or a wrong result.
Using MAXIFS with Cell References and Dates
Instead of typing criteria directly, reference a cell. For example, with a date in D1, to find the max sales after that date:
=MAXIFS(B2:B100, A2:A100, ">"&D1)
The ampersand (&) concatenates the operator with the cell value. For dates, ensure the cell contains a real date (not text). For text criteria with wildcards, "*Widget*" matches any cell containing "Widget". To match an exact blank or non-blank, use "" or "<>".
Common Problems and Fixes
- #VALUE! error – Usually means you pressed Enter instead of Ctrl+Shift+Enter for an array formula, or your criteria range and max range have different sizes. Check that all ranges have the same number of rows.
- Returns 0 instead of a value – This happens when no rows meet the criteria. MAXIFS returns 0 if no match, while an array formula returns 0 as well. To return something else, wrap in IFERROR:
=IFERROR(MAXIFS(...), "No match"). - Criteria with text and numbers – If your criteria column contains numbers stored as text, you may need to use
"Widget"or"123"as text, or convert the column to numbers. - MAXIFS not available – If you see #NAME?, your Excel version is older than 2019. Use the array formula or upgrade.
- Case sensitivity – MAXIFS is not case-sensitive by default. If you need case-sensitive matching, use an array formula with EXACT:
=MAX(IF(EXACT(A2:A20,"widget"), B2:B20)).
Comparing MAXIFS with Other Functions
MAXIFS is the direct answer for conditional max. But sometimes you need a different approach:
- MAX with FILTER (Microsoft 365) –
=MAX(FILTER(B2:B20, A2:A20="Widget"))works but requires Microsoft 365 or Excel 2021. It is more flexible if you also want to return the row or other data. - SUMPRODUCT-based max –
=SUMPRODUCT(MAX((A2:A20="Widget")*B2:B20))is an old non-array formula that works in all versions, but it can be tricky with text and returns 0 if no match. - PivotTable – For quick analysis, a PivotTable with a Max value field can show the max per category without formulas, but it is not dynamic for a single cell lookup.
For finding the max across multiple columns with criteria, use MAXIFS with a range that spans multiple columns, but ensure the criteria ranges match the shape. For example, =MAXIFS(B2:C20, A2:A20, "Widget") returns the max from B2:C20 where A equals "Widget".
Frequently Asked Questions
How do I find the max value with multiple criteria in Excel?
Use MAXIFS with multiple criteria pairs. For example, =MAXIFS(B2:B20, A2:A20, "Widget", C2:C20, ">10") finds the max B where A is "Widget" and C is greater than 10. Each criteria range must be the same size as the max range.
Can I use MAXIFS with a cell reference for the criteria?
Yes, just reference the cell directly. For example, =MAXIFS(B2:B20, A2:A20, E1) uses the value in E1 as the criteria. For operators, concatenate: =MAXIFS(B2:B20, A2:A20, ">"&E1).
Why does MAXIFS return 0 when there is no match?
MAXIFS returns 0 if no row meets all criteria. To show a custom message, wrap it in IFERROR: =IFERROR(MAXIFS(...), "No match"). For an array formula, you can use =IF(COUNTIFS(...)=0, "No match", MAX(IF(...))).
Does MAXIFS work in Excel 2016?
No, MAXIFS was introduced in Excel 2019. In Excel 2016, use the array formula =MAX(IF(...)) with Ctrl+Shift+Enter, or use SUMPRODUCT as a non-array alternative.
How do I find the max value based on a date range?
Use two criteria: one for greater than or equal to the start date, one for less than or equal to the end date. Example: =MAXIFS(B2:B100, A2:A100, ">="&D1, A2:A100, "<="&D2) where D1 is start date and D2 is end date.
Can MAXIFS handle wildcards?
Yes, use * for any sequence and ? for a single character. For example, =MAXIFS(B2:B20, A2:A20, "W*") finds the max for any text starting with W. Wildcards work only with text criteria, not numbers.
Related
- Excel Transpose: How to Quickly Flip Data
- How to Rename a Cell in Excel: A Complete Guide
- Excel Replace Only in Selected Cells: 2 Ways
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel TRIM Formula Examples for Cleaning Messy TextIn Excel for Microsoft 365, use =TRIM(A2) to remove leading and trailing ordinary spaces from the text in…
- SUMIF a Date Between Two Dates in ExcelIf your dates are in A2:A100, the values to add are in B2:B100, and the start and end…
- Excel Insert a New Row and Keep FormulasTo insert a row in Excel and keep formulas, select the row number where the new row should…
- MID and FIND in Excel: Extract Text Between DelimitersTo extract text between parentheses in Excel, use MID with FIND to locate the opening and closing characters.…
- Excel SUMIF for Text: Match and Join Text Values EasilyExcel has no SUMIF function that adds text strings, but in Microsoft 365 you can use TEXTJOIN with…
- How to Filter Dates in Excel by Month, Year, or RangeTo filter dates in Excel, select a cell in your data, choose Data > Filter, open the arrow…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.