Excel MAXIFS: Find the Highest Value by Criteria

How-to basics · Updated November 19, 2026

To find the largest value in an Excel range that meets a condition, use MAXIFS. For example, with categories in A2:A7 and values in B2:B7, =MAXIFS(B2:B7,A2:A7,"East") returns the highest value for East; add more criteria pairs to require multiple conditions.

What MAXIFS does

MAXIFS checks one or more ranges for specified conditions, then returns the maximum number from the corresponding cells in a separate maximum range. It is the most direct built-in option for questions such as “What is the largest sale in the East region?” or “What is the highest score for a particular class?”

The function has this structure: =MAXIFS(max_range,criteria_range1,criteria1,[criteria_range2,criteria2],...). The first argument is the range containing the numbers to compare. Each following pair tells Excel where to test a condition and what condition to apply. Optional pairs let you narrow the result further.

MAXIFS is available in Microsoft 365 and Excel 2019 or later, including Excel for the web. If you use an older standalone Excel version that does not recognize the function, see the alternatives below. The formula syntax is the same in Excel for Windows and Mac; regional settings may use a different list separator, such as a semicolon, instead of a comma.

Find a maximum with one condition

Suppose a small sales table has regions in A2:A7 and sale amounts in B2:B7. The rows are East—120, West—95, East—180, North—140, West—160, and East—155. The maximum sale for East is 180.

With the region names in A2:A7 and the amounts in B2:B7, this formula returns 180:

=MAXIFS(B2:B7,A2:A7,"East")

Putting the condition in a cell makes the worksheet easier to reuse. If F2 contains East, and the same region and amount ranges are used, enter:

=MAXIFS(B2:B7,A2:A7,F2)

Change F2 to West and the result becomes 160; change it to North and the result becomes 140. This is useful for a report where a reader selects a category from a cell rather than editing the formula. You can type the function into a cell and press Enter. Excel’s formula autocomplete can help complete the function name and arguments.

Add more than one criterion

Each additional condition is another criteria-range and criteria pair. For example, a transaction table might have region in A2:A100, product in B2:B100, and revenue in C2:C100. To find the maximum revenue for East and Product A, use:

=MAXIFS(C2:C100,A2:A100,"East",B2:B100,"Product A")

This returns the largest value in C2:C100 only for rows where the region is East and the product is Product A. Conditions in separate pairs are combined as AND requirements: a row must satisfy every listed criterion to be considered. MAXIFS does not provide an OR condition simply by adding another pair. For an OR requirement, use separate MAXIFS calculations and compare their results, or use a formula built around FILTER in a compatible Excel version.

All the ranges should cover corresponding rows and have the same dimensions. If the maximum range has 99 cells but a criteria range has 100, Excel returns a #VALUE! error. Using complete columns is possible, but bounded ranges or Excel Tables can make a workbook easier to maintain and avoid unnecessary calculation over unused rows.

Use number, text, and date conditions

Criteria can test exact text, numbers, comparisons, and patterns. For a comparison such as “greater than 100,” put the operator and number together in a quoted criterion. With scores in B2:B20 and departments in A2:A20, this finds the highest score in Sales that exceeds 100:

=MAXIFS(B2:B20,A2:A20,"Sales",B2:B20,">100")

If the threshold is in F2 instead, concatenate the comparison operator with that cell reference. With the same ranges and the threshold in F2, use:

=MAXIFS(B2:B20,A2:A20,"Sales",B2:B20,">"&F2)

For dates, Excel stores valid dates as serial numbers, so comparison criteria can define a date interval. If dates are in A2:A100 and amounts are in B2:B100, this returns the maximum amount from January 2026. The start date is included and the first day of February is excluded:

=MAXIFS(B2:B100,A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1))

Using a less-than condition for the next month’s first day is often safer than trying to include the final day: it also handles dates that contain times. Wildcards can match text patterns: an asterisk represents any number of characters, while a question mark represents one character. For instance, a criterion of "East*" can match text beginning with East. To match a literal asterisk or question mark, precede it with a tilde.

Understand blanks, zero, and no matches

MAXIFS ignores text and blank cells in the maximum range when it calculates a maximum. However, a cell that looks like a number but is stored as text is not a dependable numeric value for this calculation. Convert such entries to real numbers if they should be included. Also check for leading or trailing spaces in text criteria: “East” and “East ” may not behave as the same category.

A result of 0 does not always mean that the matching rows contain a zero. MAXIFS returns 0 if no cells satisfy the criteria, so a zero result can be ambiguous, especially when legitimate matching values might be zero or negative. If you need to distinguish no matches from a real result, check whether any rows meet the condition first. With regions in A2:A7, values in B2:B7, and the requested region in D2, use:

=IF(COUNTIFS(A2:A7,D2)=0,"No match",MAXIFS(B2:B7,A2:A7,D2))

COUNTIFS checks whether at least one row meets the criterion; if not, the formula displays “No match.” If matches exist but all their values are negative, MAXIFS can then correctly return the least negative value. For multiple conditions, add the same condition pairs to COUNTIFS that you use in MAXIFS.

Return the record associated with the maximum

MAXIFS returns a number, not the name or other details from the row containing that number. If you also need the product, employee, or date attached to the highest qualifying value, use a lookup or filter formula. In Microsoft 365, FILTER can return every row meeting a condition, including tied maximums.

For example, suppose employee names are in A2:A7, sales are in B2:B7, regions are in C2:C7, and the requested region is in F2. To return the name or names of employees who achieved the regional maximum, use:

=FILTER(A2:A7,(B2:B7=MAXIFS(B2:B7,C2:C7,F2))*(C2:C7=F2),"No match")

The multiplication combines the two TRUE/FALSE tests as an AND condition, and FILTER can spill more than one result if the maximum is tied. FILTER is available in Microsoft 365 and Excel 2021 or later. If you only need the maximum number, MAXIFS is simpler; if you need the full row or want to show ties, a filter-based result is more informative.

Common errors and ways to troubleshoot

  • #NAME? Usually means Excel does not recognize MAXIFS, often because the workbook is being opened in an older Excel version. Check the version or use an alternative formula.
  • #VALUE! Check that the maximum range and every criteria range have matching dimensions. Also inspect any references that might point to incompatible ranges.
  • Unexpected zero: Verify the criteria match actual cell contents, and check whether the result is zero because no rows matched. The COUNTIFS check above helps separate these cases.
  • Unexpectedly low maximum: Look for numbers stored as text, filters or imported data with inconsistent formats, and criteria that are narrower than intended.
  • Dates do not match: Confirm the cells contain real Excel dates rather than text that only looks like a date. If times are present, use a start-inclusive and next-period-exclusive date range.
  • Formula will not accept commas: Some regional settings use semicolons between arguments. Use the separator Excel expects for your system.

When the formula is correct but the result seems wrong, test the condition on a few rows manually. Check the source data for spelling variations, extra spaces, hidden characters, and numeric values formatted as text. For a large workbook, converting the data to an Excel Table can also help keep ranges aligned as rows are added.

Alternatives for older Excel or different needs

In Excel versions without MAXIFS, an array formula using MAX and IF can apply a condition. With categories in A2:A7 and numeric values in B2:B7, enter:

=MAX(IF(A2:A7="East",B2:B7))

In older Excel versions that require legacy array entry, confirm the formula with Ctrl+Shift+Enter rather than Enter; Excel displays braces around the formula in the formula bar, which it adds itself. In Microsoft 365 and Excel 2021 or later, press Enter for this formula. For multiple conditions, multiply the logical tests inside IF. MAXIFS is generally clearer when it is available, particularly as conditions accumulate.

A PivotTable is another option when you want to summarize many categories and compare their maximums without writing a separate formula for each one. Place the category field in Rows, the numeric field in Values, and change the value summary from Sum to Max using the value field settings. PivotTables are useful for exploratory summaries, while MAXIFS is convenient when a result needs to appear in a specific cell and respond to criteria cells.

Frequently Asked Questions

Can MAXIFS use two or more conditions?
Yes. Add a criteria range and criterion for each additional requirement. MAXIFS includes a value only when its row satisfies all the supplied conditions.

How do I find the maximum for a condition stored in a cell?
Put the requested category in a cell and use that cell reference as the criterion, such as =MAXIFS(B2:B7,A2:A7,D2) when values are in B2:B7, categories are in A2:A7, and D2 contains the category.

Why does MAXIFS return zero when there are no matching rows?
Zero is the function’s result when no cells meet the criteria. Use COUNTIFS in an IF formula to display a message or another value when no qualifying row exists.

Can MAXIFS return the name next to the highest value?
Not by itself; it returns only the maximum number. Use FILTER in a compatible Excel version to return matching names or rows, and note that a filter can show all records tied for the maximum.

Does MAXIFS work with dates?
Yes. Excel dates are numeric values, so MAXIFS can compare date criteria like other numbers. For a date period, use a condition for the first date and another for the start of the next period.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.