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

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

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

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.

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