Does MAX work with text or dates?

MAX ignores pure text entries, but dates work perfectly because they are stored as numbers internally. When you apply MAX to a date column, it returns the largest date (furthest in the future).

How do I find the maximum value only if a condition is true?

Use MAXIFS instead of MAX. For example, =MAXIFS(D:D,B:B,"Fresh Farm Co") finds the highest quantity only for items supplied by Fresh Farm Co. MAX alone compares all values regardless of other criteria.

Can MAX work with multiple non-contiguous ranges?

Yes. You can pass separate ranges as arguments: =MAX(D2:D5,D8:D9,D12:D15). This is useful when your data is split across different sections of the spreadsheet and you want one maximum across all of them.

What's the difference between MAX and MAXA?

MAXA treats logical values as numbers: TRUE counts as 1 and FALSE counts as 0. MAX ignores them entirely. For a stock inventory, use MAX; MAXA is rare and only needed when your range mixes numbers and TRUE/FALSE values intentionally.

MAX function

MAX returns the largest numeric value in a range, ignoring text and empty cells. Useful for finding peak inventory, highest prices, or latest dates.

=MAX(number1, ...)

Generate a MAX formula

Describe what you need. The generator will reach for MAX where MAX is the right tool, and tell you when it is not.

Arguments

How MAX reads its arguments

Argument	Required	Description
number1	Required	Required. A number, reference to a range, or named range containing numeric values. Non-numeric entries and empty cells are skipped.
...	Repeating	Optional. Additional numbers, ranges, or named ranges to compare. MAX can accept up to 254 ranges or individual values.

Returns

A single number representing the highest value encountered in the supplied range(s).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find highest stock quantity

Ingredient	Supplier	Unit	Qty
Tomatoes	Fresh Farm Co	kg	45
Basil	Fresh Farm Co	bunch	24
Olive Oil	Mediterranean Imports	L	12
Garlic	Fresh Farm Co	kg	3
Pasta	Italian Foods Ltd	kg	35
Mozzarella	Dairy Direct	kg	8
Chicken Breast	Premium Meats	kg	18
Balsamic Vinegar	Mediterranean Imports	L	2

=MAX(D2:D9)

Result: 45

MAX scans all quantities in column D and returns 45, the highest stock level in the restaurant's inventory. This identifies Tomatoes as the most-stocked ingredient, useful for monitoring which items are overordered or consuming storage space.

2. Identify latest shelf-life item

Ingredient	Supplier	Unit	Qty	Expiry Date
Tomatoes	Fresh Farm Co	kg	45	2026-09-20
Basil	Fresh Farm Co	bunch	24	2026-09-17
Olive Oil	Mediterranean Imports	L	12	2027-03-15
Garlic	Fresh Farm Co	kg	3	2026-10-05
Pasta	Italian Foods Ltd	kg	35	2027-06-10
Mozzarella	Dairy Direct	kg	8	2026-09-18
Chicken Breast	Premium Meats	kg	18	2026-09-16
Balsamic Vinegar	Mediterranean Imports	L	2	2028-01-20

=MAX(E2:E9)

Result: 2028-01-20

MAX compares dates as numbers, returning the largest date value: 2028-01-20 (Balsamic Vinegar). This shows which inventory item has the longest shelf life remaining, helping prioritize which stock to use first and which can sit longer.

3. Compare maximum stock between suppliers

Ingredient	Supplier	Unit	Qty
Tomatoes	Fresh Farm Co	kg	45
Basil	Fresh Farm Co	bunch	24
Olive Oil	Mediterranean Imports	L	12
Mozzarella	Dairy Direct	kg	8
Balsamic Vinegar	Mediterranean Imports	L	2

=MAX(D2:D4,D7:D8)

Result: 45

By specifying multiple ranges (D2:D4 for Fresh Farm Co and D7:D8 for Mediterranean Imports), MAX compares only those suppliers' quantities, returning 45. This pattern lets you track highest stock levels for specific vendors without summing—useful for analyzing supplier performance.

Common errors

Which MAX error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The range contains text, blank cells, or logical values (TRUE/FALSE) that MAX cannot directly compare with numbers in the same call.	Ensure the range contains only numeric values, or exclude text columns. Use MAXA if you need to count TRUE as 1 and FALSE as 0.
#NAME?	The function name is misspelled, such as =MAx(D2:D9), =MAXIMUM(D2:D9), or =MAX(D2:D9 (missing closing parenthesis).	Check that the function is spelled exactly as MAX with correct syntax: =MAX(number1, number2, ...) and all parentheses are balanced.
#REF!	A reference in the formula points to a deleted column or row, or to a range outside the valid spreadsheet bounds.	Verify that all column and row references still exist. If you deleted a column, update the formula to reference the correct range.

Tips and when to use something else

MAX ignores text and empty cells automatically—you don't need to clean data before using it.
When comparing dates, MAX returns the latest (furthest future) date; use MIN to find the soonest expiry.
Use MAXIFS to find the maximum value with conditions, such as =MAXIFS(D:D,B:B,"Fresh Farm Co") to find max qty only for a specific supplier.
MAX works across multiple ranges: =MAX(D2:D5,D8:D9) compares inventory from different sections without summing.

Related functions

MINMIN finds and returns the smallest numeric value in a range or list, automatically excluding text and empty cells.MAXIFSReturns the largest value in a range that meets multiple criteria, combining the power of MAX with IF-like filtering.AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.LARGELARGE returns the k-th largest value in an array, where k=1 is the maximum, k=2 is the second-largest, and so on.MINIFSReturns the minimum value in a range that meets one or more specified criteria, useful for finding smallest quantities subject to filters.

Frequently asked questions

Does MAX work with text or dates?: MAX ignores pure text entries, but dates work perfectly because they are stored as numbers internally. When you apply MAX to a date column, it returns the largest date (furthest in the future).
How do I find the maximum value only if a condition is true?: Use MAXIFS instead of MAX. For example, =MAXIFS(D:D,B:B,"Fresh Farm Co") finds the highest quantity only for items supplied by Fresh Farm Co. MAX alone compares all values regardless of other criteria.
Can MAX work with multiple non-contiguous ranges?: Yes. You can pass separate ranges as arguments: =MAX(D2:D5,D8:D9,D12:D15). This is useful when your data is split across different sections of the spreadsheet and you want one maximum across all of them.
What's the difference between MAX and MAXA?: MAXA treats logical values as numbers: TRUE counts as 1 and FALSE counts as 0. MAX ignores them entirely. For a stock inventory, use MAX; MAXA is rare and only needed when your range mixes numbers and TRUE/FALSE values intentionally.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17