[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8649
  • Last Modified:

A "MAXIFS()" function in Excel

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.
Fig. 1What 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.
Fig. 2Thanks
0
WeThotUWasAToad
Asked:
WeThotUWasAToad
  • 3
  • 3
  • 2
  • +1
4 Solutions
 
Wilder1626Commented:
Is this what you are looking for?

Look at the attachment

=MAX(IF(A2:A7=D3,B2:B7))

Open in new window

Max-if.xlsx
0
 
Wilder1626Commented:
In your case, it would be
In cell D3:
=MAX(IF(C7:C16=C3,D7:D16))

Open in new window

and in cell D4:
=MAX(IF(C7:C16=C4,D7:D16))

Open in new window

0
 
Wilder1626Commented:
here is a sample with what you needs.

Max ifMAXIF-number-2.xlsx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
byundtCommented:
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
 
WeThotUWasAToadAuthor Commented:
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
 
byundtCommented:
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
 
byundtCommented:
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
 
barry houdiniCommented:
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
 
WeThotUWasAToadAuthor Commented:
Excellent responses and explanations. Thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now