Max IF in Excel

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Vandana Kataria

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What is MAX IF Formula in Excel?

The MAX IF formula is a combination of two excel functions (MAX and IF Function) that identifies the maximum value from all the outcome that matches the logical test. MAX IF is used as an array formula where the logical test can run multiple times in a data set.

The method to use MAX IF function together is as follows:

=MAX(IF(logical test,value_ if _true,value_ if_ false))

Being an array formula, it should always be used by pressing “Ctrl+Shift+Enter” while running the formula.

Table of contents
Max if formula in excel

How to use Max If Formula in Excel?

Let us consider the previous example with new numbers in column C. The following image shows the marks scored by students in various subjects. The subjects in the list are written in an unorganized manner.

Max if Formula 2

Let us apply the MAX IF function to ascertain the maximum marks scored by a student in Mathematics.

We apply the following formula.

"=MAX(IF(B2:B11=C14,C2:C11))"

Example 3

Here, the logical test is "B2:B11=C14." The value in "B2:B11" is compared with "C14," which is “Maths”.

The MAX IF formula returns “true” or “false” depending on the logical test. In this example, the array returns all scores of “Maths” obtained by students.

From the range "C2:C11," the function provides values matching with “Maths.” The maximum array value matching the logical test is 94.

Example 4

The MAX IF function is entered using “Ctrl+Shift+Enter” to get the maximum value from the given data set.

The Cautions While Using MAX IF Formula

The following points should be kept in mind while using this function:

  • The variables of the logical test must be clearly defined; otherwise, the formula may not pass the logical test.
  • The selection of cells should be in accordance with the requirement because an incorrect selection may give erroneous results.

Applications of Excel Max IF Formula

The Excel MAX IF function is used in situations where one needs to find a criteria-based maximum value from a large data set. The applications of the MAX IF function are mentioned as follows:

  1. It is used to find the maximum marks scored by a student in a specific subject. The data set consists of the marks obtained in multiple subjects by all students of a certain class. In such a situation, the data set is large and complex, as shown in the following image:
Max if Formula 1

Even if the list extends to serial number 1000, the MAX IF function helps determine the maximum marks obtained by a student.

  1. It is used by sales professionals of MNCs to identify the city with maximum sales of specific products. An organization operating at such a large scale works with huge data sets.
  2. It is used by the meteorological team to find the year in which a particular month recorded the highest temperature. The temperature of different cities over several years is compared and analyzed to study variations of weather.

Frequently Asked Questions

#1 - How to use MAX IF formula with multiple criteria?

The MAX IF function includes additional criteria with nested IF statements. The formula is stated as follows:

"{=MAX(IF(criteria_range1=criteria1,IF(criteria_range2=criteria2,max_range)))}"

Alternatively, the multiplication operation can be used to handle multiple criteria. The formula is stated as follows:

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

#2 - How to use MAX IF formula without an array?

The MAX IF formula can be used without an array with the help of the SUMPRODUCT function. The formula is stated as follows:

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

If required, the user can add more pairs of criteria.

Note: This formula can be entered using the “Enter” key. It returns the same result as the MAX IF array formula.

#3 - What is the MAXIFS function of Excel?

The MAXIFS function determines the maximum value from a range of values. It is a criteria-based function where the criteria can be dates, numbers, text, and so on.

This function is available to users of Excel 2019 and Excel 365. The syntax of the function is stated as follows: "=MAXIFS(max_range,range1,criteria1,,,...)"

The “max_range” is the range of values from which the maximum value is to be determined. The “range1” is the first range to be evaluated. The “criteria1” is the criteria used on the first range.

Note: In the MAXIFS function, the user can enter 126 range/criteria pairs. Every range whose values are to be evaluated against a criterion must be of the same size as the "max_range."

  • The MAX function is an array formula that finds the maximum value in a given range.
  • The IF function is a conditional function that displays results based on certain criteria.
  • The MAX IF function identifies the maximum value from all the array values that match the logical test.
  • The formula of Excel MAX If function is “=MAX(IF(logical test,value_ if _true,value_if_ false)).”
  • The MAX IF formula is used to find the maximum marks obtained by a student, the maximum sales of a product, the maximum temperature of a month, and so on.