asked on

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.

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

Wilder1626

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

Wilder1626

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

Wilder1626

here is a sample with what you needs.

MAXIF-number-2.xlsx

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WeThotUWasAToad

ASKER

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

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial