Solved

A "MAXIFS()" function in Excel

Posted on 2015-01-02
9
3,490 Views
Last Modified: 2015-01-16
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
Comment
Question by:WeThotUWasAToad
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40528605
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
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40528613
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
 
LVL 11

Expert Comment

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

Max ifMAXIF-number-2.xlsx
0
 
LVL 80

Accepted Solution

by:
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:WeThotUWasAToad
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 80

Assisted Solution

by:byundt
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 80

Assisted Solution

by:byundt
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

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

by:WeThotUWasAToad
ID: 40554609
Excellent responses and explanations. Thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now