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.

_____________________

26 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)))))))))

  • Razvan

    i need a solve for my problem .

    i need to find the closest date for an item , my probelm is, all the date of my items are in the same row.

    So , i need to find the closest date for each item, thanks in advance.

  • Riley

    This is my scenarios (below). I need the excel formula. Thanks

    Item Nos.
    Tree Removal 2
    Curb 4
    Curb 10
    Tree Removal 11
    Tree Removal 6
    Jersey 4

    With a criteria of "Tree Removal" from column "Item", I need to get the maximum figure in Column "Nos." considering the criteria mentioned. The result should be "11"

  • Rudi

    I still can not solve my problem:

    I have a list with some 7000 entries sorted by date and time descending. I would like to find the last entry in that list of a specific date in column O and get a corresponding value from column T

    01/11/2014 00:03 466.00
    01/11/2014 00:11 463.00
    01/11/2014 00:11 466.28
    01/11/2014 07:38 463.28
    01/11/2014 07:59 468.86
    02/11/2014 08:00 473.12

    In this sample list I would like to know the last entry from 01/11/2014 and get 468.86 as a result.

    Is that possible in a single formula?

    Thanks for any advice.

  • Chris

    Hi
    I have noticed that when I use the max / IF functions together like this (using it to find a max date)
    when I attempt to sort my data the reference cells don't stay aligned...
    IE =MAX(IF('Order Dates'!F:F='Order Summary'!D7,'Order Dates'!P:P))

    When I sort on the column this formula is in it loses the references (D7)

    Any way around this problem?

  • Jaidria

    Hi,
    I am having a problem with my nested IF statement the Max functions seems to be completely ignoring it! I have only just returned to using Excel after 8 years absence and am a bit rusty.

    My data includes 2 columns of text used in the IF statement and when both these criteria are met then I want the max from the corresponding values

    e.g. Col 1 is Sex, M or F; Col 2 is Age, mature, fawn, and Col 3 is weight in Kg. So if it is a M and a fawn, I wish to find the max weight; if it is F and mature, I wish to find the max weight

    I have tried representing the text as numerals and applying this to the calculation but am not having much luck.
    Here's an example of my current formula

    =MAX(IF($C$4:$C$31="F",IF($D$4:$D$31="mature",$E$4:$E$31)))

    Have tried several variations and alternative functions but am obviously just not quite getting it and my little brain is spinning.

    Thanks in advance for any help you can give

  • ahmed

    Dear Mr/Ms:
    I have a problem and do not know how to solve it, let me illustrate using this example:
    suppose you have the following columns and rows
    c1 5 10 12
    C2 4 5 6
    c2 7 14 8
    c3 3 4 5
    c2 1 2 3
    c3 2 3 4

    and I wanna sort the last 3 columns according to the first column depending on the max values of them
    so I will end up by the following
    c1 5 10 12
    C2 7 14 8
    c2 4 5 6
    c2 1 2 3
    c3 3 4 5
    c3 2 3 4

    column 1 will be sorted ascendingly, and the rest 3 columns will be affected, after thatpick the highest three values of the last 3 columns according to the column 1 and remove the other values, then sort them again
    how can i do that ??
    thanks in advance!

  • Ashish Pandey

    Thanks a lot, i knew this formula but it didn't clicked.
    Thanks again !

  • Mohamed Tawfik

    how can i repeat Max And If function on a lot of cells ?

  • C, reygo.

    Let me thank you firt for your post very helpful.

    Q: I want have to boxe k8 and G13, with differents values, 18 and 10, but I need to excel to choose bigger number and place it in i13. This boxes are not connected by other boxes. I just need to find the way to compare this two values, not following a vertical or horizontal sum. Thanks.

  • Srinivas

    how to extract the report of Min and Max date for below ID's

    report should be something like "789" "C" "6 july" "16 Oct"

    Id Name St. Dt. Ed. Dt
    123 A 26-May-15 31-Dec-15
    456 B 12-Aug-15 11-Sep-15
    456 B 14-Sep-15 16-Oct-15
    456 B 3-Aug-15 11-Aug-15
    789 C 12-Aug-15 28-Aug-15
    789 C 31-Aug-15 16-Oct-15
    789 C 6-Jul-15 31-Jul-15
    789 C 3-Aug-15 11-Aug-15

  • TAPASH

    x=75
    y=80
    z=200

    if x>=75, then x*4 but value not be greater than 500

  • Dave

    How can I do use Max If in a portfolio transaction sheet. In the PORT tab you will see the incorrect USO # of shares are being pulled from the TRANS tab. It should show 15 not 25 and also the cost basis is wrong. It is pulling the "max" value of a previously bought and sold position. Can anyone help me correct my formula? Thanks!

    https://docs.google.com/spreadsheets/d/1d4tAxF8d3w6GUJrLRIfEke39GZ2JpWIzmxzkSIJs2lc/edit?usp=sharing

  • SURYA

    I want to convert the daily trading data of stocks having Open, High, Low and Close level to weekly Open, High, Low and Low levels. How to convert the same, as week trading days vary for holidays.

  • Kiran

    Hi, I need a formula to identify highest num for each employee containing multiple employees can any one help me

    Emp Date Hrs
    111 12/8/2015 19:40 8
    111 12/8/2015 19:33 9
    111 12/8/2015 18:00 5
    222 12/8/2015 19:40 8
    222 12/8/2015 19:33 9
    222 12/8/2015 18:00 3

  • Bhaskar

    I,m trying to find the MAX value in a range of cells based on couple of conditions. Example as follows: There is continuity in the data for different dates for same person A or B Or C.. we have different numbers. For one day there will be unique Identity. I need the MAX value of that day from No1. Any Suggestions.?

    Date Name No1 Unique
    day1 A 100 0
    day1 A 100 0
    day1 A 200 1 ?
    day1 B 50 0
    day1 B 60 1
    day1 B 100 0
    day1 C 100 0
    day1 C 100 0
    day1 C 200 1
    day1 D 50 0
    day1 D 60 0
    day1 D 100 0
    day1 D 200 1
    day2 A 100 0
    day2 A 100 0
    day2 A 150 1 ?
    Thank you!!

  • Zoe

    Hai, I have a problem in multiple criteria. Can u explain to me.
    This is my problem:
    CUSTOMER CODE AMOUNT INV max amount invoice
    #00056 23.9 I1012312
    #00056 30.8 I1003212
    #00056 30.8 I1012322
    #00056 57.2 I1060001
    #00056 42.8 I1020001
    #00056 35 I9080000
    #00056 18 I1022222
    #00056 86.4 I1035222
    #00056 27 I1053666
    #00056 Total 0 86.4 I1035222
    #00053 15.4 I0000222
    #00053 40.3 I3210000
    #00053 20.9 I1025300
    #00053 20.1 I1035000
    #00053 27.6 I1022000
    #00053 86.4 I1068221
    #00053 57.2 I1068223
    #00053 71.7 I1068224
    #00053 23.9 I1032000
    #00053 24.4 I1044500
    #00053 40.3 I1066000
    #00053 20 I1022200
    #00053 Total 0 86.4 I1035222

    Can i know why the two invoice number were same. Its is the formula wrong?
    In amount part I use =MAX(IF(A:A=A24,C:C)).
    In invoice part I use =VLOOKUP(E:E,C:D,2,0).

    • VLOOKUP will return the first match that it finds, and that is the 86.4 value from the 00056 Customer.
      You could inset a new column after the invoice number column, and use this formula to combine the customer code and amount: =A2&"|"&B2
      Then in the Invoice column, use this invoice to find the max invoice for each customer, based on the customer code in A11 and max amount in E11:
      =INDEX(C:C,MATCH(A11&"|"&E11,D:D,0))

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=""> <s> <strike> <strong>