While working in excel, we need to find the maximum value under a given condition. The combination of the MAX function and IF function can help you to find the maximum value from a given data range with the specific criteria. In this article, we will learn how to use the MAX IF function in excel and explain all possible ways to find maximum value under certain criteria.

**Table of Contents**hide

## Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

## The Excel MAX IF Function: Definition

The **MAX IF** function is the combined form of the** MAX** and the **IF** function. The **MAX** function returns the maximum value from the given range of data, and the **IF** function helps to find that value based on given criteria. Users of Excel 2019 or Office 365 can easily find this conditional maximum value but In Excel 2013 and earlier versions, you still have to use the **MAX IF** formula.

### Summary

The Excel MAX IF formula returns the largest numeric value that satisfies one or more criteria in a given range of numbers, dates, text, and other conditions.

### Syntax

The MAX IF is an Array Formula and the format is

### Argument

**max_range**is the range of cells in which the maximum value will be determined (required).**criteria_range**Is the set of cells provided with the criteria (required).

### Return Value

Returns the maximum value under certain conditions.

## Excel Max If Function: 4 Approaches

### 1. MAX IF Formula with One Criteria

We will learn this type by following these steps

**Step-1: **

Let’s say, we are given a range of data like given in this picture below. We need to find the maximum number of sales of the **Sales Rep. **

**Step-2:**

Now, create a table anywhere in the worksheet, and in the name column insert the names of the **Sale Rep’s.**

**Step-3:**

Apply the “**MAX IF”** formula. We want to find the maximum sales for “**Alex”.**

Here,

**Max_range**is the Total Sale column (**D4:D15**)**criteria**is the name of the Sales Rep (**F4**)**Criteria_range**Is the Sales Rep. Column (**B4:B15**)

The formula looks like this

**Step-4:**

Since this is an array formula we have to complete this formula by closing all the brackets.

Press **SHIFT+CTRL+ENTER **to do so.

So we have our maximum value. For the other two names, we will use the same formula.

### 2. MAX IF Formula with Multiple Criteria

Sometimes we have to find the maximum value by satisfying multiple criteria. We will now learn this type

**Step-1:**

Let’s assume that we have more than one Sales Rep named **Alex**, **Bob**, and **John** in **Computer, Cycle**, and** Medicine** category. Now we have to find the highest number of sales made by these Sales Reps in each category.

**Step-2:**

Create a table anywhere in the worksheet and the Name and the Category column insert the given criteria.

**Step-3:**

Apply the** MAX IF **formula. We want to find the maximum sales of **Alex** under Computer Category.

For this, the formula is

**=MAX(IF(criteria_range1=criteria1, IF(criteria_range2=criteria2, max_range)))**

Where,

**Max_range**is the Total Sale column (**D4:D19**)**Criteria2**is the name of the Category (**G4)****Criteria_range2**Is the category Column (**C4:C19**)**Criteria1**is the name of the Sales Rep (**F4**)**Criteria_range1**is the Sales Rep Column (**B4:B19**)

Press **SHIFT+CTRL+ENTER **simultaneously to apply the formula**. **The final formula is this

We have found our maximum number.

Apply the same formula to those other cells.

### 3. Excel MAX IF Without an Array

We can get the same result without using the Array formula. To do so, we can use a function named **“SUMPRODUCT”** where we don’t have to press **SHIFT+CTRL+ENTER.**

**Step-1:**

We will use the data from the previous example. The target is to find the maximum sales of **Alex **in the Computer category.

**Step-2:**

Apply the **“****SUMPRODUCT”** function. The format is

**=SUMPRODUCT(MAX((criteria_range1=criteria1) * (criteria_range2=criteria2) * max_range))**

Where,

**Max_range**is the Total Sale column (**D4:D19**)**Criteria2**is the name of the Category (**G4**)**Criteria_range2**Is the category Column (**C4:C19)****Criteria1**is the name of the sales Rep**(F4**)**Criteria_range1**is the Sales Rep Column (**B4:B19**)

The final formula is

Press **“ENTER”**. Our maximum value is here.

### 4. MAX IF Formula With OR Logic

In this case, we need to find the maximum number of sales between Alex and Bob.

**Step-1:**

Insert a table and in the name columns, insert the condition.

**Step-2:**

Apply this formula

**=MAX(IF((criteria_range1=criteria1) + (criteria_range2=criteria2), max_range))**

Where,

**Max_range**is the Total Sale column (**D4:D19**)**Criteria2**is the name of the Category (**G4**)**Criteria_range2**Is the category Column (**B4:B19**)**Criteria1**is the name of the Sales Rep (**G3**)**Criteria_range1**is the Sales Rep Column (**B4:B19**)

The final formula is

**=MAX(IF((B4:B19=G3)+(B4:B19=G4),D4:D19))**

Apply the formula by pressing **SHIFT+CTRL+ENTER.**

And we get out maximum value.

## Excel MAXIFS Function

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.

**Step-1:**

Insert a table and input your criteria

**Step-2:**

We need to find the maximum sales for Alex, John, and Bob for a given category. For this, apply

The **“MAXIFS”** function. The format is

**=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)**

Where,

**Max_range**is the Total Sale column (**D4:D19**)**Criteria_range1**is the Sales Rep. Column (**B4:B19**)**Criteria1**Is the name of the Sales Rep(**F4**)**Criteria_range2**is the name of the category column (**C4:C19**)**Criteria2**is the name of the category (**G4**)

The final formula is

Press **“ENTER”**. We have our maximum value

Now do the same for the rest of the names

## Quick Notes

➤ The MAX IF is an Array Formula so you have to press **SHIFT+CTRL+ENTER **simultaneously to complete this formula.

➤The MAXIFS function is only available for **Excel 2019** and **Office 365**.

## Conclusion

The MAX IF formula and its different types are discussed in this article. Hope this article helps to solve your problem. If you have any suggestions or confusion, you are welcome to comment.