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.

**Table of Contents**Expand

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

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,
is the*max_range**Total Sale*column (**D5:D16**). is the name of the*criteria**Sales Rep*(**B19**).Is the*criteria_range**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 theargument.*logical_test***D5:D20**→ This indicates theargument.*[value_if_true]***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 theargument.*logical_test***IF(C5:C20=C23,D5:D20)**→ It refers to theargument.*[value_if_true]***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 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**.

`=SUMPRODUCT(MAX(((B5:B20=B23)*(C5:C20=C23)*(D5:D20))))`

**Formula Breakdown**

- Here,
denotes the*max_range**Total Sale*column (**D5:D20**) is the name of the*Criteria2**Category*(**C23**)refers to the*criteria_range2**Category*column (**C5:C20**)is the name of the*Criteria1**Sales Rep*(**B23**)indicates the*criteria_range1**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.

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

`=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,
is the*max_range**Total Sale*column (**D5:D20**). is the name of the*criteria2**Category*(**C23**).refers to the*criteria_range2**Category*column (**B5:B20**).is the name of the*criteria1**Sales Rep*(**C22**).indicates the*criteria_range1**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 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**.

`=MAX(IF((B5:B20=C22)*(C5:C20=C23),D5:D20))`

**Formula Breakdown**

- Here,
represents the*max_range**Total Sale*column (**D5:D20**). refers to the name of the*criteria2**Category*(**C23**).indicates the*criteria_range2**Category*column (**B5:B20**).is the name of the*criteria1**Sales Rep*(**C22**).is the*criteria_range1**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.

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

`=MAXIFS($D$4:$D$20,$B$4:$B$20,B23,$C$4:$C$20,C23)`

**Formula Breakdown**

- Here,
is the*max_range**Total Sale*column (**$D$4:$D$20**). is the*criteria_range1**Sales Rep.*column (**$B$4:$B$20**).Is the name of the*criteria1**Sales Rep*(**B23**).is the name of the*criteria_range2**Category*column (**$C$4:$C$20**).is the name of the*criteria2**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.

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