Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

10000+ Copies Already Downloaded

MAX IF Formula

Ever tried to get max value from a range using a specific condition? Which formula do you think you can use? No idea?

Let me tell you.

Well, in excel, there is no specific function for this (unless you are using office 365), but, you can combine MAX and IF function to create a formula which can help you to get the max value from a range using a specific criteria.

So today, in this post, you will learn to use this combination of max and if to get a conditional max value from a range.

Don’t have time to read the whole formula tip right now?

​No worries. Let me send you a Free Cheat Sheet which you can read when it’s convenient for you.

Apply MAX IF Formula

Let’s take an example of below data where you have month wise, week wise, and date wise sales quantity. Download this raw data from here to follow along.

Now from this data, you need to get the max sales quantity for a particular month. For example, “Jan”. For this the formula will be:

=MAX(IF(A2:A367="Jan",D2:D367))

And, you need to enter it as an array formula by using ctrl + shift + enter.

{=MAX(IF(A2:A367="Jan",D2:D367))}

When you enter it, it will return 200 which is the highest sales value in the month of Jan on 07-Jan-2016.

How does this work

To understand the working of max if you need to split it into three parts. And, please take a note that you have entered it as an array formula.

First of all, you have created a logical test in IF function to match entire month column with the criteria. And, it has returned an array where match values are TRUE and all others are FALSE.

In IF function, you have specified sales quantity column for the TRUE values and that's why it has return sales quantity only for the TRUE.

In the end, MAX function will simply return the highest number from this array which is the highest sales quantity for the month of Jan.

MAX IF with Multiple Criteria

I am sure at this point you are thinking how to use more than one condition in max if. And that’s a super valid point.

In the real world of data, there is a huge possibility that we need to use multiple criteria in to get a highest value. So, let’s continue with the previous example.

Let's say, instead of highest sales value from the month of Jan, you need to get the highest sales for the week-2 of Jan.

So, here you have two different conditions month is Jan and week is 2nd.

The formula will be:

=MAX(IF(A2:A367=G2,IF(B2:B367=G4,D2:D367),))

And, when you enter it as an array:

{=MAX(IF(A2:A367=G2,IF(B2:B367=G4,D2:D367),))}

How does this work

Here we have used nested IF to test more than one logic. One is for the month and the second is for the week.

So, it returns an array where you have sales quantity only where the month is Jan and Week is 2nd.

And, in the end, max returns the max value from that array.

MAX IF without an Array

Arrays are powerful but not everyone wants to use it. And, you have an option to not to use an array in max if.

So, for this, you can use SUMPRODUCT function because it can help you to use an array without applying actual an array formula. And the formula will be:

=SUMPRODUCT(MAX((A2:A367="Jan")*D2:D367))

So, when you insert this formula you don’t need to use ctrl + shift + enter.

How does this work

Again we need to split this formula into three different parts to understand it.

In the first part, you have compared month column with the criteria and it has returned an array with TRUE where criteria matched, else FALSE.

After that, you have multiplied that array with the sales quantity column which returns an array where you have sales quantity instead of TRUE.

In the end, MAX function has returned the highest value from that array which is your highest sales quantity.

This entire formula, sumproduct has the major role to allow you to use an array without enter an actual array formula.

Sample File

You can learn more about max if from this sample file.

Conclusion

Getting the highest value based on a specific condition is what we often need to do. And, using this technique of max if formula you can do it with no efforts.

All the methods which you have learned above have different application according to the situation. And, I hope you found all of them useful for your work.

Now tell me one thing. Which on do you really like an array or with an array? Please share within the comment section.

I would love to hear from you.