A "MAXIFS()" function in Excel

Posted on 2015-01-02
Medium Priority
Last Modified: 2015-01-16

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
Question by:WeThotUWasAToad
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
LVL 11

Expert Comment

ID: 40528605
Is this what you are looking for?

Look at the attachment


Open in new window

LVL 11

Expert Comment

ID: 40528613
In your case, it would be
In cell D3:

Open in new window

and in cell D4:

Open in new window

LVL 11

Expert Comment

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

Max ifMAXIF-number-2.xlsx

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

LVL 81

Accepted Solution

byundt earned 1500 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

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
LVL 81

Assisted Solution

byundt earned 1500 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.
LVL 81

Assisted Solution

byundt earned 1500 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..."
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 500 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.

Author Closing Comment

ID: 40554609
Excellent responses and explanations. Thanks!

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question