How to use MAX IF in Excel (Formula)

Last Updated: December 26, 2023
puneet-gogia-excel-champs

- Written by Puneet

In Excel, we can’t simply use the default MAX function with a condition (unless you are using Microsoft Office 365).

But the thing is if want to get maximum value from a range using a specific condition you need a MAX IF formula.

So, what’s the point? You can combine MAX and IF to create a formula that can help you to get the max value from a range using specific criteria.

In short: MAXIF is an array formula that you can use to find the max value from a range using criteria. But here’s the kicker: In this post, I’m gonna share with you different ways to use the MAXIF formula.

Apply MAX IF Formula

Let’s take an example of the below data where you have month-wise, week-wise, and date-wise sales quantity.

sales quantity data to use max if formula

Now from this data, you need to get the max sales quantity for a particular month. For example, “Jan” and 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))}
max if formula applied

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.

understand max i -formula in excel

Please take note that you have entered it as an array formula.

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

first array with max if formula with array

Second, in the IF function, you have specified the sales quantity column for the TRUE values and that’s why it has returned sales quantity only for the TRUE.

second array of max if formula with array

Third, the MAX function will simply return the highest number from this array which is the highest sales quantity for the month of Jan. Just like this formula hack here I’ve listed some of the others which you must read and learn.

MAX IF with Multiple Criteria

I am sure at this point you are thinking that how we can use more than one condition in max if. And that’s a smart thought. In the real world of data, there is a huge possibility that we need to use multiple criteria to get the highest value. Let’s continue with the previous example.

Let’s say, instead of the highest sales value from the month of Jan, you need to get the highest sales for week 2 of Jan. So, here you have two different conditions, the month is Jan and the week is the 2nd. And the formula will be:

=MAX(IF(A2:A367=F5,IF(B2:B367=G5,D2:D367),))

And, when you enter it as an array:

{=MAX(IF(A2:A367=F5,IF(B2:B367=G5,D2:D367),))}
multiple criteria with max if formula

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 the week is the 2nd.

multiple criteria with max if formula first array

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 them. And, you have an option not to use an array in max if. So, for this, you can use the SUMPRODUCT function because it can help you to use an array without applying the actual 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.

max if formula without arrays using sumproduct

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 the month column with the criteria, and it has returned an array with TRUE where criteria matched, else FALSE.

compare months max if formula with sumproduct

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, the 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 entering an actual array formula.

Conclusion

Getting the max value based on criteria is what we often need to do. And using this technique of max if the formula you can do it with no effort.

All the methods which you have learned above have different applications according to the situation. I hope this formula tip will help you to get better at Excel. But now, tell me one thing.

Which one do you really like, an array or without an array?

Please share your views with me in the comment section. I’d love to hear from you, and please, don’t forget to share this post with your friends, I am sure they will appreciate it.

Get the Excel File

15 thoughts on “How to use MAX IF in Excel (Formula)”

  1. The above example is good but how can we write vba macro for the same week month wise sales to obtain maximum sales

    Reply
      • Hi Puneet

        How to retrieve information form another sheet by; Name with Latest two dates across 14 columns?
        How does EXCEL read cross over years? eg; 2019 / 2020
        Also access multi 3 sheets into formula?

        A = Name (appears 20 times)
        B = Dates (appears 100 times) MONTH/DAY/YEAR A6 = 1/01/2019 TO A100000 =5/20/2020
        C = Distance (appears 20 times)

        Formula’s eg;

        A6 =VLOOKUP(A6,’QLD RESULTS’!A:B,2,FALSE) (returns first entry NOT LATEST)
        A6 =VLOOKUP(A6,’QLD RESULTS’!A:C,3,FALSE) (as above)

        {=MAX(‘QLD RESULTS’!A6:A100000,’QLD RESULTS’!B6:B100000)} (return 4th latest entry)
        {=MAX(IF(‘QLD RESULTS’!$A$6:A$100000=A6,’QLD RESULTS’!$B$6:$B$100000,FALSE))} (as above)

        I appreciate any direction with these issue.

        Regards

        Tony

        Reply
      • Hi Puneet

        I loss the connection to my rely to your question, so how do I get it back?

        From a old fella

        Regards

        Tony

        Reply
    • Hi
      Sorry for the misunderstanding

      How to retrieve information from another sheet by; Name with latest two dates? (across 14 columns)
      How to check multiply 4 sheets in same formula?
      Also how does EXCEL read cross over years? eg; 2019 & 2020
      Have filter to check input as (not all items showing) has come up, so have redirected to another sheet.

      A6 = Name (appears 20 times)
      B6 = Date (appears 100 times) Month/Day/Year A6 = 1/01/2019 / A100000= 5/20/2020
      C6 = Distance (appears 20 times)

      Formula’s; tried below
      A6 =VLOOKUP(A6,’QLD RESULTS’!A:A,1,FALSE) (returns first entry across 14 columns Not latest)
      B6 =VLOOKUP(A6,’QLD RESULTS’!A:B,2,FALSE) (as above)
      C6 =VLOOKUP(A6,’QLD RESULTS’!A:C,3,FALSE) (as above)

      A6 {=MAXIFS(A6:A100000,B6:B100000=A6)} (check formula same sheet didn’t work)
      A6 {=MAX(‘QLD RESULTS’!A6:A100000,’QLD RESULTS’!B6:B100000)} (returned 4th latest date)
      A6 {=MAX(IF(‘QLD RESULTS’!$A$6:A$100000=A6,’QLD RESULTS’!$B$6:$B$100000,FALSE))} (as above)

      I greatly appreciate any direction with these issue.

      Regards

      Tony

      Reply
  2. Min is trickier, since the function as described will generate an array with numerous zero values. Thus, your MIN will always be be zero. However, here is a potential workaround. Considering the array version of the formula, the final version of the array formula is:
    {=MAX(IF(A2:A367=F5,IF(B2:B367=G5,D2:D367),))}
    As stated, if we simply change MAX to MIN, we will likely get 0. Now, in the formula above, the IF function is being fed a default output. If we change the default output of the IF statement to a value that we know to be higher than any items in our list of items, then instead of generating an array filled with zeroes we will produce an array with our defined limited scope of items and a list of numbers all greater than our list. That way, when we apply the MIN, there will be no zero value to grab. In practice, this looks like:
    {=MIN(IF(A2:A367=F5,IF(B2:B367=G5,D2:D367),1000000))}
    I have entered 1,000,000 into the conditional statement (far higher than necessary for this example). The only other change is MAX to MIN, and Voila!

    Reply

Leave a Comment