Solved

# A "MAXIFS()" function in Excel

Posted on 2015-01-02
4,407 Views
Hello,

What would be the simplest way to create a sort of "MAXIFS()" function in Excel (2013)?

I know MAXIFS() does not exist but I'm looking for a way to simulate other "…IFS()" functions, eg COUNTIFS(), SUMIFS(), AVERAGEIFS(), but in this case, to find the greatest value in "a range of cells specified by a given set of conditions or criteria".

For example, suppose the spreadsheet shown in the following screenshot contains one range (C7:C16) in which each cell's value is 2014 or 2015, and an adjacent range (D7:D16) in which each cell can contain any 2-digit number.
What formula in D3 & D4 would return the maximum value of those values in the column D range which are specified by the year shown in C3 & C4 respectively?

The colors in the following screenshot make it easier to see that the correct solution formulas in D3 & D4 would return the values 39 & 78 respectively.
Thanks
0
• 3
• 3
• 2
• +1

LVL 11

Expert Comment

ID: 40528605
Is this what you are looking for?

Look at the attachment

``````=MAX(IF(A2:A7=D3,B2:B7))
``````
Max-if.xlsx
0

LVL 11

Expert Comment

ID: 40528613
In your case, it would be
In cell D3:
``````=MAX(IF(C7:C16=C3,D7:D16))
``````
and in cell D4:
``````=MAX(IF(C7:C16=C4,D7:D16))
``````
0

LVL 11

Expert Comment

ID: 40528631
here is a sample with what you needs.

MAXIF-number-2.xlsx
0

LVL 81

Accepted Solution

byundt earned 375 total points
ID: 40528680
I'm glad to see that Wilder1626's second workbook shows an array-entered formula. It won't work reliably if you don't array enter the formula.

The simple array-entered MAX & IF formula will return 0 if there are no matches to the criteria. This is because Excel assumes FALSE (or 0) is returned by the IF should the criteria not be matched. Assuming that 0 is not a valid answer, you can trap this possibility by taking the reciprocal of the value returned by MAX twice. For example:
=IFERROR(1/(1/MAX(IF(A\$2:A\$7=D2,B\$2:B\$7))),"no match")              must be array-entered

If you have more than one criteria, you can multiply them as Boolean expressions inside the IF:
=IFERROR(1/(1/MAX(IF((A\$2:A\$7=D2)*(C\$2:C\$7="red"),B\$2:B\$7))),"no match")              must be array-entered
Max-ifQ28589900.xlsx
0

Author Comment

ID: 40528693
Thanks for the great responses — both the formulas and the explanation.

I'm happy to say that I recognized the need to enter Wilder1626's solution as an array formula before seeing Brad's post. But as usual Brad, the additional information and deeper insight are much appreciated.

A couple of questions:

Why do the formulas in Wilder1626's first attachment return the correct values even if not array-entered while the formulas in the 2nd attachment do not?

Also, googling just led me to a short article on Excel array entries* which mentions that selecting a range within a formula in the formula bar and pressing F9 reveals the range as an array of values. That's a handy trick but is there another shortcut key or any other way to switch it back (ie to show the range again rather than the values it contains)?

Thanks again
*http://www.excel-easy.com/functions/array-formulas.html
0

LVL 81

Assisted Solution

byundt earned 375 total points
ID: 40528702
With respect to the successful operation of the first formula (without array-entry), I found it insightful to use the Formulas...Evaluate Formula menu item. In doing so, it appears that Excel evaluates the criteria as a single value, then returns the entire range (B2:B7) if the criteria is TRUE. If the criteria evaluates FALSE, then 0 will be returned.

I also noticed that Excel was choosing a value within A2:A7 that was on the same row as D2 or D3. Since those values lined up, the criteria always returned TRUE, and the formula returned the MAX (or MIN) of range B2:B7. Those happened to be the right answers, but that was just an accidental result of the sample data.

If you change both formulas on Wilder1626's workbook to use the MAX function, you will see that they return 60 in each case--the maximum value in B2:B7, even though the first formula ought to have returned 50.

Furthermore, if you swap the values of D2 & D3 (i.e. 2015 in D2 and 2014 in D3), you will see the criteria evaluates as FALSE, and 0 is returned.

With respect to the trick with selecting an array parameter and then hitting F9 to see the values returned, the ESC key will restore your original formula.
0

LVL 81

Assisted Solution

byundt earned 375 total points
ID: 40528716
Regarding Wilder1626's second workbook, those formulas work as expected when array-entered. They return #VALUE! error values if not array-entered because the IF tests the criteria by choosing a test value from C7:C22 on the same row as the formula. When the formula is not on one of the rows in test range C7:C22, then IF returns #VALUE!

For further enlightenment, try copying cells C3:D4 and pasting them in various places in G7:G21. Depending on whether the dates in column G match those in C7:C22, the non-array-entered formulas will return either 92 or 0.

What you are seeing through this exercise is a Microsoft programmer who tried really hard to return a meaningful result when the user made a hash of the input parameters. If Lotus123 did it that way, then that's why Excel does too. But if Lotus123 doesn't accept that syntax, then you just have to say "Hmm..."
0

LVL 50

Assisted Solution

barry houdini earned 125 total points
ID: 40529232
Hello Steve,

I'm surprised Brad didn't mention using AGGREGATE function here (he usually does!)

In Excel 2010 or later you can use that function with 14 as the first argument (LARGE functionality) to get the largest value based on conditions......and the bonus is that you don't need "array entry", e.g. on Brad's sheet you can use this formula in I2 copied down

=IFERROR(AGGREGATE(14,6,B\$2:B\$7/(A\$2:A\$7=D2),1),"No match")

and this one in J2

=IFERROR(AGGREGATE(14,6,B\$2:B\$7/(A\$2:A\$7=D2)/(C\$2:C\$7="red"),1),"No match")

regards, barry

PS - An alternative to using Brad's 1/(1/ construction is to use LARGE rather than MAX, if there are no rows which fulfill the criteria you'll get #NUM! error rather than 0. That approach might be preferable, as it doesn't exclude the possibility of zero being the largest value.
0

Author Closing Comment

ID: 40554609
Excellent responses and explanations. Thanks!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…