Excel MAX Function: Find the Largest Value With Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

The Excel maximum formula returns the largest numeric value in a range or list. You write =MAX(B2:B13) and Excel scans those cells and gives you back the highest number it finds. When you need the largest value that also meets a condition, the Excel maximum formula extends to MAXIFS, which filters the range before it takes the maximum.

Quick Answer

• =MAX(range) returns the largest number in one or more ranges.
• =MAX(number1, number2, ...) also accepts individual values, not only ranges.
• =MAXIFS(max_range, criteria_range, criteria) returns the largest value that meets a condition.
• MAX ignores text, logical values, and empty cells inside a range.
• If the range contains no numbers, MAX returns 0.

Syntax

MAX takes one or more arguments. Each can be a number, a cell reference, or a range.

MAXIFS takes a maximum range plus one or more criteria pairs.

The general form is:

$$ \text{MAXIFS} = \max\{\, x \in \text{max\_range} \mid \text{criteria hold} \,\} $$

How It Works

MAX walks through every cell you give it and keeps the largest numeric value. Numbers typed directly into the formula count too, so =MAX(10, 4, 27) returns 27. Text entries such as "N/A" or "pending" are skipped when they sit inside a referenced range. Logical values like TRUE and FALSE are also skipped inside ranges, though they are counted when typed directly as arguments.

Empty cells are ignored. That behavior matters because a blank cell is not the same as a zero. If your range holds only blanks and text, MAX returns 0, which can look like a real result when it is not.

MAXIFS adds a filter step. Excel checks each cell in criteria_range1 against criteria1, keeps the positions where the test passes, then takes the maximum of the matching cells in max_range. The two ranges must be the same size, or Excel returns a #VALUE! error. You can stack several criteria pairs, and all of them must pass for a row to qualify.

Criteria accept numbers, text, and comparison operators. "July" matches the text July. ">30" matches values above 30. "<>" matches non-blank cells. Wildcards work in text criteria, so "J*" matches any month starting with J.

Worked Example

The table below tracks daily temperatures for the first twelve days of July 2024. Column A holds dates, column B holds temperatures in degrees, and column C holds the month name pulled from each date with TEXT.

Cell D2 uses =MAX(B2:B13) and returns 36, the hottest temperature across the whole period. Cell E2 uses =MAXIFS(B2:B13,C2:C13,"July") and also returns 36, because every row in this sample falls in July. The two formulas agree here, which is a useful check that your criteria range is aligned with your max range.

To see MAXIFS do real filtering work, change one month label. If row 6 read "June" instead of "July", the July maximum would drop to 35 and the overall maximum would stay at 36. That gap is exactly what the criteria argument is for.

More Examples

Largest value across two ranges. =MAX(B2:B13, F2:F13) compares both blocks and returns the single highest number. This is handy when your data sits in separate columns.

Largest value with a numeric threshold. =MAXIFS(B2:B13, B2:B13, ">30") returns 36. The criteria range and the max range can be the same range.

Largest value with two conditions. =MAXIFS(B2:B13, C2:C13, "July", B2:B13, ">30") returns 36. Both tests must pass.

Largest value ignoring zeros. =MAXIFS(B2:B13, B2:B13, "<>0") skips any zero entries, which is useful when zero means "no reading" instead of a real measurement.

Largest value with a wildcard. =MAXIFS(B2:B13, C2:C13, "J*") returns 36 because every month label starts with J.

Largest value from a filtered list. =MAX(IF(C2:C13="July", B2:B13)) is the array-style alternative for older Excel versions. It needs to be entered as an array formula in those versions, so MAXIFS is the simpler choice when your version supports it.

If you want to count how many readings exist before taking a maximum, the COUNT function in Excel pairs well with MAX. For summing the same range, see the Excel SUM function. When you need to pull a related value from the row that holds the maximum, the Excel INDEX function is the usual next step.

Errors and How to Fix Them

Common Mistakes

• Leaving numbers stored as text. MAX skips them, so the result looks too low. Fix it by converting the column to numbers, or by checking with ISNUMBER.
• Mismatched range sizes in MAXIFS. If max_range covers 12 rows and criteria_range1 covers 10, you get #VALUE!. Fix it by selecting both ranges with the same start and end rows.
• Assuming blank cells count as zero. They do not. If every cell is blank or text, MAX returns 0, which can be mistaken for a real reading. Fix it by testing with COUNT first.
• Forgetting that MAX ignores logical values in ranges. A column of TRUE and FALSE entries will not produce a maximum. Fix it by converting those values to 1 and 0 if you need them counted.
• Using MAX where MAXIFS is needed. =MAX(B2:B13) cannot filter by month. Fix it by adding the criteria pair with MAXIFS.
• Pointing the criteria range at the wrong column. A criteria range that does not line up row by row with the max range gives a plausible but wrong answer. Fix it by verifying that both ranges start on the same row.

Limitations

MAX returns a single number, so it cannot tell you which row produced that number. If you need the date or label attached to the maximum, you have to look it up separately with INDEX and MATCH or a similar approach. MAX also cannot return the second or third largest value. For that you need LARGE, which takes a rank argument.

MAXIFS handles text and numeric criteria well, but it does not support OR logic across criteria pairs. Every pair you add must pass. To get an OR condition you need a different construction, such as taking the MAX of two MAXIFS results or using an array formula. Accuracy of statistical procedures in spreadsheet software has been studied in detail, and the authors note that results depend on correct formula construction and data types [1]. Treat any maximum as a check on your inputs, not a substitute for reviewing them.

Frequently Asked Questions

What is the difference between MAX and MAXIFS?

MAX returns the largest value in a range with no conditions. MAXIFS returns the largest value that also meets one or more criteria. Use MAX for a simple high value and MAXIFS when you need to restrict the result to a group, a date range, or a threshold.

Does MAX count text or blank cells?

No. Inside a referenced range, MAX skips text, logical values, and empty cells. It only compares numbers. If the range contains no numbers at all, MAX returns 0.

Why does my MAX formula return 0?

The most common reason is that the range holds no numeric values. Numbers formatted or stored as text are ignored. Check the cell format and convert text numbers back to numbers, then rerun the formula.

Can MAX work across multiple ranges?

Yes. =MAX(B2:B13, F2:F13) evaluates both ranges and returns the single largest value. You can list up to 255 arguments, and each one can be a number, a cell, or a range.

How do I find the maximum for a specific month?

Use MAXIFS with the month column as the criteria range. =MAXIFS(B2:B13, C2:C13, "July") returns the highest temperature where the month equals July. If your month values come from dates, build them with TEXT first so the text matches exactly.

Is there a maximum formula for the second largest value?

MAX cannot do this. Use =LARGE(B2:B13, 2) for the second largest value and change the rank number for other positions. LARGE accepts the same ranges as MAX.

References

1. McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis

Further Reading

• Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician
• Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology
• Microsoft Support: Excel help and learning
• Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology
• Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology

Related Articles

• SQL MAX Function: Syntax and Examples
• Greater Than or Equal To in Excel: Operators and Examples
• Excel FIND Function: Syntax, Examples and SEARCH Comparison
• COUNT Function in Excel: Syntax, Examples and Tips
• Excel INDEX Function: Syntax, Examples and How to Use It