Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles MIN AND MAX How to Get the Maximum in Excel IF There Are Multiple Criteria

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:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc