Excel MAX IF: Find the Highest Number by Condition

To find the largest number in Excel that meets a condition, use MAXIFS, such as =MAXIFS(C2:C7,B2:B7,"East"). If you mean the largest value only when a cell contains a number, use MAX on the range: it ignores text and blank cells in a cell reference.

What “MAX IF” Means in Excel

Excel does not have a function literally named MAXIF. People usually use “MAX IF” to describe finding a maximum value after applying a condition—for example, the highest sale in the East region, the latest score for a particular student, or the largest measurement above a threshold. Excel’s purpose-built function for that job is MAXIFS.

The function separates the values being compared from the cells that determine which values qualify. That distinction matters: the maximum range contains the numbers Excel will compare, while each criteria range contains the corresponding labels or numbers Excel will test. The ranges should cover the same rows so that a label is matched with the right value.

For a plain maximum with no condition, use MAX. For a maximum subject to one or several conditions, use MAXIFS when your Excel version includes it. If you are working with an older version, an array formula is an alternative.

Use MAXIFS for One Condition

Suppose a worksheet has sales representatives in A2:A7, regions in B2:B7, and sales amounts in C2:C7. The rows are Mina, East, 120; Omar, West, 450; Mina, East, 280; Leo, East, 640; Omar, East, 510; and Mina, West, 390. To return the largest sale in the East region, enter this formula in an empty cell:

With regions in B2:B7 and sales amounts in C2:C7, this returns 640, the largest amount among the East rows: =MAXIFS(C2:C7,B2:B7,"East").

The general pattern is MAXIFS(max_range, criteria_range1, criteria1, ...). The first argument is the range whose maximum you want. The next two arguments specify a range to test and the condition it must satisfy. You can add more criteria-range and criterion pairs when needed.

In Microsoft 365 for Windows, type the formula into a cell and press Enter. Excel for Mac uses the same formula and argument separators in the usual regional settings. Excel for the web also supports MAXIFS. In some locales, Excel uses semicolons rather than commas between arguments; use the separator configured for your installation if the comma version produces a formula error.

Apply More Than One Condition

MAXIFS can require several conditions at once. For instance, you might want the largest sale made by Mina in the East region, rather than the largest sale from anyone in that region. Excel includes a row only when every supplied condition is satisfied.

With representatives in A2:A7, regions in B2:B7, and sales in C2:C7, this formula returns 280, Mina’s only East-region sale in the sample data: =MAXIFS(C2:C7,B2:B7,"East",A2:A7,"Mina").

Criteria can refer to cells instead of fixed text. If E2 contains East and F2 contains Mina, the formula can use those cells as the criteria. That makes a small report easier to reuse: change the selection in E2 or F2 and the result updates without editing the formula. It is also useful when a worksheet has a drop-down list of regions or employees.

With regions in B2:B7, representatives in A2:A7, sales in C2:C7, East in E2, and Mina in F2, this returns 280: =MAXIFS(C2:C7,B2:B7,E2,A2:A7,F2).

Use Numeric Conditions and Thresholds

A criterion can test numbers as well as text. For example, a condition such as “greater than 250” can exclude smaller values before Excel calculates the maximum. Comparison operators include greater than (>), less than (<), greater than or equal to (>=), and not equal to (<>). When an operator is part of a criterion, put it inside quotation marks.

With sales amounts in C2:C7, this returns 640, the largest sale strictly above 250: =MAXIFS(C2:C7,C2:C7,">250").

To make the cutoff adjustable, place the threshold in a cell and join the operator to that cell with the ampersand operator. This avoids having to rewrite the formula whenever the cutoff changes. If D2 contains 250, and regions and sales are in B2:B7 and C2:C7, respectively, this returns 640, the largest East sale above the threshold: =MAXIFS(C2:C7,B2:B7,"East",C2:C7,">"&D2).

Check whether the boundary should be included. The criterion ">250" excludes a value of exactly 250, while ">=250" includes it. This small difference often explains why a result does not match a manually filtered list.

What Happens When a Cell Is Not a Number?

If your question is literally about taking a maximum only from numeric cells, a regular MAX formula is often enough. When MAX receives a cell reference, it ignores blank cells, text, and logical values in that range, and compares the numeric values. It does not treat a number stored as text in a referenced cell as an ordinary numeric entry.

With A2:A6 containing the number 7, the text “pending,” the number 18, a blank, and the number 12, this returns 18: =MAX(A2:A6).

That behavior can be useful when a column mixes numbers with notes, but it can also hide a data-quality issue. If a value that looks like 18 is stored as text, MAX may skip it rather than compare it. A common cause is importing data from another system or entering a leading apostrophe. Convert such entries to actual numbers before relying on the maximum. You can often identify numbers stored as text through Excel’s warning indicators, cell alignment, or number-format checks; formatting a text value as Number does not necessarily convert the underlying value.

Also distinguish a cell reference from a typed argument. MAX has rules for direct arguments and references, so a formula containing a quoted numeric string is not the same as asking MAX to inspect a worksheet range. For dependable results, keep the source column consistently numeric and use a separate column for notes.

Older Excel Versions and the MAX IF Array Formula

MAXIFS is available in Excel 2019 and later, including Microsoft 365. If you need to support a version that predates MAXIFS, you can combine MAX with IF. This is commonly called a MAX IF array formula because IF evaluates the condition across the range and passes qualifying values to MAX.

With regions in B2:B7 and sales in C2:C7, this returns 640, the maximum sale where the region is East: =MAX(IF(B2:B7="East",C2:C7)).

In Microsoft 365, press Enter to enter this formula; dynamic-array calculation handles the range operation. In older Excel versions without dynamic arrays, confirm the formula as an array formula with Ctrl+Shift+Enter on Windows rather than Enter alone. Excel displays braces around a confirmed legacy array formula, but you do not type those braces yourself. If you edit the formula later in those older versions, confirm it again with the same key combination.

For a new workbook used by people with current Excel, MAXIFS is usually clearer and easier to maintain. The array approach is useful mainly for compatibility with older installations or when adapting a formula already built around array calculations.

Handle No Matches and Common Errors

A result of 0 from MAXIFS does not always mean the largest qualifying value is actually zero. MAXIFS returns zero when no cells meet the criteria, so that result can be ambiguous if your data also permits zero or negative numbers. Check that the criterion text matches the source values and that the maximum range contains numeric values before interpreting the result.

If you need to display a message when there are no qualifying numeric values, you can test for matches before returning MAXIFS. With regions in B2:B7 and candidate values in C2:C7, this returns the text No numeric match because the sample has no North-region row: =IF(SUMPRODUCT((B2:B7="North")*ISNUMBER(C2:C7))=0,"No numeric match",MAXIFS(C2:C7,B2:B7,"North")).

Other common problems include criteria ranges and maximum ranges having different sizes, hidden spaces in labels, and values that appear numeric but are stored as text. For example, “East” and “East ” are different text values because the latter has a trailing space. Clean imported labels or use a consistent source list before assuming the calculation is wrong.

Confirm that every criteria range lines up row by row with the maximum range.
Check spelling, extra spaces, and the exact comparison operator in each criterion.
Verify that the candidate values are numbers, not numeric-looking text.
Check whether zero is a legitimate maximum or the function’s no-match result.

Choose the Right Formula for the Job

Use MAX when you want the largest numeric value in a range and have no condition. Use MAXIFS when one or more conditions decide which values count. Use MAX(IF()) when you need an array-formula alternative for an older Excel version, while taking account of the different entry method in legacy releases.

For example, if a report needs the largest East-region sale, MAXIFS expresses the intent directly. If it needs the largest East-region sale made by Mina, add a second condition pair. If the requirement is simply “largest number in this mixed column,” MAX is the appropriate function; adding an IF condition would make the formula more complicated without improving the result.

MAXIFS also differs from filtering the worksheet manually. A filter changes which rows are displayed, while MAXIFS calculates from its specified ranges and criteria whether or not rows are hidden. That makes it convenient for summary cells and dashboards, but it means a manually applied filter does not automatically become part of the formula’s criteria.

Frequently Asked Questions

Does Excel have a MAXIF function?
No. The function for finding a maximum with one or more conditions is MAXIFS. MAX(IF()) is another pattern, often used for older-version compatibility.

How do I get the largest value if another cell matches?
Use MAXIFS with the value range first, followed by the range containing the matching labels and the label to find. For example, the East-region sales example above uses the region column as its criteria range.

Can MAXIFS use multiple conditions?
Yes. Add another criteria-range and criterion pair for each additional condition. MAXIFS includes a row only when all of its criteria are met.

Why does MAXIFS return 0 when there is no match?
Zero is MAXIFS’s result when no entries meet the criteria. If zero is also a possible data value, test for qualifying numeric entries separately so you can distinguish a real zero from no match.

Does MAX ignore text and blank cells?
Yes, when they are entries in a referenced range, MAX ignores text and blanks while evaluating numeric values. Numeric-looking text may also be skipped, so convert it to a real number if it should count.

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.