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.
Leave a Comment