Averageifs Function with dynamic number of Criteria - Toggle Criteria On/Off

[longtail18]

Hello all,

I have spent the better part of a day unsuccessfully trying to see if there was a way to toggle the number of criteria on / off to perform a dynamic average.

I have included a screen shot of file - essentially, I would like to toggle on / off certain attributes and perform an average. I am running into issues when I toggle attributes off, the calc does not run on the remaining attributes with Yes.



I tried using a sumproduct formula and still had no luck. Please let me know if this is possible.

Thank you,
Chris

 

[Eric W]

One possibility is to use the wildcard option of the SUMIFS/AVERAGEIFS functions. If there is a criterion you don't want to use, just put a * in the cell. For example, in your J6 cell you can use:

=SUMIFS(G:G,C:C,J3,E:E,J4,F:F,J5)

and you'll get the result you have now. If you don't care about the season, just put * in J3, and it will include row 6 in the amount.