New *IFs function to accept any aggregating function
The *Ifs functions are very popular among excel users, it seems like every new version of excel adds a few more of these functions,and I see a bunch of requests on here to add even more. perhaps the best approach may be to combine them into a Ifs function to rule them all.
it would be similar to existing IFS functions, but the first parameter would let you define what to do with the matching data.
e.g. =Sumifs(A:A,B:B,E1,C:C,F1) could become =NewFunction("Sum",A:A,B:B,E1,C:C,F1)
=Averageifs(A:A,B:B,E1,C:C,F1) could become =NewFunction("Average",A:A,B:B,E1,C:C,F1)
this would allow the creation of new IFS functionality like StddevIfs, VarPIfs, MedianIfs, ModeIfs, ProductIfs and so many more with out creating a ton of new functions.
Gerdami Des Betes commented
Yes, I was about to ask for MINIFS and MAXIFS.
Ryan Hoover commented
in response to Kenneth's post:
"It would be nice if we could pass the aggregate functions themselves to as arguments, rather than strings representing them (e.g. NewFunction(SUM,A:A,B:B,E1,C:C,F1)). That is, your new function would be a higher-order function."
as a programmer this sounds like a great idea at first but I feel that enumeration / strings would ultimately be the best way to handle this suggestion, primarily because I do not think that Excel treats functions as first-class citizens; and it would be consistent with the functionality of the subtotal function.
A generalisation of the Aggregating (aka. Reducing or Folding) functions would indeed be helpful. We have SumProduct, SumIfs, the aforementioned lack of other aggregating functions, and we have array formula for more general usage though they have their own serious usability limitations.
Ryan's idea of having an enumerated value of Sum/Average/StdDev to supply to formulae is a good one.
While I'm at it, the problem with IF statements in SumProduct or array formulae is that you have to return some kind of null value (e.g. zero or empty string, and sometimes there isn't an appropriate one) when you apply a function, whereas you really want to apply an actual Filter (Where clause) to the array that omits these elements entirely.
SUMIF (and others) sums only one range. If you have to sum different ranges with same criteria you have to do as many SUMIF as ranges that you have:
But if you have to evaluate 3 different columns with the same criteria the formula becomes:
It would be great if the formula could use more ranges like:
Kenneth Barber commented
I can't see that being used too often. To get same effect, have a "Visible?" column, where you use SUBTOTAL functions that refer to a cell in the same row as the SUBTOTAL function. This column lets you know if the row is visible or not. Then include this column in SUMIFS.
Kenneth Barber commented
It would be nice if we could pass the aggregate functions themselves to as arguments, rather than strings representing them (e.g. NewFunction(SUM,A:A,B:B,E1,C:C,F1)). That is, your new function would be a higher-order function.
Your function is also less memory-intensive (though less versatile) than my suggestions below, where I suggest a FILTER function that can be operated on by the regular SUM, AVERAGE, etc. functions. This is because you can calculate your total, product, maximum, etc. as you are filtering, rather than doing them in separate steps.
Combine SUBTOTAL and SUMIFS to get filtered rows taken into account.
SUMIFS currently calculates the sum of an row independently of an active filter. It would be helpful to have an additional function which provides conditional sum and takes the actual filter into account like