Asikul Himel
While working in Excel, we need to find the maximum value under a given condition. The combination of the MAX and IF functions can help you to find the maximum value from a given data range with specific criteria. In this article, we will learn how to use the MAX IF function in Excel and explain all possible ways to find the maximum value under certain criteria.
Download Practice Workbook
What Is MAX IF Formula in Excel?
For understanding the MAX IF formula, we have to grasp two functions individually.
🔁 MAX Function
The MAX function is one of the most commonly used functions in Excel. It returns the maximum value from a selected range. The MAX function ignores the logical values and text. The syntax of the MAX function is given below.
MAX (number1, [number2], ...)
🔁 IF Function
The IF function is another essential function of Excel. The IF function returns a specified value, if a given logical test is satisfied. The syntax for the IF function is given here.
=IF(logical_test, [value_if_true], [value_if_false])
In this article, we will use the combination of the MAX function and the IF function. In general, the MAX IF formula returns the largest numeric value that satisfies one or more criteria in a given range of numbers, dates, texts, and other conditions. After combining these two functions, we get a generic formula like this.
=MAX(IF(criteria_range=criteria, max_range))4 Examples of Using the MAX IF Function in Excel
In this section of the article, we will discuss four suitable approaches to use the MAX IF function in Excel. Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.
1. Using MAX IF Function with an Array Formula
First, we will use the MAX IF formula with an array in Excel. We can use the MAX IF formula based on not only a single condition but also for multiple criteria. Here, we will discuss both of these scenarios.
1.1 Using MAX IF Formula with Single Criteria
In this section of the article, we will learn to use the MAX IF formula with one criterion. Let’s say we are given a range of data, like in the picture below. We need to find the maximum number of sales of the Sales Rep.
Now, let’s follow the steps mentioned below to do this.
Steps:
- Firstly, create a table anywhere in the worksheet, and in the name column, insert the names of the Sale Reps.
- After that, apply the MAX IF formula. Here, we want to find the maximum sales for “Alex”. The formula looks like this.
=MAX(IF(B5:B16=B19,D5:D16))Here, the range of cells B5:B16 indicates the cells of the Sales Rep. column, cell B19 refers to the selected Sales Rep., and the range of cells D5:D16 represents the cells of the Total Sale column.
Formula Breakdown
- Here, max_range is the Total Sale column (D5:D16).
- criteria is the name of the Sales Rep (B19).
- criteria_range Is the Sales Rep. column (B5:B16).
- Output → $3,000.
- Since, this is an array formula we have to complete this formula by closing all the brackets. So, Press SHIFT + CTRL + ENTER to do so.
So we have our maximum value. For the other two names, we will use the same formula.
1.2 Applying MAX IF Formula with Multiple Criteria
While working in Excel, sometimes we have to find the maximum value by satisfying multiple criteria. Using the MAX IF formula is a great way to do this. Let’s assume that we have more than one Sales Rep named “Alex”, “Bob”, and “John” in the Computer, Cycle, and Medicine category. Now we have to find the highest number of sales made by these Sales Reps in each category.
Now, let’s follow the instructions outlined below to do this.
Steps:
- Firstly, create a table anywhere in the worksheet and the name and the Category column insert the given criteria.
- Following that, apply the MAX IF formula. We want to find the maximum sales of “Alex” under the Computer category. The formula is given below.
=MAX(IF(B5:B20=B23,IF(C5:C20=C23,D5:D20)))Here, range of cells C5:C20 indicates the cells of the Category column, cell C23 refers to the selected category.
Formula Breakdown
- In the first IF function,
- C5:C20=C23 → It is the logical_test argument.
- D5:D20 → This indicates the [value_if_true] argument.
- Output → {FALSE;FALSE;FALSE;FALSE;FALSE;60;90;80;FALSE;FALSE;FALSE;FALSE;200;150;FALSE;FALSE}.
- In the 2nd IF function,
- B5:B20=B23 → This is the logical_test argument.
- IF(C5:C20=C23,D5:D20) → It refers to the [value_if_true] argument.
- Output → {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;90;80;FALSE;FALSE;FALSE;FALSE;FALSE;150;FALSE;FALSE}
- Now, the MAX function returns the maximum value from the array.
- Output → $150.
- Next, press SHIFT + CTRL + ENTER simultaneously to apply the formula. The final formula is this
As a result, we have found our maximum number.
- After that, apply the same formula to those other cells and you will get the following outputs.
Read More: How to Use Multiple IF Statements with Text in Excel
2. Utilizing Excel MAX IF Without an Array
We can get the same result without using the array formula. To do so, we can use the SUMPRODUCT function where we don’t have to press SHIFT + CTRL + ENTER. Let’s use the procedure discussed in the following section to do this.
Steps:
Here, we will use the data from the previous example. Our goal is to find as many sales as possible for ““Alex”” in the “Computer” category.
- Firstly, create a table as shown in the following picture.
- Following that, apply the formula given below in cell D23.
=SUMPRODUCT(MAX(((B5:B20=B23)*(C5:C20=C23)*(D5:D20))))Formula Breakdown
- Here, max_range denotes the Total Sale column (D5:D20)
- Criteria2 is the name of the Category (C23)
- criteria_range2 refers to the Category column (C5:C20)
- Criteria1 is the name of the Sales Rep (B23)
- criteria_range1 indicates the Sales Rep Column (B5:B20)
- Output → $150.
- Then, press ENTER and our maximum value will be available in cell D23 as demonstrated in the image below.
Read More: How to Use SUMPRODUCT IF in Excel (2 Suitable Examples)
3. Using MAX IF Formula with OR Logic
We can use the MAX IF formula in conjunction with OR logic. In this section of the article, we will discuss the detailed procedure to use the MAX IF formula with OR logic in Excel. So, let’s explore the guidelines given below.
Steps:
- Firstly, insert a new table as shown in the following image.
- After that, use the following formula in cell C24.
=MAX(IF((B5:B20=C22)+(B5:B20=C23),D5:D20))Here, cell C22 refers to the first selected name, and cell C23 indicates the second selected name.
Formula Breakdown
- Here, max_range is the Total Sale column (D5:D20).
- criteria2 is the name of the Category (C23).
- criteria_range2 refers to the Category column (B5:B20).
- criteria1 is the name of the Sales Rep (C22).
- criteria_range1 indicates the Sales Rep Column (B5:B20).
- Then, apply the formula by pressing SHIFT + CTRL + ENTER.
Consequently, we will get the maximum sales amount between “Alex” and “Bob” in cell C24.
4. Applying MAX IF Formula with AND Logic
We can also use the MAX IF formula with a combination of AND logic. Here, we will satisfy 2 criteria at a time to apply AND logic. Now, let’s use the instructions outlined below to do this.
Steps:
- Firstly, create a new table as shown in the following image.
- After that, use the following formula in cell C24.
=MAX(IF((B5:B20=C22)*(C5:C20=C23),D5:D20))Formula Breakdown
- Here, max_range represents the Total Sale column (D5:D20).
- criteria2 refers to the name of the Category (C23).
- criteria_range2 indicates the Category column (B5:B20).
- criteria1 is the name of the Sales Rep (C22).
- criteria_range1 is the Sales Rep Column (B5:B20).
- Next, hit ENTER.
As a result, you will have the following output on your worksheet as demonstrated in the image below.
How to Use MAXIFS Function in Excel
The MAXIFS function is a direct alternative to the MAX IF formula with multiple criteria. The users of Excel 2019 and Excel for Office 365 can have the same result by using the MAXIFS function. Let’s follow the steps mentioned below to use the MAXIFS function in Excel.
Steps:
- Firstly, insert a table and input your criteria as demonstrated in the following image.
Here, we need to find the maximum sales for “Alex”, “Bob”, and “John” in a given category.
- Afterward, use the formula given below in cell D22.
=MAXIFS($D$4:$D$20,$B$4:$B$20,B23,$C$4:$C$20,C23)Formula Breakdown
- Here, max_range is the Total Sale column ($D$4:$D$20).
- criteria_range1 is the Sales Rep. column ($B$4:$B$20).
- criteria1 Is the name of the Sales Rep (B23).
- criteria_range2 is the name of the Category column ($C$4:$C$20).
- criteria2 is the name of the Category (C23).
- Output → $150.
- After that, press ENTER and you will get the following output on your worksheet.
- Finally, use the AutoFill option of Excel to get remaining outputs.
Things to Remember
- The MAX IF is an Array Formula so in the older versions of Excel, you have to press SHIFT + CTRL + ENTER simultaneously to complete this formula.
- The MAXIFS function is only available for Excel 2019 and Office 365.
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.
Conclusion
So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to use the MAX IF function in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.
