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.

# 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. 