Nesting If Statements Within an Average Array Calculation

[elizabethc]

I am trying to create a simple way for anyone using my spreadsheet to see an average calculation using data disaggregated by one or more demographics. Using an array, I have been able to calculate the average if there is a value selected for each of the demographics, but I cannot figure out how to make it work if the the user does not want to disaggregate the data by one or more of the demographics. I'm assuming I would need to add some addition If statements, but I'm not sure where to start. Any help is much appreciated!

Screenshot 2020-01-08 08.55.33.png

The formula I am using is: =AVERAGE(IF(AllResponses_Edited!A:A=B2,IF(AllResponses_Edited!B:B=B3,IF(AllResponses_Edited!C:C=B4,IF(AllResponses_Edited!D:D=B5,IF(AllResponses_Edited!E:E=B6,IF(AllResponses_Edited!F:F=B7,IF(AllResponses_Edited!G:G=B8,IF(AllResponses_Edited!H:L<>"",AllResponses_Edited!H:L)))))))))

Note that this spreadsheet is using all dummy data; it contains no private information.

[FDibbins]

Hi, welcome to the forum

perhaps it would help (us) if you added some headings to you data table, and ID'd what the different indexes are for?
I have a feeling that SUMPRODUCT or AVERAGEIF may be what you need here.

[elizabethc]

Thank you! It's good to be here.

I've added data headers in the attached. Thanks for the suggestion. It's my understanding that the Averageifs formula won't work if the data being averaged spans a different number of columns than for the if criteria. I suppose I could adjust my data format to resolve this. But, even so, I'm still not sure how I would achieve the goal of being able to choose to filter by one or more demographics rather than all. Essentially, how do I make it work if someone selects "Any" from one or more of the dropdowns I've created?

Appreciate the help!

-Elizabeth