AverageIFS across multiple sheets (including 0s)

[NickAFL]

Good morning

I am trying to create a sheet which will average all of the compliance scores for the company's advisers. There are separate sheets for each adviser. I need to be able to summarise all of their scores onto one sheet which will then change based on the selected product and selected month.

I have attached the basic sheets. I need each sheet row (Rows 15-117 on the sheets) to show the average score for that row on the Scores Ave sheet based on whatever Review Month or Product Set is shown in D2 & D11 on the Scores Ave Sheet.

Many thanks in advance.

Nick

 

[awoohaw]

You can use Power Query to Import and clean up each worksheet into the Excel Data Model, and then build pivot reports off of it.
There are many YouTube channels that have great tutorials on PQ. Mr. Excel, ExcelIsFun, MyOnlineTrainingHub, and Leila Gharani, here is a playlist by MyOnlineTrainingHub that I've referred to often:

https://www.youtube.com/playlist?list=PLmd91OWgLVSKnVrL0YxdOH61MAiqlFHac