How to use MAX IF in Excel (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.

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. In Microsoft 365 and Excel 2021 or later, you can simply press Enter, as these versions handle arrays on their own.

{=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 how MAX IF works, you need to split it into three parts.

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 matching values are TRUE and all others are FALSE.

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

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 others that you must read and learn.

• SUMIFOR in Excel
• RANKIF in Excel

MAX IF with Multiple Criteria

I am sure at this point you are thinking about 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),))}

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 the sales quantity only where the month is Jan and the week is the 2nd.

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

You can also write the same two conditions without nesting IF. Multiply the two tests, so a row counts only when both are TRUE:

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

One more thing: the comma before the last closing bracket in the nested formula makes the outer IF return 0 (not FALSE) for other months. With positive sales, that doesn’t matter, but if all the matching values are negative, the formula returns 0 instead of the real maximum. In that case, remove that comma or use the formula above.

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.

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.

After that, you have multiplied that array with the sales quantity column, which returns an array where you have the sales quantity instead of TRUE (and 0 instead of FALSE). In the end, the MAX function has returned the highest value from that array which is your highest sales quantity.

In this entire formula, SUMPRODUCT plays the major role of allowing you to use an array without entering an actual array formula.

But keep two limits in mind. As the rows that don’t match become 0, the result is 0 when all the matching values are negative. And if the sales column has a text value, the multiplication gives a #VALUE! error.

MAXIFS Function (Excel 2019 and Later)

If you are using Excel 2019, 2021, 2024 or Microsoft 365, you have the MAXIFS function, which does the same job without an array formula. You specify the max range first and then pairs of criteria range and criteria:

=MAXIFS(D2:D367,A2:A367,"Jan")

And for the month and the week from cells F5 and G5:

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

MAXIFS ignores text values, returns 0 when no cell matches the criteria, and gives a #VALUE! error if the max range and the criteria ranges are not the same size.

MAX IF with AGGREGATE (No Array Needed)

If you are on Excel 2010 to 2016 and want to avoid Ctrl + Shift + Enter, use AGGREGATE. Function 14 is LARGE, option 6 ignores errors, and dividing by the condition turns every row that doesn’t match into an error:

=AGGREGATE(14,6,D2:D367/(A2:A367="Jan"),1)

Unlike the SUMPRODUCT version, this works with negative values and skips text values in the sales column.

Conclusion

Getting the maximum value based on criteria is something we often need to do. And using this MAX IF technique, you can do it with no effort.

All the methods that you have learned above have different applications depending on the situation. If you have MAXIFS, it’s the simplest one; otherwise, use MAX IF or AGGREGATE. 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.

Frequently Asked Questions

=MINIFS(D2:D367,A2:A367,"Jan")

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