Max IF 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.

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.

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))"

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.

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

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.

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:

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

2. 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.
3. 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.