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.
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:
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.
Formula Applied =MAX (IF(A2:A10=F1, C2:C10))
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:
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:
Formula Applied = MAX(IF(C2:C11=H3, IF(D2:D11=H4, E2:E11)))
Using Multiplication operator:
Formula Applied = MAX(IF((C2:C11=H3)*(D2:D11=H4), E2:E11))
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.
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:
Formula Applied = =MAX(IF((C2:C11="Jan") + (D2:D11="Feb"), E2:E11))
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.
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:
Formula Applied =MAXIFS(E2:E11, C2:C11, H3, D2:D11, H4)
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.