Go Back

How to Get the Maximum in Excel IF There Are Multiple Criteria

We can get the maximum value of a dataset without considering any criteria by simply using the MAX or LARGE function in Excel. But what if we need to consider one or multiple criteria to get the maximum value? In this article, we will learn how to get the maximum if multiple criteria by using different methods.

Figure 1. How to Get Maximum If Multiple Criteria

Array Formula Approach

We can get the maximum if multiple criteria value of a dataset with the help of array formula approach based on MAX or LARGE function along with IF function in Excel. The array formula must be entered using Ctrl+Shift+Enter keyboard keys, and as a result of this operation, the formula is automatically surrounded by curly brackets ({ }).

Using MAX and IF Functions

We have a dataset of orders for multiple products in different regions and we need to get the maximum order amount of a given product in a given region. Using an array formula based on MAX and IF functions we can get the maximum if multiple criteria in the following formula:

=MAX(IF(B2:B14=F2,IF(C2:C14=G2,D2:D14)))

Figure 2. Using MAX and IF Functions

Using LARGE and IF Functions

Using an array formula based on LARGE and IF functions we can get the maximum order amount if there are multiple criteria in the following formula:

=LARGE(IF(B2:B14=F2,IF(C2:C14=G2,D2:D14)),1)

Figure 3. Using LARGE and IF Functions

Non-Array Formula Approach

Using special functions that natively handle the arrays we can get the maximum if multiple criteria in Excel, such as MAXIFS and SUMPRODUCT functions.

Using the MAXIFS Function

The MAXIFS function has been introduced in Excel 2016 to return the maximum value based on one or multiple criteria without using an array formula. Using the following MAXIFS formula we can get the maximum order amount for “Apple” in “East” region:

=MAXIFS(D2:D14,B2:B14,F2,C2:C14,G2)

Figure 4. Using the MAXIFS Function

Using SUMPRODUCT and MAX Functions

As the SUMPRODUCT function handles the arrays natively, so we can use this function along with MAX function to get the maximum if multiple criteria in Excel 2013 and earlier versions in the following formula:

=SUMPRODUCT(MAX((B2:B14=F2)*(C2:C14=G2)*(D2:D14)))

Figure 5. Using SUMPRODUCT and MAX Functions

Using SUMPRODUCT and LARGE Functions

Using SUMPRODUCT and LARGE functions we can get the maximum value of a dataset based on multiple criteria in the following non-array formula:

=SUMPRODUCT(LARGE((B2:B14=F2)*(C2:C14=G2)*(D2:D14),1))

Figure 6. Using SUMPRODUCT and LARGE Functions

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need to make a formula with sum if with multiple criteria
Solved by Z. D. in 24 mins
problem: Need lookup formula to pull multiple tracking numbers from another sheet using 2 criteria. column A: critieria 1 column B: criteria 2 column C: # Quantity Column D: desired tracking numbers (Multiple if possible, if column C has #2, there would be 2 tracking numbers) if not possible: column E: next tracking number from criteria 1 & 2
Solved by E. J. in 30 mins
Countif statement with multiple criteria!
Solved by C. U. in 22 mins

Leave a Comment

avatar