How to Use MAX IF Function in Excel

the result of the formula

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.

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

=MAX(IF(criteria_range=criteria, max_range))

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.

data set

Step-2:

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

creating table

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

=MAX(IF(B4:B15=F4,D4:D15))

 

entering function

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.

the result of the formula

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

final result

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.

data set

Step-2:

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

creating table

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

=MAX(IF(B4:B19=F4,IF(C4:C19=G4,D4:D19)))

entering formula

We have found our maximum number.

applying formula

Apply the same formula to those other cells.

final result

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.

entering formula

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

=SUMPRODUCT(MAX(((B4:B19=F4)*(C4:C19=G4)*(D4:D19))))

applying formula

Press “ENTER”. Our maximum value is here.

final result

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.

creating table

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.

entering formula

And we get out maximum value.

result

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

creating table

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

=MAXIFS(D4:D19,B4:B19,F4,C4:C19,G4)

entering data

Press “ENTER”. We have our maximum value

applying formula

Now do the same for the rest of the names

final result

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo