How to Use MAX IF Function in Excel (4 Practical Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


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))

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 Excel 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 Inserting 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.

Using MAX IF function with Single Criteria in Excel

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.

Using excel max if function in Excel

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

Final output of method 1.1 to use MAX IF function in Excel


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.

Applying MAX IF function with Multiple Criteria in Excel

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.

Final output of method 1.2 to use MAX IF function in Excel

Read More: How to Use IF Function with Multiple Conditions in Excel


2. Inserting Excel MAX-IF Function 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.

Utilizing Excel MAX IF function Without an Array in Excel

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

Final output of method 2 to use MAX IF function in Excel


3. Combining 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.

Using MAX IF function with OR Logic in Excel

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

Final output of method 3 to use MAX IF function in Excel


4. Applying MAX-IF Formula with AND Logic in Excel

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.

Applying MAX IF function with AND Logic in Excel

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

Final output of method 4 to use MAX IF function in Excel

Read More: How to Make Yes 1 and No 0 in Excel


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

How to Use MAXIFS Function in Excel 

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 the remaining outputs.

Final output of method 5 to Use MAXIFS Function in Excel 


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.

Download Practice Workbook


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.


Related Articles


<< Go Back to Excel IF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo