Count average grounds of a selection of towns

[RubenFind]

Hi all,

Hope you can help me with the following issue. I want to calculate the average number of grounds in a selection of cities (E2:E4). In this example, the number of grounds per city are given in column C.
However, I don't succeed in getting the correct number, because it takes into account every type of sports (3*8 + 2*5 + 1*8). In this example it needs to be 21 (8+5+8). Selecting one type of sports is not possible, because not every town has the same sports (e.g. football is not in Mosbach, and golf is not in Aachen). The zero's in column C must be left out. To illustrate my issue, I simplified the example. However, in my dataset, the number of towns and the selection of cities (in collumn E) are quite large, what makes it hard to calculate the averages manually.
Does anyone have a solution?

Thank you very much in advance.

 

[I_know_nuffin]

try
=SUMIF(A$1:C$18,E2,C$1:C$18)/(COUNTIF(A$1:A$18,E2)-COUNTIFS(A$1:A$18,E2,C$1:C$18,0))

 

[RubenFind]

try
=SUMIF(A$1:C$18,E2,C$1:C$18)/(COUNTIF(A$1:A$18,E2)-COUNTIFS(A$1:A$18,E2,C$1:C$18,0))

Thanks a lot for helping me out! The formula gives 8 as a result. However, the average of the 3 towns (Aachen, Mosbach, Nürnberg) should be 7. It seems that the formula now only gives the average grounds of Aachen. Any idea how to adjust the formula so that it give the average of all the three towns in column E?

Thanks again! Really appreciate it.

 

[I_know_nuffin]

If you drag the formula down to F3 & F4 it gives the average for each town
then sum that and divide by 3
F7 =sum(F2:F4)/3