With regions in A2:A10 and sales amounts in C2:C10, use =MAXIFS(C2:C10,A2:A10,"East") to return the largest sale for East; in the example below, the result is 190. MAXIFS is the simplest option in current Excel when you need the maximum value among rows that meet one or more conditions.
What MAXIFS does
MAXIFS checks one or more criteria ranges, keeps the rows that satisfy the conditions, and returns the greatest numeric value from the corresponding cells in the maximum range. It is useful for questions such as “What was the highest sale in East?”, “What was the largest order for a particular product?” or “What was the highest score for a student who passed?”
For the opening example, imagine a table with region names in A2:A10, product names in B2:B10 and sales amounts in C2:C10. The formula looks for “East” in the region column, then compares the sales amounts on those same rows. The East values are 120, 175, 190 and 140, so the greatest is 190. Rows from West and North do not affect the answer.
The function is available in Excel for Microsoft 365 and Excel 2019 or later. In Microsoft 365 for Windows, enter the formula in a blank cell and press Enter. The same formula works in Excel for Mac and Excel for the web; the ribbon layout may differ, but no special menu command is needed.
MAXIFS syntax and argument order
The general syntax is =MAXIFS(max_range,criteria_range1,criteria1,[criteria_range2,criteria2],...). The first argument is the range containing the numbers to maximize. Each following pair identifies a range to check and the condition that range must meet. You can add more pairs when several conditions must all be true.
- max_range: The values Excel will compare, such as sales or scores.
- criteria_range1: The first column or range to inspect, such as region.
- criteria1: The required value or test, such as
"East"or">=100". - Additional pairs: Optional ranges and criteria, evaluated together as an AND condition.
Keep the ranges aligned: the maximum range and every criteria range should cover the same number of rows and columns. For example, use C2:C10 with A2:A10, not C2:C10 with A2:A11. Misaligned ranges can cause an error or produce results that do not correspond to the intended records. Using whole-column references is allowed, but bounded ranges or Excel Tables are often easier to audit and can avoid unnecessary calculation over unused cells.
Find a maximum with two or more conditions
To find the maximum for East and the Widget product, use the same table layout: regions in A2:A10, products in B2:B10 and sales in C2:C10. The formula is =MAXIFS(C2:C10,A2:A10,"East",B2:B10,"Widget"). It returns 120 if the only East Widget amounts are 120 and 190? In the example table, both are East Widgets, so the correct result is 190. Each criteria pair adds a requirement; a row must meet both conditions to contribute.
You can also apply a comparison to the maximum range itself. With regions in A2:A10 and sales in C2:C10, =MAXIFS(C2:C10,A2:A10,"East",C2:C10,">150") returns 190. It considers East rows whose sale is greater than 150, excluding East values of 120 and 140. The criterion is written as text, including the comparison operator.
For a threshold stored in a cell rather than typed into the formula, put the threshold in F2. With regions in A2:A10, sales in C2:C10 and the threshold in F2, use =MAXIFS(C2:C10,A2:A10,"East",C2:C10,">"&F2). The ampersand joins the greater-than operator to the cell value. This approach makes it easy to change the threshold without editing the formula.
Use dates, numbers and text criteria
MAXIFS criteria can match text, compare numbers, or test dates. For an exact text match, enter the required text in quotation marks in the formula, or refer to a cell containing it. For numeric tests, operators such as >, >=, < and <> can be joined to a value. The not-equal operator <> means “does not equal.”
For a date condition, use real Excel dates rather than text that only looks like a date. Suppose dates are in B2:B10, regions are in A2:A10 and sales are in C2:C10. To find the largest East sale dated on or after January 1, 2026, use =MAXIFS(C2:C10,A2:A10,"East",B2:B10,">="&DATE(2026,1,1)). This uses Excel’s DATE function to create an unambiguous date value. The result depends on the records in the stated ranges; make sure the date cells are stored as dates, not imported text.
Text criteria can include wildcards. An asterisk matches any number of characters, while a question mark matches one character. With product names in B2:B10 and sales in C2:C10, =MAXIFS(C2:C10,B2:B10,"Pro*") returns the largest sale for a product name that begins with “Pro.” To search for a literal asterisk or question mark, prefix that character with a tilde in the criterion. Exact text matches are usually clearer when the category name is known.
Keep criteria in cells for easier updates
Hard-coded criteria are quick for a one-off answer, but criteria cells make a report easier to reuse. Put the desired region in F2, the product in G2 and use the existing table layout of regions A2:A10, products B2:B10 and sales C2:C10. Then enter =MAXIFS(C2:C10,A2:A10,F2,B2:B10,G2). Changing F2 or G2 updates the result without changing the formula.
For comparison criteria, a cell reference usually needs to be combined with the operator. With the same region and sales ranges and a minimum amount in F2, use =MAXIFS(C2:C10,A2:A10,"East",C2:C10,">"&F2). If F2 contains 150, Excel tests for values greater than 150. Remember that “greater than 150” excludes 150; use >= when the threshold itself should qualify.
If the source data is an Excel Table, structured references can make formulas more readable and automatically adjust as rows are added. For a table named SalesData with columns Region and Amount, use =MAXIFS(SalesData[Amount],SalesData[Region],"East"). To rename a table or inspect its name, select a table cell and use the Table Design tab. Table names and column headings must match the workbook exactly.
Check for no matches and misleading zero results
One important behavior is that MAXIFS returns 0 when no rows satisfy the criteria. That can look like a genuine result, especially if a report uses zero as a meaningful value. It can also be misleading when all valid matching amounts are negative, because zero is greater than every negative number. Check whether the criteria actually matched before interpreting a zero.
With regions in A2:A10 and sales in C2:C10, this version displays a message when no East row exists: =IF(COUNTIF(A2:A10,"East")=0,"No match",MAXIFS(C2:C10,A2:A10,"East")). COUNTIF checks whether the criterion appears, and IF chooses between the message and the maximum. If matches can exist but their amount cells are blank or nonnumeric, validate the amount data too; a matching category alone does not guarantee a usable numeric value.
Before trusting a result, check the source rows, criteria spelling and data types. “East” and “East ” (with a trailing space) may not behave as intended, and numbers imported as text may not be compared as numeric amounts. Also confirm that the maximum range points to the measure you mean to analyze, rather than an adjacent column such as quantity or unit price.
Older Excel alternatives and related methods
If MAXIFS is unavailable in an older Excel release, an array formula using MAX and IF can perform the same single-condition task. With regions in A2:A10 and sales in C2:C10, enter =MAX(IF(A2:A10="East",C2:C10)). In Excel versions that do not support dynamic array calculation, confirm it with Ctrl+Shift+Enter on Windows rather than Enter alone. On Mac, use Control+Shift+Return. Excel displays braces around a confirmed legacy array formula; do not type the braces yourself. In current Microsoft 365, Enter is sufficient.
A PivotTable is another option when you need to explore several categories rather than calculate one result. Select the data, choose Insert > PivotTable, place the category field in Rows and the numeric field in Values, then change the value summary from Sum to Max through Value Field Settings. A PivotTable is convenient for comparing groups, but it summarizes a report layout rather than placing a single criteria-based formula in a cell. Use MAXIFS when a formula-driven result should update as criteria cells change.
Common mistakes to avoid
- Putting arguments in the wrong order: MAXIFS starts with the maximum range, followed by criteria-range and criteria pairs. It does not start with the condition.
- Using unequal range sizes: Keep all referenced ranges aligned to the same rows.
- Forgetting quotation marks: Type text criteria as text, and put comparison tests such as
">=100"in quotation marks. When joining a comparison with a cell, use the ampersand. - Expecting OR logic from separate criteria pairs: Separate pairs mean AND: every condition must be true for a row to count.
- Misreading zero: A zero result can indicate no qualifying rows, not necessarily a real maximum of zero.
- Using text in the amount column: Confirm that amounts are numeric and dates are actual Excel dates before relying on the result.
For OR logic—for example, the maximum for either East or West—you can calculate each maximum separately and take the greater result. With regions in A2:A10 and sales in C2:C10, use =MAX(MAXIFS(C2:C10,A2:A10,"East"),MAXIFS(C2:C10,A2:A10,"West")). This returns 210 for the example data, the greater of East’s 190 and West’s 210. Be aware that a category with no match contributes zero, which matters if the other category’s qualifying amounts are negative.
Frequently Asked Questions
Can MAXIFS use more than one criterion?
Yes. Add a criteria-range and criterion pair for each additional condition. All pairs must be satisfied by a row, so the conditions work as AND logic. For OR logic, compare separate MAXIFS results or use a different formula approach suited to the workbook.
Why does MAXIFS return 0 when there is no matching record?
That is the function’s no-match result. Check whether the criteria match any rows, and consider wrapping the calculation in IF with COUNTIF or COUNTIFS if the report should show “No match” instead of zero.
Can I find the maximum for a date range?
Yes. Add the date column as a criteria range twice: once for the start date and once for the end date. Join operators such as >= and <= to real date values or date cells. This includes both endpoints when you use those operators.
Does MAXIFS return the row or name associated with the maximum?
No. MAXIFS returns only the maximum value. To return the corresponding person, order number or other field, use a lookup formula based on the maximum and criteria, taking care to decide what should happen if multiple rows share the same maximum.
What if my Excel version does not recognize MAXIFS?
MAXIFS is available in Excel 2019 and later, including Microsoft 365. Older versions can use the MAX(IF(…)) array formula shown above, confirmed with the appropriate array-entry shortcut, or a PivotTable for a grouped summary.
Related
- Excel Data Validation Lists: Hard-Coded or Flexible?
- Excel Data Validation List: How to Exclude Blanks in Excel
- Excel Data Validation List From Another Sheet: Steps
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add a Header to Just the First Page in WordTo add a header only to page one in Word, open the header, select Different First Page on…
- Outlook Routing Rules: How to Create and Manage ThemOutlook routing rules automatically handle messages that match conditions you choose—for example, moving mail from a supplier into…
- Excel Keyboard Shortcuts: How to Change or Customize ThemIn Excel for Microsoft 365 on Windows, you cannot use a built-in settings page to remap any keyboard…
- How to Normalize Names in Excel for Cleaner, Consistent DataTo normalize names in Excel, keep the original names in column A and enter =PROPER(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))) in B2,…
- Creating a Signature in Microsoft Outlook: Setup GuideTo create an Outlook signature, open the signature settings for your version of Outlook, add and format the…
- Excel Power Query: Keep Leading ZerosYes. To keep leading zeros in Excel Power Query, make the affected column’s data type Text before loading…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.