Collecting average values from multiple sheets with multiple conditions

[redpoll]

Hi all,

I have multi-sheet excel file where each sheet represents one round of golf hole by hole.

Sheet name = Round1

[TABLE="width: 222"]
<tbody>[TR]
[TD][TABLE="width: 307"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Hole[/TD]
[TD]Mts[/TD]
[TD]Score[/TD]
[TD]App[/TD]
[TD]Club[/TD]
[TD]Dist[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]349[/TD]
[TD]4[/TD]
[TD]S[/TD]
[TD]6i[/TD]
[TD]158[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]480[/TD]
[TD]5[/TD]
[TD]H[/TD]
[TD]7i[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]212[/TD]
[TD]3[/TD]
[TD]S[/TD]
[TD]9i[/TD]
[TD]122[/TD]
[/TR]
</tbody>[/TABLE]
...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet name = Round99

[TABLE="width: 307"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Hole[/TD]
[TD]Mts[/TD]
[TD]Score[/TD]
[TD]App[/TD]
[TD]Club[/TD]
[TD]Dist[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]349[/TD]
[TD]4[/TD]
[TD]S[/TD]
[TD]6i[/TD]
[TD]158[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]480[/TD]
[TD]5[/TD]
[TD]S[/TD]
[TD]6i[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]212[/TD]
[TD]3[/TD]
[TD]H[/TD]
[TD]9i[/TD]
[TD]122[/TD]
[/TR]
</tbody>[/TABLE]
...

I would like to create one sheet that collects AVERAGE value from sheets Round1 to Round99 column F for each club (column E) when column D="H" as I've tried to illustrate below:

[TABLE="width: 421"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Club[/TD]
[TD]Distance[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6i[/TD]
[TD](AVG of F IF E2:E19="6i" and D2:D19="H")[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]7i[/TD]
[TD](AVG of F IF E2:E19="7i" and D2:D19="H")[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9i[/TD]
[TD](AVG of F IF E2:E19="9i" and D2:D19="H")[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 179"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Can someone help me on this?

Thank you!

 

[Special-K99]

Using VBA or formulas?

 

[redpoll]

Using VBA or formulas?

Preferably formulas.

 

[redpoll]

Any suggestions?

 

[Aladin Akyurek]

In B2 of the destination sheet enter:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&"Round"&ROW($1:$99)&"'!F2:F19"),INDIRECT("'"&"Round"&ROW($1:$99)&"'!E2:E19"),A2,INDIRECT("'"&"Round"&ROW($1:$99)&"'!D2:D19"),"H"))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&"Round"&ROW($1:$99)&"'!E2:E19"),A2,INDIRECT("'"&"Round"&ROW($1:$99)&"'!D2:D19"),"H"))