Formula for calculating the below scenario

[PrashanthGM12345]

I have 5 Tables Table A, B, C, D, E.
Table A, B, C , D has 3 Columns : Audit Date, Opportunity ID and Audit Score
Table E has 2 Columns : Audit Months like Jan, Feb, March,.... Dec and Average Audit Score for a month.
I want a formula to do the following scenario.
It should scan the Audit Date columns (A,E,I,M) of Table A,B,C,D and calculate the average audit score Columns (C,G,K,O) of Month = January and copy the average audit score of Jan Month in the Cell R4.
similarly it should do for all the remaining months in column A,E,I,M until December and update the average score in the corresponding cell in Table E.

Note: While calculating Average it should not do
((Average of Table A audit score + Average of Table B audit score + Average of Table C audit score + Average of Table D audit score) AND finally take the average again of all the 4 averages.) since AVERAGE OF AVERAGE is a wrong Approach.

So first for the month of jan, it should take the (Sum of Table A audit score for jan month + sum of table B Audit score for jan month + Sum of Table C audit score for jan month + Sum of Table D audit score for jan month) and then take the average for january month.
Similarly it should do for all the other months.

I have attached the file for reference.
Also i am pasting the link which i had used for doing the same for 1 table : https://chandoo.org/forum/threads/formula-or-macro-or-function-confused.37539/#post-225547

 

[GraH - Guido]

use a sum(if) and count(if), then divide both. Sound like doing an average.

 

[PrashanthGM12345]

Can you please elaborate? I didnt catch you

 

[GraH - Guido]

Please see file.

 

[PrashanthGM12345]

Awesome. Seriously great work. Very much impressed. Thanks a ton

 

[bosco_yip]

Or........

In R4, copied down :

=IFERROR(AVERAGEIFS($C$3:$O$52,$A$3:$M$52,">="&Q4,$A$3:$M$52,"<"&EDATE(Q4,1))/100,"")

Regards
Bosco

 

[PrashanthGM12345]

It works. Thank you Bosco. Appreciate your efforts

 

[GraH - Guido]

Or........
In R4, copied down :
=IFERROR(AVERAGEIFS($C$3:$O$52,$A$3:$M$52,">="&Q4,$A$3:$M$52,"<"&EDATE(Q4,1))/100,"")

Wait a minute... these functions actually work on ranges over multiple columns?! I feel rather stupid now for not knowing this. Still wondering how it works, since the criteria arguments are also evaluated towards all cells referenced in the array I would expect. So there are situations where this does not work? I my head currently I go #syntaxError. Need to study that a bit.