Excel Find Maximum Value Based on Criteria with MAXIFS

How-to basics · Updated November 19, 2026

To find the largest value that meets a condition in Excel, use MAXIFS. For example, with regions in A2:A9 and sales in C2:C9, =MAXIFS(C2:C9,A2:A9,"East") returns the highest sales figure for East; add more range-and-condition pairs to apply multiple criteria.

What MAXIFS does

MAXIFS returns the largest numeric value in a range after checking whether one or more corresponding cells meet specified conditions. It is useful for questions such as “What was the highest sale in the East?” or “What was the largest order for Product B in the East?” Unlike sorting a table or applying a filter, the formula updates when the source data changes.

The function is available in Microsoft 365 and Excel 2019 and later, including Excel 2021 and Excel 2024. In Excel for Microsoft 365 on Windows, enter the formula in a blank cell and press Enter. MAXIFS is also available in current Excel for Mac and Excel for the web, although ribbon details and some keyboard shortcuts can differ by platform.

Here is a small example. Column A contains Region, column B contains Product, and column C contains Sales:

  • East, A, 120
  • West, A, 145
  • East, B, 180
  • North, A, 160
  • West, B, 210
  • East, A, 175
  • North, B, 130
  • East, B, 180

With the headers in row 1 and these records in rows 2 through 9, the maximum East sale is 180. If there are tied values, MAXIFS returns the maximum value, not a list of the records that contain it.

MAXIFS syntax and how its arguments work

The syntax is =MAXIFS(max_range,criteria_range1,criteria1,[criteria_range2,criteria2],...). The first argument is the range containing the numbers to compare. Each condition is entered as a pair: a range to test, followed by the condition to apply to that range. Add pairs to require several conditions at the same time.

  • max_range: The cells from which Excel will return the largest numeric value.
  • criteria_range1: The first range Excel checks, such as the Region column.
  • criteria1: The condition for that range, such as East, a cell reference, or a comparison expression.
  • Additional pairs: Optional range-and-condition pairs, for example a Product range and the condition B.

All criteria ranges must have the same dimensions and corresponding positions as the maximum range. In the example, C2:C9, A2:A9, and B2:B9 each contain eight rows. Using C2:C9 for sales but A2:A10 for regions can produce a size-mismatch error. Keep the ranges aligned, and include or exclude header rows consistently.

Find the maximum with one criterion

With regions in A2:A9 and sales in C2:C9, this formula finds the largest sale where the region is East: =MAXIFS(C2:C9,A2:A9,"East"). In the sample data, the qualifying sales are 120, 180, 175, and 180, so the result is 180.

For a changing condition, put the region name in E2 instead of typing it into the formula. With region labels in A2:A9, sales in C2:C9, and the chosen region in E2, use =MAXIFS(C2:C9,A2:A9,E2). If E2 contains West, the sample returns 210. Cell references make a report easier to reuse: change E2 to another region without editing the formula.

Criteria can also express comparisons. With sales in C2:C9 and regions in A2:A9, this formula returns the highest East sale that is at least 150: =MAXIFS(C2:C9,A2:A9,"East",C2:C9,">=150"). The qualifying East values are 180, 175, and 180, so the maximum is 180. For a threshold stored in E2, with E2 containing 150, use =MAXIFS(C2:C9,A2:A9,"East",C2:C9,">="&E2). The ampersand joins the comparison operator to the threshold value.

Use multiple criteria

Multiple criteria are combined as AND conditions: every condition must be true for a row to contribute a value. With sales in C2:C9, regions in A2:A9, and products in B2:B9, this formula finds the highest East sale for Product B: =MAXIFS(C2:C9,A2:A9,"East",B2:B9,"B"). The two matching sales are 180 and 180, so the result is 180.

For a reusable formula, enter a region in E2 and a product in F2. With the same source columns, use =MAXIFS(C2:C9,A2:A9,E2,B2:B9,F2). Changing either input changes the result. This is handy for a summary sheet where users choose criteria from cells or drop-down lists.

You can also apply more than one condition to a numeric column. With dates in D2:D9 and sales in C2:C9, this formula finds the largest sale in a date interval from January 1 through January 31, 2026: =MAXIFS(C2:C9,D2:D9,">="&DATE(2026,1,1),D2:D9,"<="&DATE(2026,1,31)). It assumes D2:D9 contains real Excel dates, not text that merely looks like dates. DATE constructs the boundary dates so the formula does not depend on a particular display format.

Text, wildcards, and date criteria

For a text condition, enter the exact text in quotation marks or refer to a cell containing that text. Excel criteria are generally not case-sensitive, so East and EAST are treated as equivalent for this kind of match. If you need to distinguish letter case, MAXIFS alone does not provide a case-sensitive comparison.

Wildcard criteria can find text that partly matches a value. An asterisk matches any sequence of characters, while a question mark matches one character. For example, with product names in B2:B9 and sales in C2:C9, =MAXIFS(C2:C9,B2:B9,"Widget*") returns the largest sale for product names beginning with Widget. To match a literal asterisk or question mark in data, prefix that character with a tilde.

Dates are stored by Excel as serial numbers, so comparisons work when the cells contain genuine date values. If a date condition is in E2, and dates are in D2:D9 while sales are in C2:C9, use =MAXIFS(C2:C9,D2:D9,">="&E2) to find the maximum sale on or after that date. Format E2 as a date for readability; its number format does not change the underlying comparison.

What happens when nothing matches?

MAXIFS returns 0 when no cells meet the criteria. That can be misleading if zero is a possible result or if valid matching values could be negative. Check whether any rows match before interpreting the maximum. With regions in A2:A9 and the requested region in E2, use =COUNTIF(A2:A9,E2) to count matching rows. If the result is zero, the criteria found no matching region.

When several criteria are involved, use COUNTIFS with the same criteria pairs as MAXIFS. With regions in A2:A9, products in B2:B9, and the requested values in E2 and F2, use =COUNTIFS(A2:A9,E2,B2:B9,F2). A count of zero means there are no matching records. A positive count does not guarantee that the maximum range contains a numeric value: check that the corresponding cells in C2:C9 are numbers, not numbers stored as text.

Also distinguish a genuine maximum of zero from a no-match result. If matching records contain negative values, MAXIFS can return the largest negative number; zero should not automatically be treated as the answer in that case. A separate count or an explicit message formula can make the report clearer. With the same criteria cells and ranges, for example, =IF(COUNTIFS(A2:A9,E2,B2:B9,F2)=0,"No match",MAXIFS(C2:C9,A2:A9,E2,B2:B9,F2)) displays No match when no records satisfy both conditions and otherwise displays the maximum.

Common errors and ways to troubleshoot

If Excel displays #VALUE!, check that max_range and every criteria_range have matching dimensions. A range with a different number of rows or columns is a frequent cause. If Excel displays #NAME?, check the spelling of MAXIFS and confirm that the Excel version supports it. In older versions, a function unavailable in that release may not calculate.

  • The answer is unexpectedly zero: Check whether any rows match, and confirm that the relevant maximum cells contain numeric values.
  • Numbers appear to be ignored: Values imported as text are not numeric values for this calculation. Convert them to numbers, for example by using a suitable data-import or conversion method.
  • A text condition does not match: Look for extra spaces, inconsistent labels, or a criterion that refers to the wrong cell. TRIM can help clean unwanted ordinary spaces in source text, but check the result before replacing source data.
  • A date condition gives an unexpected result: Confirm that both the dates in the data and the boundary in the criteria cell are real Excel dates.
  • The formula changes when copied: Use dollar signs to lock ranges when appropriate. For example, references such as $A$2:$A$9 remain fixed when a formula is filled down, while E2 can remain relative if each row uses a different criterion.

Excel normally uses commas between function arguments in the examples here. Some regional settings use semicolons instead. If Excel rejects a correctly written formula, check the list separator configured for that installation rather than changing the function name.

MAXIFS compared with filters and older formulas

MAXIFS is usually the clearest choice when the goal is one maximum for one or more conditions. A table filter is useful when you want to inspect every matching record, but filtering does not by itself create a reusable maximum calculation. You can filter the data and use a separate calculation, but the formula is easier to place in a summary cell and update from criteria cells.

For a pre-MAXIFS Excel version, an array formula can calculate the largest value meeting one condition. With sales in C2:C9 and regions in A2:A9, enter =MAX(IF(A2:A9="East",C2:C9)). In older Excel versions without dynamic arrays, confirm the formula with Ctrl+Shift+Enter rather than Enter; Excel displays braces around the formula when it is entered as an array formula, and you should not type those braces yourself. Excel for Microsoft 365 evaluates this formula with Enter, but MAXIFS is generally more direct where it is available.

On Windows, Ctrl+Shift+Enter is the legacy array-formula shortcut. On a Mac, the equivalent for entering a legacy array formula is Control+Shift+Enter in supported desktop Excel versions. Excel for the web may not support every legacy array-formula editing workflow, so use MAXIFS or a current desktop version when possible. If you need the matching record or all rows tied for the maximum rather than only the maximum number, a different lookup or filtering approach is needed.

Frequently Asked Questions

Can MAXIFS use more than one condition?
Yes. Add a criteria-range and criteria pair for each additional condition. The conditions work together as AND requirements: the row must satisfy all of them.

Does MAXIFS return the row that contains the maximum?
No. It returns the maximum numeric value only. To identify the associated product, date, or record, use a separate lookup or filtering formula based on the maximum and the same criteria.

Can I find the maximum when a cell contains a particular word?
Yes. Use a wildcard such as an asterisk in the text criterion, for example Widget* to match text beginning with Widget. The corresponding criteria range must contain the text you want to test.

Why does MAXIFS return zero when I expected a value?
There may be no matching rows, or the matching cells in the maximum range may contain text rather than numbers. Check the criteria with COUNTIF or COUNTIFS and verify the values in the maximum range.

Can MAXIFS return every record tied for the maximum?
No. MAXIFS returns a single maximum value, even if multiple rows share it. Use that value with a separate filter or lookup method if you need to display all tied records.

Choosing a reliable formula

For most conditional-maximum calculations in current Excel, start with MAXIFS, keep each source range aligned, and use cell references for criteria that may change. Add conditions in pairs, verify that numeric and date values are stored in usable formats, and check for matching records when zero would be ambiguous. These checks turn a short formula into a dependable summary that continues to work as the worksheet changes.

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.