Excel MAX IF Formula: To get highest value with conditions

We have already covered the implementation, definition and examples of MAX function in our previous tutorial. MAX function is used to return the largest number in a dataset. In some situations, however, you may need to drill down into your data further to find the max value based on certain criteria.

This tutorial will cover a few different methods to fetch the max value in Excel worksheets on the basis of one or several conditions that you specify.

1. MAX IF formula to work with one condition
2. MAX IF formula to work with multiple conditions
3. MAX IF formula working without array
4. Integrating Excel MAX IF with OR logic
5. Implementing MAXIFS function.

Excel MAX IF formula: with one condition

Only recently, Microsoft Excel introduced the built-in MAXIFS function with Excel 2019 version to implement conditional max easily.

Before that, with Excel 2016 and earlier versions, Excel users have to customize the formula by combining the MAX function with IF statement.

Take a real-time example to see how the traditional MAX IF function works. Below is the data table where we are given the sales reports of various agents.

The table includes the three-month sales report, and you are looking for the values for the best salesperson, Eva. Do the following:

1. With the salesperson names in A1:A15 and sales report in C1:C15, the formula will become as follows:

Formula Applied =MAX(IF(A1:A15="Eva", C1:C15))

NOTE: Since we are using an Array function, it won't work using the traditional enter key. An array formula must always be entered simultaneously by pressing Ctrl + Shift + Enter keys.

1. The formula will return the highest sales value for salesperson "Eva".

1. Instead of directly adding the criteria in the formula cell, putting them in a cell is more appropriate. In such cases, we need not change the formula repeatedly; changing the criterion cell is sufficient. We can conclude the following formula and get the output:

Formula Applied =MAX (IF(A2:A10=F1, C2:C10))

1. You will notice both the formulas will return the same output.

How this formula works

At first, the IF function will work internally and match the given criteria_Range (A1:A15) with the target name Eva. The output of this operation will be returned either TRUE or False; if the criteria are matched, it will be true, or else it will return False. In our case, since it has found "Eva", so it will return true.

For the value_ if_true argument, we supply the long jump results (C2:C10), so if the logical test evaluates to TRUE, the corresponding number from column C is returned. The value_ if_false argument is omitted, meaning will just have a FALSE value where the condition is not met:

This array is fed to the MAX function, which returns the maximum number ignoring the FALSE values.

NOTE: To fetch the internal arrays, all you need to do is to select the equivalent part of the formula in your Excel sheet and press the F9 key. IF you want to exit the formula evaluation mode, simply press the Esc key.

MAX IF formula to work with multiple conditions

In the above section we covered how to implement MAX IF formula on the basis of one single condition. What if you want to find max value based on two or more conditions. In such situations, opt any one of the following methods:

1. Nested IF: to handle multiple criteria

1. Using Multiplication operation: to include additional criteria

NOTE: The curly brackets at the start and end of formula indicates that it is array formula.

For example, we are given the quarterly sales report of a company, and we are asked to find the name of the female salesperson who made the max sales in April. To solve the above problem, do the following:

1. We will enter two criteria, the first criterion (female) in C1, the second criterion (April month) in C2, and it will integrate these criteria by using the IF function. It will be shaped in the following formula:

Formula Applied = MAX(IF(C2:C11=H3, IF(D2:D11=H4, E2:E11)))

1. To get the output of the array formula, press Ctrl + Shift + Enter keys simultaneously and it will return the max value by matching the given criteria.

Using Multiplication operator:

1. For this, we will enter two criteria, the first criterion (female) in C1, the second criterion (April month) in C2 and use the multiplication operator (*) to apply both the criteria. The formula will become:

Formula Applied = MAX(IF((C2:C11=H3)*(D2:D11=H4), E2:E11))

1. You will have the following output:

When you compare both results (see the screenshot below), you will notice they have returned the same output. Though both the formulas are easy, comparatively, the formula with the Boolean logic is preferred the most as it enables the users to add as many conditions as they want without any hassle of nesting multiple Ifs.

MAX IF formula working without array

Many Excel users consider array formulas a bit tough in Excel and always avoid using them wherever possible. To cater to these, Microsoft Excel has provided a few built-in functions to handle arrays. One such built-in Excel function is the SUMPRODUCT function.

The SUMPRODUCT and MAX functions can be collectively together in a single formula using the below given syntax:

In the above syntax, we have integrated two criteria, though if required you can easily insert more range/criteria pairs in the above syntax.

We have taken the same data (as used in the earlier examples) to see the formula in action. Using the formula, we want to fetch the maximum sales report of sales_person "Reema" done in the month of "April".

Formula Applied =SUMPRODUCT(MAX(((B2:B16=G1) * (C2:C16=G2) * (D2:D16))))

The advantage of choosing it over the above formulas is that you do not need to run this formula by pressing the CTRL+ SHIFT + Enter keys; you will get the result only with a single enter key. You will have the following output:

Integrating Excel MAX IF with OR logic

In the previous examples, we used the multiplication operator, which behaved like an AND operator. The advantage of using the AND operator is it only returns are output if all the criteria are met. What if you want to find the max value even if a single criterion?

To cater to the above requirement, we will use the OR logic, where instead of multiplying the conditions, we will add them to the MAX IF formula.

Syntax

For example, we are given the quarterly sales report of a company, and want to find the name of the sales who did max sales in the month of Jan, Feb. To solve the above problem, do the following:

1. We will enter two criteria, the first criterion (Jan month) in C1 and the second criterion (Feb month) in C2. It will integrate these criteria by using the + operator in the IF function. It will be shaped in the following formula:

Formula Applied = =MAX(IF((C2:C11="Jan") + (D2:D11="Feb"), E2:E11))

1. To get the output of the array formula, press Ctrl + Shift + Enter keys simultaneously and it will return the max value by matching the given criteria.

You can also incorporate the SUMPRODUCT function to solve the above problem. Use the following SUMPRODUCT function:

Formula Applied =SUMPRODUCT(MAX(((C2:C11="Jan") + (D2:D11="Feb")) * C2:C10))

Since its not an array formula, therefore, simply press the enter key to run the output.

NOTE: The SUMPRODUCT function only works with numeric data, for text values it would return 0 as output.

Implementing MAXIFS function.

So far, in this tutorial, we have covered a different combination of the MAX IF formula to fulfill our requirements. However, you need not play around with those troublesome array formulas anymore! With the latest Excel versions, 2019, 2021, and Excel 365, the users can use the MAXIFS formulas and integrate as many criteria without using IF or the multiplication operator. The advantage of using this function, it can process the range that contains both numeric and text data. Since it is not an array formula, it can fetch the output with the enter key.

Syntax

To understand it better, we are taking the same data (as used in the earlier examples). Using the MAXIFS formula, we will to fetch the maximum sales report of sales_person "Reema" done in the month of "April". Below are the steps:

1. In the first parameter, we will enter the range for which you want to find the maximum number. In the later parameters, we will enter the criteria ranges (you can enter up to 126 criteria/ranges).

Formula Applied =MAXIFS(E2:E11, C2:C11, H3, D2:D11, H4)

1. Since MAXIFS is not an array formula, press the enter key, and it will return the following output.

That it's with MAX IFS today. Try all the possible MAX IFS options. Let's catch up in next tutorial with some fascinating Excel topic and real-time examples.