MAX IF in Excel: find the largest value that meets a condition

MAXIFS: the simplest way (Excel 2019 and later)

The sheet is a list of sales deals with the date, rep, region, product and amount. To find the biggest deal in one region, MAXIFS takes the range to take the maximum from first, then a range to test and the condition: =MAXIFS(E2:E10,C2:C10,"West"). Point the condition at a cell instead of typing the text, =MAXIFS(E2:E10,C2:C10,H1), so you can change the region in H1 and the result follows.

For more conditions, keep adding pairs: =MAXIFS(E2:E10,C2:C10,"West",D2:D10,"Hardware") is the biggest West hardware deal. Every condition must be true for a row to count. Conditions are written like COUNTIFS and SUMIFS conditions, so dates and numbers go in quotes with an operator: =MAXIFS(E2:E10,A2:A10,">="&DATE(2026,10,1)) is the biggest deal since October 1.

MINIFS has exactly the same arguments and returns the smallest value. Both functions exist in Excel 2019, 2021, 2024, Microsoft 365 and Excel for the web. In Excel 2016 and older they return #NAME?. Every test range must be the same size as the first range, or the result is #VALUE!.

MAX with FILTER (Excel 365 and 2021)

FILTER keeps only the deals whose region equals H1, and MAX takes the largest of what is left. It gives the same answer as MAXIFS but accepts any test you can write as a formula, including OR conditions and tests on calculated values, which MAXIFS cannot do.

Like MAXIFS, the comparison ignores upper and lower case, so "west" also matches West.

Several conditions, including a date range

Inside FILTER, multiply tests to require all of them (AND) and add them to accept any of them (OR). Each test gives TRUE or FALSE for every row, and multiplying turns them into 1 only where every test is true. Here the conditions are Hardware deals dated in September.

For OR, =MAX(FILTER(E2:E10,(C2:C10="East")+(C2:C10="South"))) is the biggest deal in either region. MAXIFS has no OR; you would need two MAXIFS inside MAX.

When nothing matches: 0, #CALC! or a message

There are no North deals. MAXIFS returns 0 in that case, which looks like a real result and can end up in a report as "biggest deal: 0". MAX(FILTER()) returns #CALC! instead, because FILTER has nothing to return. Neither tells your reader what happened.

Give FILTER a third argument, the value to use when it is empty, if 0 is acceptable: =MAX(FILTER(E2:E10,C2:C10="North",0)) returns 0 without the error. If 0 would mislead, check first with COUNTIFS and show a message, as below. The same check works around MAXIFS.

The gotcha: numbers stored as text are skipped

The East maximum looks like 9150. It is wrong: Lena Fischer's October deal in E9 is 9800, but it was imported as text, so MAX, MAXIFS and MAX(FILTER()) all skip it without any warning. You can spot it because the cell is left-aligned, has a green triangle, and =ISTEXT(E9) returns TRUE.

Fix the column once with Data > Text to Columns > Finish, or the warning icon's Convert to Number. In a FILTER formula you can also convert inside: the double minus turns text numbers into real numbers. Do not use -- if the column has words such as "TBC" in it; they turn into #VALUE!.

Who made the biggest deal? Return the name, not just the number

The usual approach is =INDEX(B2:B10,MATCH(MAX(FILTER(E2:E10,C2:C10="South")),E2:E10,0)). It finds the South maximum, 6800, then searches the whole amount column for 6800. The first 6800 belongs to Omar Haddad in the West, so the formula returns Omar Haddad, the wrong person in the wrong region, with no error.

Search for both things at once: the region and the amount. In XLOOKUP, multiplying the two tests gives 1 only on the row where both are true, and XLOOKUP looks for that 1. If two deals in the same region tie for the maximum, it returns the first one.

Excel 2016 and older: MAX(IF()) as an array formula

Before MAXIFS and FILTER, the standard way was MAX wrapped around IF. IF returns the amount where the region matches and FALSE everywhere else, and MAX ignores the FALSE values. In Excel 2016 and older you must press Ctrl+Shift+Enter instead of Enter, and Excel shows curly braces around the formula. Press plain Enter and you get a wrong result or #VALUE!. In Excel 365 and 2021, Enter is enough.

For the smallest value, use =MIN(IF(C2:C10="West",E2:E10)), which returns 3900. A shortcut you may see online, =MIN((C2:C10="West")*E2:E10), returns 0, because every non-West row becomes 0. Multiplying only works for MAX, and only when all values are positive.

Which method to pick

Excel 2019 or later and simple AND conditions: MAXIFS. It is short, uses the same condition style as SUMIFS and COUNTIFS, and is easy for others to read. Excel 365 or 2021 and you need OR, a calculated test or a clear "no match" result: MAX(FILTER()). File must open in Excel 2016 or older: MAX(IF()) with Ctrl+Shift+Enter.

Whichever you pick, check two things before you publish the number: that a 0 really means "the largest is 0" and not "nothing matched", and that the amount column has no text numbers.