Excel MAXIFS Formula: Find the Highest Value Under Multiple Criteria

Isolating peak performance metrics from dense, multi-layered datasets often feels like an uphill struggle. While tracking standard funding sources or basic departmental budgets is straightforward, extracting the absolute maximum value under highly specific, overlapping criteria remains a common frustration for analysts.

Fortunately, mastering the MAXIFS formula grants users immediate clarity, instantly filtering out noise to pinpoint top-tier outliers, such as identifying the peak Q4 marketing spend across diverse regional portfolios.

Stipulation: This streamlined approach requires Excel 365 or Excel 2019; older versions must rely on complex MAX(IF()) array formulas.

Below, we will explore the exact formula syntax, step-by-step implementation methods, and key troubleshooting tips to optimize your data analysis.

In data analysis, finding extreme values-such as the highest sales revenue, the maximum temperature, or the top test score-is a routine task. While Excel's basic MAX function makes it simple to find the largest number in a continuous range, real-world scenarios are rarely that straightforward. Often, you need to find the highest value that meets a specific set of conditions. For instance, you might need to determine the highest sales figure for a specific region, for a specific product line, during a specific quarter.

Depending on your version of Excel, there are several powerful formulas you can use to perform this multi-criteria search. In this comprehensive guide, we will explore the three most effective methods: the modern MAXIFS function, the dynamic MAX and FILTER combination, and the classic MAX and IF array formula designed for legacy versions of Excel.

The Sample Data Set

To demonstrate these formulas in action, we will use the following sample sales database. It contains information about regional sales across different product categories and quarters.

Our objective in the following examples is to find the highest revenue achieved in the North region for the product Laptop.

Method 1: The Modern Standard – MAXIFS

If you are using Excel 2016, Excel 2019, Excel 2021, or Microsoft 365, the MAXIFS function is the easiest and most efficient tool for this job. It is designed specifically to return the maximum value among cells filtered by one or more conditions.

The Syntax of MAXIFS

The syntax of the function is straightforward:

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• max_range: The actual range of cells in which you want to find the highest value (e.g., numeric values like revenue, grades, or dates).
• criteria_range1: The range of cells that you want to evaluate with your first condition.
• criteria1: The condition or criteria that defines which cells in criteria_range1 will be filtered.
• [criteria_range2, criteria2], ...: (Optional) Additional ranges and their corresponding criteria. You can add up to 127 criteria pairs.

Applying MAXIFS to Our Sample Data

To find the maximum revenue for "Laptop" in the "North" region from our table, use this formula:

=MAXIFS(D2:D10, A2:A10, "North", B2:B10, "Laptop")

How It Works

1. Excel evaluates the first criteria range (A2:A10) and matches rows where the value is "North" (Rows 2, 4, 5, 7, and 9).
2. Excel evaluates the second criteria range (B2:B10) and matches rows where the value is "Laptop" (Rows 2, 5, 6, 9, and 10).
3. It identifies the intersection where both conditions are true (Rows 2, 5, and 9).
4. Finally, it looks at the target values in D2:D10 for these matching rows ($12,000, $18,500, and $11,000) and returns the maximum value: $18,500.

Tip: Instead of hardcoding text like "North" and "Laptop" inside the formula, you can reference cells (e.g., =MAXIFS(D2:D10, A2:A10, F1, B2:B10, F2)) to make your models dynamic and interactive.

Method 2: The Dynamic Array Approach – MAX & FILTER

For Microsoft 365 and Excel 2021 users, the dynamic array engine introduces a highly intuitive and readable alternative: combining the classic MAX function with the modern FILTER function.

The Syntax

=MAX(FILTER(array, include, [if_empty]))

• array: The range containing the values you want to evaluate (the target max range).
• include: A boolean array or formula that represents your criteria. Multiple criteria are multiplied together to represent an "AND" condition.

Applying MAX and FILTER

To implement this on our sample data, use the following formula:

=MAX(FILTER(D2:D10, (A2:A10="North") * (B2:B10="Laptop"), 0))

How It Works

The core logic lies in the multiplication of logical arrays within the FILTER function:

• (A2:A10="North") returns an array of TRUE/FALSE values: {TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}.
• (B2:B10="Laptop") returns another array: {TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE}.
• When multiplied together, TRUE behaves as 1 and FALSE as 0. The multiplication results in: {1; 0; 0; 1; 0; 0; 0; 1; 0} (representing rows where both conditions are met).
• The FILTER function uses this binary mask to isolate the revenues: {$12,000; $18,500; $11,000}.
• The outer MAX function evaluates this filtered array and returns $18,500.

This method is highly flexible and useful if you need to build advanced formulas where MAXIFS might fail, such as when applying case-sensitive criteria or transforming data on the fly before checking conditions.

Method 3: Legacy Compatibility – MAX and IF (Array Formula)

If you are working with Excel 2013, 2010, or older versions, the MAXIFS and FILTER functions are not available. In these legacy environments, you must use an array formula combining MAX and nested IF statements.

The Syntax

=MAX(IF(criteria_range1=criteria1, IF(criteria_range2=criteria2, max_range)))

Applying the Legacy Formula

Write the following formula in your cell:

=MAX(IF(A2:A10="North", IF(B2:B10="Laptop", D2:D10)))

CRITICAL STEP: Because this is an array formula in older Excel versions, you cannot simply press Enter. You must press Ctrl + Shift + Enter simultaneously. When done correctly, Excel will automatically wrap your formula in curly braces, making it look like this:

{=MAX(IF(A2:A10="North", IF(B2:B10="Laptop", D2:D10)))}

How It Works

The nested IF statements act as gatekeepers. The outer IF checks for "North" and filters the dataset. The inner IF checks those results for "Laptop". For any row that fails either test, Excel assigns a value of FALSE. The final evaluated array looks like this:

{12000; FALSE; FALSE; 18500; FALSE; FALSE; FALSE; 11000; FALSE}

Because the standard MAX function automatically ignores logical FALSE values, it evaluates only the remaining numeric values ($12,000, $18,500, and $11,000) and returns the correct result: $18,500.

Handling Advanced Scenarios

Real-world analytical workflows often feature nuances that require tweaking these formulas. Below are common challenges and how to solve them.

1. Handling Empty Cells or Zero Values

If your criteria matches rows that contain blank cells in the target range, MAXIFS and the alternative formulas might evaluate those empty spaces as 0. If you are dealing with negative numbers, a blank evaluated as zero could skew your results.

To solve this, add another criterion to ensure empty cells are ignored:

=MAXIFS(D2:D10, A2:A10, "North", B2:B10, "Laptop", D2:D10, "<>")

The operator "<>" instructs Excel to only consider cells in the target range (D2:D10) that are not empty.

2. Case-Sensitive Matching

Standard Excel functions like MAXIFS are case-insensitive; "laptop" and "LAPTOP" are treated as identical. If your data distinguishes between product codes based on letter case, you can combine MAX, FILTER, and the EXACT function:

=MAX(FILTER(D2:D10, EXACT(B2:B10, "Laptop") * (A2:A10="North")))

The EXACT function forces a character-by-character, case-sensitive match, ensuring precision in code-sensitive databases.

3. Finding the Highest Value with Wildcards

If your criteria are partial matches, you can use wildcards in the MAXIFS function. The asterisk (*) represents any number of characters, while the question mark (?) represents a single character.

For example, to find the highest revenue for any product starting with "Lap" in the North region, write:

=MAXIFS(D2:D10, A2:A10, "North", B2:B10, "Lap*")

Summary of Methods

Conclusion

Finding the maximum value under multiple criteria is a core data analysis skill. If you are on Microsoft 365 or Excel 2019/2021, stick with MAXIFS for its speed, simplicity, and readability. When building complex solutions requiring case sensitivity or real-time array transformations, pivot to the MAX(FILTER()) paradigm. Finally, if you are maintaining legacy workbooks, fall back on the classic CSE (Ctrl+Shift+Enter) array formula to ensure backward compatibility. Use these methods to streamline your business reporting, inventory control, and financial modeling.