Forum Discussion
The average of the product of two columns across several worksheets
In my understanding you have solved the task yourself. Your solution is much easier than Power Query and if i may i would suggest to enter an additional column in all of your 54 sheets.
All your sheets have the same layout which means that you can easily add the additional column.
You can select all the 54 Assembly and Machining sheets by activating the first (Assembly) sheet and then holding down the ctrl key and select the remaining sheets with mouseclicks.
Then enter formula
=C3*D3
in the first sheet and copy the formula down.
Then hold the ctrl key down again and deselect the other sheets with mouseclicks.
Now in all sheets the formulas are entered and the product column is added.
Why not append the tables from several worksheets with Power Query? After this it's simple to calculate the sumproduct and the average. Writing VBA code for this would be much more difficult and setting up a formula with the INDIRECT function for 15+ worksheets to achieve this result, i wonder if this is even possible.
Does the example in sheet "Tabelle1" in the attached file show what you want to do? If so i would append all tables with Power Query and then apply the formulas in one resulting worksheet.
It didn't quite match the idea, I edited it to give you a better picture of the problem.
I removed the merged cells A2 and B2 in all tables in order to convert the tables Assembly 1 - 3 and Machining 1 - 3 into dynamic tables and to append them with Power Query.
In the summary table the AVERAGEIF and AVERAGEIFS formulas calculate the results you might be looking for.