Does MAXIFS work with partial text matching?

MAXIFS supports wildcards: use "East*" to match any value starting with "East", "*Coast" for values ending with "Coast", or "*ast" to match values containing "ast" anywhere. This works in Excel but behavior may vary in Google Sheets—verify with your platform.

What happens if no rows meet all the criteria?

MAXIFS returns 0 (zero) if no rows satisfy all conditions. If you need to distinguish between "no matches" and a legitimate zero value, wrap the formula in IF and COUNTIFS to detect the count of matching rows first.

Can I use MAXIFS with date ranges as criteria?

Yes. Use date operators like ">"&DATE(2024,1,1) to find the maximum value where a date column is after January 1, 2024. Alternatively, criteria like ">=2024-01-01" works if your dates are formatted as text, though comparing actual date values is more reliable.

How do I use MAXIFS when I want the maximum across multiple columns?

MAXIFS evaluates one max_range at a time. If you need the maximum across several columns with the same criteria, either create separate MAXIFS formulas and nest them in MAX (e.g., =MAX(MAXIFS(...), MAXIFS(...))) or restructure your data so the values you want to compare are in a single column.

MAXIFS function

Returns the largest value in a range that meets multiple criteria, combining the power of MAX with IF-like filtering.

=MAXIFS(max_range, criteria_range1, criteria1, ...)

Generate a MAXIFS formula

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

Arguments

How MAXIFS reads its arguments

Argument	Required	Description
max_range	Required	The range containing values to evaluate; MAXIFS returns the largest value from here. Can be a single column or multiple columns, but typically a single contiguous range.
criteria_range1	Required	The first range to check for matches. Must have the same dimensions (row and column count) as max_range. Additional criteria_range arguments follow the same pattern.
criteria1	Required	The condition that criteria_range1 must satisfy. Can be a value (e.g., "East"), a comparison operator with value (e.g., ">50"), or a reference to a cell containing criteria.
...	Repeating

Returns

A number representing the maximum value found in max_range that satisfies all criteria conditions.

Availability

Excel: 2019+ · Google Sheets: Supported

Worked examples

1. Find the maximum unit price for East region orders

Order ID	Region	Rep	Units	Unit Price
1001	East	Alice	10	50
1002	West	Bob	5	75
1003	East	Charlie	8	60
1004	East	Alice	12	50
1005	West	Alice	7	80
1006	Central	Bob	15	45
1007	East	Bob	6	70
1008	West	Charlie	9	65
1009	Central	Alice	11	55
1010	East	Charlie	14	60

=MAXIFS(E2:E11, B2:B11, "East")

Result: 70

MAXIFS filters the Unit Price column (E) to only rows where Region (B) equals "East". The East orders have prices 50, 60, 50, 70, and 60. The maximum of these is 70, which comes from order 1007 (Bob's order with 6 units at $70 each).

2. Find the maximum units sold by Alice

Order ID	Region	Rep	Units	Unit Price
1001	East	Alice	10	50
1004	East	Alice	12	50
1005	West	Alice	7	80
1009	Central	Alice	11	55

=MAXIFS(D2:D11, C2:C11, "Alice")

Result: 12

MAXIFS filters the Units column (D) to only rows where Rep (C) equals "Alice". Alice's orders contain 10, 12, 7, and 11 units respectively. The maximum is 12 units from order 1004 placed in the East region on February 15th.

3. Find the maximum unit price for large East region orders

Order ID	Region	Rep	Units	Unit Price
1004	East	Alice	12	50
1010	East	Charlie	14	60

=MAXIFS(E2:E11, B2:B11, "East", D2:D11, ">10")

Result: 60

MAXIFS filters for rows meeting TWO criteria: Region (B) = "East" AND Units (D) > 10. Only orders 1004 (12 units, $50 price) and 1010 (14 units, $60 price) qualify. The maximum unit price among these is $60 from Charlie's order 1010.

Common errors

Which MAXIFS error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The max_range and criteria_range1 have different numbers of rows or columns. For example, if max_range is E2:E11 (10 rows) but criteria_range1 is B2:B9 (8 rows), the ranges cannot be properly aligned.	Ensure all ranges span the exact same dimensions. Verify row counts match—if your data is in rows 2 through 11, all ranges should be XYZ2:XYZ11.
#REF!	One of the range references points to cells or columns that have been deleted, moved, or are otherwise invalid. For example, if you originally wrote =MAXIFS(E2:E11, B2:B11, "East") but then deleted column B, the reference becomes broken.	Check that all ranges (max_range, criteria_range1, etc.) still exist and point to valid cells. Restore or update any deleted columns or ranges.
#N/A	The criteria value or a cell referenced in the criteria argument contains an error or unresolvable reference. For example, =MAXIFS(E2:E11, B2:B11, A1) where A1 contains #N/A from a failed lookup.	Verify that criteria values are valid. If criteria references a cell, ensure that cell contains a value, not an error. Use IFERROR to provide a fallback if needed.

Tips and when to use something else

Use MAXIFS instead of array formulas with MAX and IF for cleaner, easier-to-read code that calculates faster, especially with large datasets.
When you need to exclude rows based on multiple conditions, stack additional criteria_range/criteria pairs—each one acts as an AND filter.
If you need the second-highest or third-highest value meeting your criteria, combine LARGE with FILTER or use a helper column instead of MAXIFS alone.
MAXIFS ignores completely empty cells in criteria ranges, treating them as non-matches; be aware if your data has intentional blanks.

Related functions

MINIFSReturns the minimum value in a range that meets one or more specified criteria, useful for finding smallest quantities subject to filters.AVERAGEIFSAVERAGEIFS returns the average of values in a range that meet multiple criteria, enabling filtered averages across complex datasets.COUNTIFSCOUNTIFS counts rows where all criteria are met across multiple ranges, useful for filtering inventory and finding matches across conditions.MAXMAX returns the largest numeric value in a range, ignoring text and empty cells. Useful for finding peak inventory, highest prices, or latest dates.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.

Frequently asked questions

Does MAXIFS work with partial text matching?: MAXIFS supports wildcards: use "East*" to match any value starting with "East", "*Coast" for values ending with "Coast", or "*ast" to match values containing "ast" anywhere. This works in Excel but behavior may vary in Google Sheets—verify with your platform.
What happens if no rows meet all the criteria?: MAXIFS returns 0 (zero) if no rows satisfy all conditions. If you need to distinguish between "no matches" and a legitimate zero value, wrap the formula in IF and COUNTIFS to detect the count of matching rows first.
Can I use MAXIFS with date ranges as criteria?: Yes. Use date operators like ">"&DATE(2024,1,1) to find the maximum value where a date column is after January 1, 2024. Alternatively, criteria like ">=2024-01-01" works if your dates are formatted as text, though comparing actual date values is more reliable.
How do I use MAXIFS when I want the maximum across multiple columns?: MAXIFS evaluates one max_range at a time. If you need the maximum across several columns with the same criteria, either create separate MAXIFS formulas and nest them in MAX (e.g., =MAX(MAXIFS(...), MAXIFS(...))) or restructure your data so the values you want to compare are in a single column.

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