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.