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.

Customers and Products

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

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.

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.

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

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

_____________________

46 Responses

1. Bryan says:

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. r says:

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. Marc says:

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.

4. Meghan says:

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

5. Meghan says:

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

6. Praveen says:

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

7. Razvan says:

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.

8. Riley says:

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”

9. Rudi says:

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?

10. Chris says:

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?

11. Jaidria says:

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.

• Hugo says:

Jaidria,

probably you forgot pressing CTRL+SHIFT+ENTER after typing.

12. ahmed says:

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

13. Ashish Pandey says:

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

14. Mohamed Tawfik says:

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

15. C, reygo. says:

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.

16. Srinivas says:

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

17. TAPASH says:

x=75
y=80
z=200

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

18. Dave says:

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!

19. SURYA says:

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.

20. Kiran says:

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

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

22. Zoe says:

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

23. Richard B says:

Thanks for that, I’ve been trying to do something similar for ages

24. Anthony says:

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,

• Anthony says:

Correction… I need a date of 7/18/15 for Purchase Order #7150

25. Kirk says:

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.

26. Joe says:

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.

27. Om says:

press Ctrl + Shift + Enter after typing this formula!!!!!! worked the way I wanted. Thanks a lot :)

28. sean says:

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

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

29. Simon says:

Hi,
How do I prevent it from returning 0 (zero) when there is no match?
Thanks, Simon

30. Robin says:

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.

• @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.

31. Hello,
In Excel 2016 we can use the new function “MAXIFS”
Thanks

32. Klaas Vaak says:

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?

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

• Klaas Vaak says:

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

• You’re welcome, and thanks for the additional details. Glad you got it working.

33. Ann chaples says:

Awesome tip! Thanks for posting.

34. tps says:

=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??

• When you finish the formula, press Ctrl+Shift+Enter, instead of just pressing Enter.

35. lb says:

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.

1. November 25, 2013

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