Trying to create a formula to average survey results per instructor

[nscarritt]

I am trying to come up with a good way to average a particular question in a survey result for instructors, however the caveat has me pretty puzzled. We're looking to be able to filter by mm/dd/yyyy date (which pulls mm/dd/yyyy hh:mm from raw data) and then have one average of all scores for each instructor listed. It gets a bit complicated in that the actual survey has 6 drop down boxes with all the instructors names, so one instructor could have data for the same (or any) date in any of the 6 colored columns (name, score, qualitative x 6).

My initial thought was to list out the names on a second sheet and then have some sort of averageifs to combine the columns, but I am a bit lost on how to take the dates into account, let alone how to sum the columns then average. Any help will be massively appreciated!

SurveySample.xlsx

[6StringJazzer]

I'm having trouble making sense of the way your data is organized. Each row is for a course offered on a particular date, with up to 6 facilitators to rate in columns D:U. It would be much better for each row be for a single combination of course, date, and facilitator. Then you can use a pivot table to automatically do everything you are asking for.

[gak67]

An AVERAGEIF (or AVERAGEIFS) won't work over multiple columns. You could sum 6 SUMIFS of the scores form the 6 columns with the date and instructor as criteria, and divide it by the sum of the 6 COUNTIFS. I'm not that familiar with array formulas yet, but somebody else here could let you know if one of those can help in this case.