Using MAX IF With Multiple Criteria

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 – MAX IF with multiple criteria? 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)))

IMPORTANT: 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.

_____________________

48 thoughts on “Using MAX IF With Multiple Criteria”

  1. 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)))

  2. 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

  3. 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!

  4. 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!!

  5. 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)))))))))

  6. 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.

  7. 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”

  8. 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.

  9. 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?

  10. 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

  11. 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!

  12. 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.

  13. 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

  14. 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.

  15. 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

  16. 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!!

  17. 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).

    1. 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))

  18. Hi,
    I am trying to find the most current date in a series when looking up a set of values but there are many of the same values. I need the most recent date for each of these purchase orders and have hundreds of purchase orders I need this for. I was trying to use a combo formula of Vlookup and Max but its not working.
    I need a cell that returns a date of 7/15/15 for Purchase Order# 7150
    For example:
    Purchase Order# Date
    7150 7/16/15
    7150 7/17/15
    7150 7/18/15
    7153 7/01/15
    7153 8/06/15
    7153 9/12/15
    7156 5/16/15
    7156 6/15/15
    7156 7/20/15
    7156 8/19/15
    7156 9/18/15
    7156 3/12/15
    Thanks,

  19. Adding a bump from 2016 – I found this after a quick google, it was just what I needed & the example excel file was very helpful – there were enough variants of the formulas given that I could do what I needed.

  20. This structure isn’t working for me. I’m guessing it’s because I’ve exceeded some threshold number of rows (I have >150,000). If that’s the case, it would be good to state the limit in this blog. Thanks anyway.

  21. Hi Experts!!, Pls. Help me.. I’ve followed a lot of exmaples here and tried different combination of MAX, IF & INDEX functions..even trying to nest MAX inside of Index but it doesnt work. Im frustrated and I cant seem to get a reliable formula to work consistently. I’ve attached my sample XLS which has essentially 2 active sheets- its in the link below
    https://drive.google.com/file/d/0B5iyWwANed1BcldwT2dfQ2FsMlE/view?usp=sharing
    What I need to build at work is:
    In Sheet Port Watch, cell D3 thru D500, I’d like it to give latest(most recent by date) BUY price of Stock in cell A3 thru A500 as referenced in Sheet “Past Trades”.
    Similary In Sheet Port Watch, cell E3 thru E500, I’d like it to give latest(most recent by date) SOLD price of Stock in cell A3 thru A500 as referenced in Sheet “Past Trades”.
    In Sheet Port Watch, cell F3 thru F500, it should calculate NET remaining shares by adding and substracting bought and sold (in Sheet past trades) of stock in cell A3.
    In Sheet Port Watch, cell G3 thru G500, it should calculate NET cost of remaining shares (referenced in Sheet Past Trades) of stock in cell A3.
    The past trade sheet may grow fairly big like 10K lines..once all data is brought over. SO any efficient formulae like Index match may be suited for performance.
    BUY & SELL is referenced in Sheet “Past Trades” in Column H marked Trade and also column D (any +ive Qty is BUY and -ive is SOLD)
    This is urgently required…Pls. help ASAP. thanks a lot

  22. Hello,
    I am using this exact formula, Max if and it is no longer working. It worked when I first created the formula and now all of the sudden the formula returns #VALUE!. Here is the exact formula I used: =MAX(IF(‘Guide Bike Log’!$C3:$C2495=$A2,’Guide Bike Log’!$B$3:$B$2495)). I’m referencing another tab called “Guide Bike Log” obviously. Originally it was, set for $C3:$C1000. Now that the data has extended past 1000 cells, I am expanding the number in the formula but it no longer works. Is there another way to diagnose this problem? All of the Microsoft Excel tips didn’t help. I checked that the C column doesn’t have any non-numerical text.
    Thanks in advance

    1. @Robin, did you press Ctrl+Shift+Enter to complete the formula, instead of just pressing Enter?
      This is an array-entered formula, so it won’t work correctly, if you just press Enter.

  23. I used your formula =MAX(IF($A$2:$A$9=A12,IF($B$2:$B$9=B12,$C$2:$C$9))) to find the latest date for a distance & speed of my run. The speed is based on my start time, finish time & distance run. Today I discovered an oddity.
    The latest date till today for a 5.3 km run @ 10.1 kph was 13 April 2017, based on a start time of 10:05 and a finish time of 10:39:30 = 34:30 mins. Today I added another record for the exact same distance & speed, but based on a start time of 06:30 and a finish time of 07:04:30 = 34:30 mins. The formula still returns the latest date of 13 April, but when I changed the start & finish times to 07:00 and 07:34:30 respectively, it returned today’s date. I then did some trial & error & found that with a start time of 06:42:26 & 07:16:56 (= 34:30 mins) it returns today’s date, but if I move both start & finish time down by 1 sec (06:42:25 and 07:16:55 resp.) it returns 13 April as the latest date !!!
    I have checked the formatting & formulae, everything is right. I just cannot figure this out. Any suggestions?

    1. Klaas, there must be a minute difference in the speed results, even though the run times are the same.
      Try rounding your speed calculation, to just a few decimals. Then, see if the formula returns the correct date. For example, =ROUND(A2/F2,4)

      1. Thanks for your input Debra. I have 2 speed cols.: 1 in kph & 1 in mph, with latter being a derivative of the former, i.e. mph = kph/1.6.
        Using the data from the mph col. in your formula returned that quirky result, whereas using the data from the kph col. returned the right result. Looking at the calculated data in all the cols. I cannot pinpoint where the problem is, but since I get the right result with the kph data I will leave it at that. Thanks again for your help, and also for your instructive website 🙂

  24. =INDEX(G14:G10000,MATCH(MAX(IF((C14:C10000>=$C$3)*(C14:C10000<=$C$4)*(D14:D10000=$D$4),C14:C10000,0)),C14:C10000,0))
    plz help!! is this formula wrong??

  25. Fantastic! I searched lots of other sites and this is by far the most direct and simple answer. Can’t believe the ridiculously complex things some of the other sites posted to solve this.

  26. Why doesn’t this formula work if it’s in its own column to the right of the data column and referencing the row’s A value in A6:A9 instead of the independent cells at the bottom?

  27. Dear Admin/ Expertise,
    I would appreciate that if you could solve my V lookup problem.

    Sales # Month Sales Value Location Formula
    1. John 1. Jan A. 0 1. NY 1. V lookup Formula
    2. John 2. Jan B. 200 2. TX 2. V lookup Formula
    3. John 3. Feb C. 0 3. MI 3. V lookup Formula

    I Need the Location Result (No 2 = TX) using the V lookup Formula at all the cells below the Formula column. Please note: Below Formula Column all the result should be – TX after the V lookup Formula.

    Thanks and regards
    Sub

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.