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?
- [Update]: In Excel 2019, and Excel for Office 365, you can use the new MINIFS and MAXIFS functions. There is more information on the MIN and MAX page on my Contextures site.
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)))
IMPORTANT: 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.
- [Update]: In Excel 2019, and Excel for Office 365, you can use the new MINIFS and MAXIFS functions. There is more information on the MIN and MAX page on my Contextures site.
_____________________
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
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!!