Averages with multiple conditions

[Jabe]

Hello,

I'm trying to create a dashboard which pulls averages from other spreadsheets like the example below. For each month we have a spreadsheet which tracks employee quality scores on certain activities. I'd like to pull the average score for each person, for each activity, for each month. Can someone suggest the best formula/function for doing so?

I've been trying to use AVERAGEIFS but haven't been able to get it working. Here's an example =AVERAGEIFS('August'!$C$3:$C$20,'August'!$A$3:$A$20,A3,'August'!$B$3:$B$20,B3) - with the C references being the Quality Scores, A references being the Employee Name, and B references being the Activity.

In the example below it looks like the formulas work (I'm not worried about the #DIV/0! errors as it's just from a simplification of the data set) - but I get #VALUE! errors when I open the dashboard but the source sheet is not open, is there a way around this? It would be impractical to get users of the dashboard to open all source sheets (kind of what the dashboard is meant to avoid!)

 

[Jabe]

Apologies, looks like my pictures didn't upload so trying again

 

[Fluff]

Hi & welcome to MrExcel.
How about

=AVERAGE(FILTER(August!$C$3:$C$20,(August!$A$3:$A$20=A3)*(August!$B$3:$B$20=B3)))