MAX IF in Excel

Max IF Formula in Excel

Max If is an array formula in excel, which is used to identify the maximum value out of the range of values (or large data set) with a specific condition (logical test). The formula of the Excel MAX IF function to be entered in excel to use this is as below:

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

Since this is an array formula, this should always be used in excel by using ‘Ctrl+ Shift+ Enter’ keys on the keyboard to run the formula.

Explanation of Excel Max If Formula

As the syntax of the formula has max and IF function. IF function in the formula is used to put the logic specific to criteria on which one is trying to find the maximum value. The IF function helps in running the logic in the data set and find the outcomes matching the logical test. Than Max function identifies the maximum value of all the outcomes matching the logical test. Because it is being used as an array formula, the logic by using If the function can run the logical test multiple time in the data set to find the multiple values matching the logical test and then the max function can identify the maximum value of the same.

A detailed demonstration of the use of Excel Max If a formula is explained in the next section with a snapshot of the specific use of the formula for a data set.

Application of Excel Max IF Formula

Excel MAX IF function is widely used in multiple applications where one need to find the maximum value among the large data set based on certain criteria. Excel MAX IF function can easily find out the maximum value matching certain criteria among a much larger data set. A couple of examples for the same are mentioned below:

Excel MAX IF function can be used in finding the maximum marks scored in a specific subject by a student among the large data set of marks scored by the students of a specific Class in multiple subjects in a School. The data set in such an example can be really large and complex as below:

Assume a data set of 1000 subject marks as detailed in the above table where subjects are not arranged in a specific manner. Excel MAX IF function can easily find out the maximum marks scored by a student in Maths among this large table (Use of the formula will be explained in further section).

Excel MAX IF function can be very useful for sales professionals of an FMCG company operating at a national/international level to identify the city/state/country with the maximum number of sales for specific products. One can assume that a company operating at such a large scale will have a humongous data set to get these insights on sales number of products.
Similarly, a metrological team analyzing the historical trend about the hottest summer for a specific year can find out the year where June month recorded the highest temperature among the large set of data with temperature, months and Years by using the Excel MAX IF function.

There are many examples where this Excel MAX IF function makes our life relatively easier by giving the desired result in no time by just using this formula correctly.

How to use Max If Formula in Excel?

Taking the example of data sets of marks scored by students as explained in the earlier section, we will try to work out the Max If formula in excel to find out the maximum marks scored by students in Maths subject. The data set is given below:

Here Max If the formula in Excel can be used as below using the following syntax:

=MAX (IF (K4:K13=K17, L4:L13))

Also below is the demonstration of the application of excel Max If formula on above data set in excel and snapshot of the Excel actual workings:

If we refer the above snapshot of excel working, logical test here is B2:B11=C14, which is comparing the value in B2:B11 against the C14, which is Maths.

The array will return the results as either True or False based on meeting the logical test. The array will return all the values for Maths i.e. all the marks scored in Maths by the student.

So if the function will provide the results from column E i.e. C2 to C11, to return the values matching with Maths (Logical test). Finally, Max function will identify the maximum value out of the Array values matching the logical test.

This formula shall be typed as shown in the above snapshot in the excel and then shall be entered using Ctrl, Shift and Enter to get the maximum value out of data set meeting a specific logical criterion. Here, in this case, the Logical test is ‘B2:B11 (Data set of Subjects) = C14 (Maths)’, which is used using If function.

Than Max function is applied to get the maximum value.

Things to Remember about Excel Max If Formula

One should always keep in mind that this is an array formula, therefore it should always be used with Ctrl, Shift and Enter while applying the final formula. Also, the variable to be used for the logical test shall be clearly defined and shall be without any error other-wise formula may not pass the logical test. The selection of the cells in the excel should be in line with the requirement because the wrong selection of the cells may end up in erroneous results. Excel MAX IF function is very useful in getting the maximum value for data among a large data set matching specific criteria or logical test. The formula for the logical test also shall be applied very cautiously with utmost care of each small thing in the formula to get the correct outcome from a large data set.

You can download this Max If Formula Excel template here – Max If Formula Excel Template