Find Max Value in Excel With Criteria Using MAXIFS

How-to basics · Updated November 20, 2026

To find the largest value that meets conditions in Excel, use MAXIFS. With sales in D2:D7, salespeople in B2:B7, and regions in C2:C7, this formula returns the highest sale for Maya in the West: =MAXIFS(D2:D7,B2:B7,"Maya",C2:C7,"West"). In the example data below, the result is 920.

What “maximum with criteria” means

A regular maximum finds the largest number in a range, regardless of what each number represents. A conditional maximum adds a filter: first consider only rows that meet one or more conditions, then return the largest numeric value among those rows. For example, you might want the highest sale in one region, the latest delivery date for a particular supplier, or the greatest score for a selected category.

Imagine a worksheet with a salesperson in column B, a region in column C, and a sale amount in column D. The formula in the opening example looks for rows where the salesperson is Maya and the region is West, then compares only the corresponding amounts. If the matching amounts are 540, 920, and 650, the maximum is 920. It does not return the row number or identify the salesperson; it returns the maximum value itself.

For a plain maximum without conditions, Excel has MAX. For the conditional version, MAXIFS is usually the clearest option in current Excel. It is available in Microsoft 365 and Excel 2019 or later, including Excel for the web. The examples here use Excel for Microsoft 365 on Windows.

Use MAXIFS for one or more conditions

The first argument to MAXIFS is the range containing the values to compare. After that, provide a range to test and the criterion it must meet. Add more criteria-range and criterion pairs when the result must satisfy multiple conditions. Each additional condition is combined with the others, so every listed condition must be true for a row to qualify.

With sale amounts in D2:D7 and regions in C2:C7, this formula finds the largest sale in the West, regardless of salesperson: =MAXIFS(D2:D7,C2:C7,"West"). In the example data, the West sales are 540, 920, 430, and 650, so the result is 920.

With the same columns, plus salesperson names in B2:B7, this formula requires both a Maya match and a West match: =MAXIFS(D2:D7,B2:B7,"Maya",C2:C7,"West"). If you want users to change the criteria without editing the formula, put the selected salesperson in F2 and region in G2. With those inputs, use: =MAXIFS(D2:D7,B2:B7,F2,C2:C7,G2). If F2 contains Maya and G2 contains West, it returns 920.

Keep the ranges aligned: the maximum range and each criteria range should cover the same rows and have the same shape. For example, pairing D2:D7 with C2:C100 is a range-size mismatch and can produce an error. When copying formulas down a worksheet, use absolute references such as $D$2:$D$7 if the data ranges should stay fixed while the criteria-cell references change.

Set criteria for text, numbers, and dates

A criterion can be an exact value, a cell reference, or a comparison such as greater than or equal to a number. For example, if product categories are in B2:B100 and amounts are in D2:D100, use this formula to find the maximum amount for rows with a value of at least 100: =MAXIFS(D2:D100,D2:D100,">=100",B2:B100,"Hardware"). The text criterion is not case-sensitive, so “hardware” and “Hardware” match the same text.

For a criterion stored in a cell, join the operator to the cell reference with the ampersand operator. With amounts in D2:D100, categories in B2:B100, and the minimum amount in F2, this returns the largest amount for Hardware that meets the threshold: =MAXIFS(D2:D100,D2:D100,">="&F2,B2:B100,"Hardware"). This construction is useful whenever the comparison value may change.

Dates are stored as numbers in Excel, so date criteria can use comparison operators too. With transaction dates in A2:A100 and amounts in D2:D100, put the first day of the period in F2 and the day after the period ends in G2. This formula returns the largest amount on or after the start date but before the ending boundary: =MAXIFS(D2:D100,A2:A100,">="&F2,A2:A100,"<"&G2). Using an exclusive next-day boundary is particularly helpful when the source dates include times: it includes every time on the final calendar day without needing to guess the latest possible time.

For partial text matches, Excel supports the wildcard characters * for any number of characters and ? for one character. With names in B2:B100 and amounts in D2:D100, this finds the largest amount associated with a name beginning with M: =MAXIFS(D2:D100,B2:B100,"M*"). To search for a literal asterisk or question mark, precede it with a tilde, such as ~*.

Find a maximum when criteria allow alternatives

Multiple criteria pairs in one MAXIFS formula mean “this condition and that condition.” They do not mean “this condition or that condition.” To find a maximum for either of two separate categories, calculate each conditional maximum and compare those results with MAX.

With amounts in D2:D7 and regions in C2:C7, this formula finds the maximum for East or West: =MAX(MAXIFS(D2:D7,C2:C7,"East"),MAXIFS(D2:D7,C2:C7,"West")). The East maximum in the example data is 780 and the West maximum is 920, so the final result is 920. This method is convenient for a short list of alternatives. If the list is long or changes often, consider organizing the criteria in cells and using a more structured formula or a helper calculation rather than adding many nested functions.

Check no-match results, zeros, and negative values

A result of zero does not always mean that a matching row contains a zero. MAXIFS returns 0 when no cells meet all the criteria. That can be misleading if zero is a legitimate value, or if the matching data contains only negative numbers. A useful check is to count matching rows separately so the worksheet can distinguish “no match” from a numeric maximum.

With salespeople in B2:B7, regions in C2:C7, amounts in D2:D7, the requested salesperson in F2, and region in G2, this formula displays a message when there are no qualifying rows: =IF(COUNTIFS(B2:B7,F2,C2:C7,G2)=0,"No matching rows",MAXIFS(D2:D7,B2:B7,F2,C2:C7,G2)). If F2 is Nina and G2 is West, and no such row exists, the result is “No matching rows.” If a match exists, the formula returns the maximum amount for it.

Make sure the maximum range contains numbers in the rows you expect to compare. Blank cells and text entries are not numeric maximum values, so a number stored as text may not behave like a real number. Check whether imported amounts are aligned to the left or have a warning indicator, and convert them to numbers if necessary. Also verify that there are no unnoticed spaces in criteria values; “West ” with a trailing space may not match “West.”

Use an alternative in older Excel

If the workbook must run in an Excel version without MAXIFS, an array formula can calculate a conditional maximum. In older Excel, enter the formula with Ctrl+Shift+Enter on Windows rather than pressing Enter alone. Excel displays braces around a confirmed legacy array formula; do not type the braces yourself. On Excel for Mac, the legacy array-entry shortcut is generally Control+Shift+Return, although keyboard labels can vary by device and version.

With values in D2:D7 and the matching salesperson names in B2:B7, this array formula returns the largest amount for Maya: =MAX(IF(B2:B7="Maya",D2:D7)). In the example, it returns 920. Microsoft 365 and newer Excel versions support dynamic arrays, so this form can generally be entered with Enter there; for a workbook shared with older installations, confirm how the recipient’s Excel version handles array formulas. For new workbooks, prefer MAXIFS when it is available because the criteria are easier to read and extend.

Common errors and ways to verify the result

When the answer looks wrong, check the logic as well as the formula syntax. A maximum is easy to verify by filtering the source data to the same criteria and sorting the amount column from largest to smallest. In Excel for Microsoft 365 on Windows, select the data and use Data > Filter; apply the same salesperson, region, or date conditions and inspect the largest visible amount. On Mac and in Excel for the web, the Filter command is also on the Data tab, though the ribbon layout may differ slightly.

  • Confirm the ranges: the maximum range must point to the numeric values, not the category or criteria column.
  • Confirm every condition: an extra criteria pair narrows the results because all pairs must match.
  • Check dates and numbers: text that looks like a date or number may not compare as expected until converted to a real Excel value.
  • Test for a match: use a matching-row count if zero could mean either “no qualifying row” or a real result.
  • Check formula separators: formulas shown here use commas, as in standard English-language Excel settings; some regional settings use semicolons as argument separators.

Filtering is a useful independent check, but it does not replace a formula when the result needs to update automatically as data changes. For a reusable report, keep criteria in clearly labeled input cells, use consistent data types, and make the formula ranges large enough for expected new records. Excel tables can also make source data easier to maintain as rows are added, but check the structured-reference formula carefully when building it.

Frequently Asked Questions

Can MAXIFS use more than one criterion?
Yes. Add a criteria-range and criterion pair for each condition. The conditions are joined with AND logic, meaning a row must satisfy all of them to contribute its value to the maximum.

Why does MAXIFS return 0 when there is no matching row?
Zero is the function’s result when no cells meet the criteria. Use COUNTIFS in an IF test to display a message or another result when the match count is zero.

Can MAXIFS return the name or row containing the maximum?
No. It returns the maximum value only. To identify the corresponding record, use a lookup formula or filter the source data for the criteria and maximum value. If the maximum occurs more than once, decide whether you want the first matching record or every tied record before choosing a lookup method.

Does MAXIFS distinguish uppercase and lowercase text?
No. Its text comparisons are not case-sensitive. If capitalization must determine whether a row qualifies, a case-sensitive approach using functions such as EXACT requires a different formula.

Can I find the highest value between two dates?
Yes. Use the date column as a criteria range twice: once for a start-date condition and once for an end-date condition. An inclusive start and exclusive next-day boundary are a reliable pattern, especially when timestamps may be present.

What is the simplest option if my Excel version lacks MAXIFS?
Use a conditional array formula such as =MAX(IF(criteria_range=criterion,max_range)), adjusted to your actual ranges. In older Excel, confirm it with Ctrl+Shift+Enter on Windows; in current Microsoft 365, MAXIFS is usually simpler to maintain.

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.