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

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.

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

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.

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.

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.

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.

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

Here, range of cells C5:C20 indicates the cells of the Category column, cell C23 refers to the selected category.

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

• Following that, apply the formula given below in cell D23.

• Then, press ENTER and our maximum value will be available in cell D23 as demonstrated in the image below.

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.

• After that, use the following formula in cell C24.

Here, cell C22 refers to the first selected name, and cell C23 indicates the second selected name.

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

• After that, use the following formula in cell C24.

• Next, hit ENTER.

As a result, you will have the following output on your worksheet as demonstrated in the image below.

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.

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.

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

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