Using MAX IF With Multiple Criteria

Excel doesn’t have a MAXIF function, but we’re able to create our own version, by combining the MAX and IF functions. But how about a bigger dream? Could we create our own MAXIFS function too, with multiple IFs?

Using MAX and IF

Last week, we used MAX and IF in an array-entered formula, to find the latest date that a product price was changed. Remember to press Ctrl + Shift + Enter after typing this formula, instead of just pressing Enter.

=MAX(IF($A$2:$A$9=A12,$B$2:$B$9))

Note: For more information on array formulas, I recommend Mike Girvin’s book, Ctrl+Shift+Enter: Mastering Excel Array Formulas.

maxiflatestprice02

Customers and Products

This week, our sample data has another column included – the customer name.

maxifcustomerprice02

We’d like to revise that MAX and IF formula, to check for the latest date that a product price was changed, for a specific customer.

Select a Customer and Product

The product name, Paper, is entered in cell A12, and the customer name, ABC,  is in B2. By manually checking the list, we can see that the latest price change was on June 1st.

maxifcustomerprice03

Modify the MAX and IF formula

The original formula just checked the product name, and then returned the latest date, from column B.

=MAX(IF($A$2:$A$9=A12,$B$2:$B$9))

Now, we want the formula to check both the product name (column A), and the customer name (column B), and return the latest date (column C).

Entered in cell C12, the formula will start the same, by checking the product name:

=MAX(IF($A$2:$A$9=A12,

Then we’ll add a second IF function, to check the customer name:

=MAX(IF($A$2:$A$9=A12,IF($B$2:$B$9=B12,

Finally, we’ll get the date from column C:

=MAX(IF($A$2:$A$9=A12,IF($B$2:$B$9=B12,$C$2:$C$9)))

After typing the formula, array-enter it, by pressing Ctrl + Shift + Enter.

maxifcustomerprice04

The formula is copied down to cell C13, and both cells show the correct result – June 1st.

Get the Latest Price

Finally, the Latest price is calculated using SUMIFS (Excel 2007 or later):

=SUMIFS($D$2:$D$9,$A$2:$A$9,A12,$C$2:$C$9,C12)

or SUMPRODUCT:

=SUMPRODUCT(($A$2:$A$9=A13)*($C$2:$C$9=C13)*($D$2:$D$9))

maxifcustomerprice05

Download the Sample File

You can download the sample file from the MIN and MAX Functions page on my website.

_____________________

7 comments to Using MAX IF With Multiple Criteria

  • Bryan

    Could you also use SUMPRODUCT to fake the array formula? This way you could add more criteria easily without having to count parentheses of the nested IFs: =SUMPRODUCT(MAX(($A$2:$A$9=A12)*($B$2:$B$9=B12)*($C$2:$C$9)))

  • […] MAX and IF with multiple criteria, to find the latest date that a product price was changed, for a specific […]

  • r

    when i have to use MAX IF with multiple criteria i prefer use LARGE function so for example:
    =LARGE(IF($A$2:$A$9=A12,$B$2:$B$9),1)
    Infact if noone criteria is true large return a error value instead 0 of MAX or MIN functions
    I think this is a result more correct

  • Marc

    How would one go about applying conditional formatting using the MIN/MAX function which itself employs a logical test?

    I'm using Excel 11.5.9 (Copyright 2004 - does that make it Excel 2003???) and here's my dilemma:

    I need to find the smallest decimal value in a group of cells, where, for example:

    C35 = 2.9380
    D35 = 5.5495
    F35 = 4.0359
    G35 = 7.6233

    Note that E35 is used for another variable which I do not want to include in the comparison among the above cells.

    So, the answer to which of the above cells has the smallest decimal value would be F35 (ie. whole numbers aside, ".0359" represents the smallest decimal value of the bunch).

    The formula I used to return the value of "0.0359" is =MIN(C35-INT(C35), D35-INT(D35), F35-INT(F35), G35-INT(G35)). If there's a better way to do this, I'm certainly open to suggestions.

    Now what I want to do is apply some conditional formatting so that F35 gets highlighted in red for example.

    Thanking you in advance for any advice and/or solutions!

  • Meghan

    THANK YOU SO MUCH!!! This help me so much. You are saint, sir!

  • Meghan

    Seriously though. This is just great thing for you to do with no thank in return. You deserve much much thank. I thank you. My mama thank you. My cousin filipe, he thank you too!!

  • Praveen

    Can any budy help me to resolve the below function..I am getting the error like #NUM! if I use this function

    =IF($B$20="Top",IF($A$20="Card1",(LARGE(IF(MONTH(Date_P_B)=7,Card1_Utz),(IF(B20="Top",IF(A20="Card2",(LARGE(IF(MONTH(Date_P_B)=7,Card2_Utz),B21)))))))))

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>