Find Maximum if multiple criteria in Excel

In this article, we will learn about how to find the Maximum value if it matches multiple conditions in Excel.

Scenario:

When working with long ranges of data, we need to find the maximum value among the range where more than one condition is matching. In simple words finding out the max value using Excel IF function. IF function returns True or False and Max function looks for the maximum value from the corresponding array.

Syntax to find max with multiple criteria

{=MAX (IF (Criteria1=match1),IF(Criteria2=match2, range_max))}

Note: Use Ctrl + Shift + Enter when working with arrays or ranges in Excel. This will generate Curly Braces on the formula by default. DO NOT try to put this curly braces manually.

Example:

All of these might be confusing to understand. So, let's test this formula via running it on the example shown below. Here we will perform the formula over values with criteria.

Use the formula:

{ =MAX( IF( B2:B11="East", IF( C2:C11 > 50 , D2:D11 )))}

criteria 1 is price must be from the region "East"

criteria 2 is price where quantity is greater than 50.

Explanation:

IF( C2:C11 > 50 , D2:D11 ) returns an array of FALSE values and price values where quantity is greater than 50.

{ FALSE ; 303.63 ; 108.46 ; 153.34 ; FALSE ; 95.58 ; 520.01 ; 90.27 ; 177 ; FALSE }

IF( B2:B11="East", IF( C2:C11 > 50 , D2:D11 )) returns an array of remaining price values where region is East.
MAX function finds the maximum value from the returned array and that would be the required PRICE value.

Here we matched the range (B2:B11) with value "East" and quantity (C2:C11) greater than 50, which returns the maximum from the price range (D2:D11). Press Enter to get the maximum of the range

As you can see we have the MAX value, if criteria match from the range of values.

Here are some observational notes using the above formula.

Notes:

The formula returns the MAX value from the range.
Use MAXIFS function in MS Excel 365 version to get the minimum value from data having multiple criteria. Learn more about MAXIFS function here.
Named range in the formula be used with correct keywords.

Hope this article about How to get the Maximum value with multiple criteria in Excel is explanatory. Find more articles on reference formulas here. If you liked our blogs, share it with your fristarts on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles

IF with AND function : Implementation of logic IF function with AND function to extract results having criteria in Excel.

IF with OR function : Implementation of logic IF function with OR function to extract results having criteria in excel data.

How to use nested IF function : nested IF function operates on data having multiple criteria. The use of repeated IF function is nested IF excel formula.

SUMIFS using AND-OR logic : Get the sum of numbers having multiple criteria applied using logic AND-OR excel function.

Minimum value using IF function : Get the minimum value using the excel IF function and MIN function on array data.

How to use wildcards in excel : Count cells matching phrases using the wildcards in excel

Popular Articles

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.