The fastest way to find a maximum value in Excel that meets multiple criteria is to use the MAXIFS function, which is available in Excel 2019 and Microsoft 365. For example, with products in column A, regions in column B, and sales in column C, the formula =MAXIFS(C2:C100, A2:A100, "Widget", B2:B100, "East") returns the highest sales figure for Widgets sold in the East region. If your Excel version doesn’t support MAXIFS, you can use an array formula with MAX and IF, or a SUMPRODUCT-based approach, both of which are covered below.
Background: Why You Need MAXIFS
Excel’s built-in MAX function finds the single largest number in a range, but it has no built-in way to filter that range by conditions. Before MAXIFS was introduced in Excel 2019, users had to combine MAX with IF in an array formula, or use a workaround with SUMPRODUCT. These older methods still work, but they are less intuitive and can be slower on large datasets. The MAXIFS function was designed specifically to solve this problem, and it works similarly to SUMIFS and AVERAGEIFS, which have been around since Excel 2007.
How MAXIFS Works
The syntax of MAXIFS is:
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
It takes a range to find the maximum from (max_range), and then pairs of criteria ranges and criteria. You can add up to 126 range/criteria pairs. Each criteria can be a number, text, a cell reference, or an expression (like “>100”). The function evaluates all criteria simultaneously, so a cell in the max range is only considered if it meets every condition.
Example: Suppose you have a table with dates in A2:A20, salespeople in B2:B20, and sales amounts in C2:C20. To find the highest sale made by “Smith” after January 1, 2025, you could use:
=MAXIFS(C2:C20, B2:B20, "Smith", A2:A20, ">"&DATE(2025,1,1))
This returns the largest value in C2:C20 where the corresponding B cell is “Smith” and the A cell is a date greater than January 1, 2025. Note the use of the ampersand (&) to concatenate the greater-than operator with the date.
Array Formula Alternative (Older Excel)
If you are using Excel 2016 or earlier, you can use an array formula. With the same layout (dates in A2:A20, names in B2:B20, sales in C2:C20), enter:
=MAX(IF(B2:B20="Smith", IF(A2:A20>DATE(2025,1,1), C2:C20)))
This is an array formula, so you must press Ctrl+Shift+Enter on Windows or Command+Shift+Enter on Mac to enter it. Excel will wrap it in curly braces { }. The inner IF returns an array of sales values that meet both conditions, and MAX returns the largest. This method works in all versions, but it can be slower on very large datasets because it processes arrays in memory.
SUMPRODUCT Workaround (No Array Entry)
Another alternative that doesn’t require array entry is using SUMPRODUCT with MAX in a non-array way. However, a common trick is to use SUMPRODUCT to return the maximum by multiplying conditions with the values. For example:
=SUMPRODUCT(MAX((B2:B20="Smith")*(A2:A20>DATE(2025,1,1))*C2:C20))
This works because the conditions return 1 (TRUE) or 0 (FALSE). When multiplied by the sales values, only the rows meeting both conditions contribute their sales value; others become 0. MAX then finds the largest of those products. This formula is entered normally (just press Enter). However, it has a subtle flaw: if all sales values are positive, the result is correct. If some sales could be negative, the zeros from non-matching rows could incorrectly become the maximum. In that case, you’d need a more complex array formula. So use this only when you’re sure the max range contains only positive numbers.
How to Choose the Right Method
Here’s a quick guide to picking the right approach:
- Excel 2019 or Microsoft 365: Use
MAXIFS. It’s the most readable and fastest. - Excel 2016 or older: Use the array formula with
MAXandIFif you’re comfortable with Ctrl+Shift+Enter. - If you want to avoid array entry: Use the
SUMPRODUCTtrick, but only when the max range is all positive numbers. - If you need to return the entire row (not just the max value): Consider using
FILTER(Excel 365) or an array formula withINDEX/MATCHto get the associated data.
Common Problems and Solutions
Users often run into a few issues when using these formulas:
- #VALUE! error with MAXIFS: This usually happens if the criteria ranges are not the same size as the max range. Ensure all ranges have the same number of rows and columns.
- Array formula not working: If you forget to press Ctrl+Shift+Enter, the formula may return 0 or an incorrect value. Always confirm the curly braces appear.
- Text criteria with wildcards:
MAXIFSsupports wildcards like * and ?. For example,"S*"matches any text starting with S. But if you want to match a literal asterisk, use a tilde (~) before it. - Criteria with dates: Always use the
DATEfunction or a cell reference for dates, rather than typing a date as text. For example,">"&DATE(2025,1,1)is safer than">1/1/2025"because the latter may be misinterpreted depending on your regional date settings. - Blank cells in criteria range: If a criteria cell is blank,
MAXIFStreats it as an empty string. To match truly blank cells, use""as the criteria.
Comparison: MAXIFS vs. Other Functions
It’s helpful to see how MAXIFS compares to related functions:
- MAXIFS vs. MAX+IF: Both do the same job, but MAXIFS is simpler and doesn’t require array entry. MAX+IF is more flexible for complex logic (e.g., OR conditions) but requires Ctrl+Shift+Enter in older versions.
- MAXIFS vs. LARGE:
LARGEreturns the nth largest value. For example,=LARGE(C2:C20, 2)gives the second highest. You can combine LARGE with IF for multiple criteria, but MAXIFS is more direct for the top value. - MAXIFS vs. SUMPRODUCT: SUMPRODUCT can do many things, but it’s less readable and has the negative-number caveat. MAXIFS is the clear winner for clarity.
- MAXIFS vs. FILTER: In Excel 365, you can use
=MAX(FILTER(C2:C20, (B2:B20="Smith")*(A2:A20>DATE(2025,1,1)))). This is more verbose but also lets you return the entire filtered range if needed.
Frequently Asked Questions
Can I use MAXIFS with OR logic (e.g., either condition A or B)?
Yes, but not directly. MAXIFS requires all criteria to be AND. To do OR, you can use an array formula like =MAX(IF((B2:B20="Smith")+(B2:B20="Jones"), C2:C20)) (entered with Ctrl+Shift+Enter). Or, in Excel 365, you can use =MAX(FILTER(C2:C20, (B2:B20="Smith")+(B2:B20="Jones"))).
Does MAXIFS work in Google Sheets?
Yes, Google Sheets has a MAXIFS function with the same syntax. However, the array formula method with MAX and IF in Google Sheets does not require Ctrl+Shift+Enter; it works automatically with array formulas enabled.
Why does my MAXIFS formula return 0?
This often means no rows meet all your criteria. Check that your criteria text matches exactly (including spaces) and that date comparisons are correct. Also, ensure the max range contains numbers, not text.
Can I use MAXIFS with a cell reference for the criteria?
Absolutely. For example, if you have the product name in cell E1, use =MAXIFS(C2:C100, A2:A100, E1, B2:B100, "East"). This makes the formula dynamic.
Is there a way to get the second highest value with multiple criteria?
Yes, you can use LARGE with IF in an array formula: =LARGE(IF((A2:A100="Widget")*(B2:B100="East"), C2:C100), 2) (Ctrl+Shift+Enter). Or, in Excel 365, you can use =LARGE(FILTER(C2:C100, (A2:A100="Widget")*(B2:B100="East")), 2).
Does MAXIFS ignore blank cells in the max range?
Yes, blank cells are ignored. If you want to include them as zero, you’d need to handle them separately, but typically you don’t want blanks counted.
Related
- Excel HR Templates for Attendance: A Practical Guide
- Excel How to Ungroup Cells: Rows, Columns & Sheets
- Excel: How to Split Information in a Cell
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Max Value Between Two Dates in Excel: Formula GuideTo find the largest value recorded between two dates in Excel, use =MAXIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2). This assumes dates are in…
- Concatenate Text Cells in Excel: Formulas and TipsTo concatenate text cells in Excel, join their references with the ampersand operator, such as =A2&" "&B2 to…
- Excel Filter Shortcut: How to Add or Remove FiltersIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to add filter drop-downs to the current data range;…
- Microsoft Excel RANK Formula: How to Rank ValuesIn Microsoft Excel for Microsoft 365, use RANK.EQ to rank a number against a list: with scores in…
- Excel Formula to Split Text in a Cell: A Practical GuideIn Excel for Microsoft 365, split text in a cell with =TEXTSPLIT(A2,","), replacing the comma with the character…
- Excel COUNTIF Ignore Duplicates: How ToThe short answer: Excel’s COUNTIF function does not have a built-in option to ignore duplicates, but you can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.