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

### Download the Sample File

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

_____________________

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 […]

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

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!

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

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

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

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.

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"

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.

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?

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

Jaidria,

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

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!

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

Thanks again !

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

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.