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.

46 Responses

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

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

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)

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!

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.

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!

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

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

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.

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.

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.

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 belowhttps://drive.google.com/file/d/0B5iyWwANed1BcldwT2dfQ2FsMlE/view?usp=sharing

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)

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.

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)

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