Using SUMIF and COUNTIF to calculate number of particular instances against one team

[ajw1982]

Hi,

I've just joined here in the hope that someone can help. My Excel skills are OK, but certainly nowhere near advanced! (I put "complex" in inverted commas as perhaps this is not at all complex to some of you!). Basically I want to know if the following is possible (I really hope it is!):

I have a spreadsheet with football (soccer) data from past seasons. It contains details of all the fixtures and a number of stats for each game.

An example of what I would like to be able to do: In column C we have the home teams listed. In column E we have the number of goals scored by the home team (in that match).

I'd like to create a formula that can do the following: search column C for all instances of a team (e.g. "Arsenal"), cross reference to column E for the values of home team goals scored in each of these instances. I'd then like excel to add these figures up (number of home team goals scored), and divide by the number of instances to create an average.

e.g. In my 2010/2011 season data Arsenal will be in column C 19 times (19 home games), let's say that in those 19 games the sum is 38, then divided by the 19 = average of 2.

Obviously I can do the simple bits of the formula but need a way to automate the process as above... otherwise it will take me forever to do manually and cannot then simply be applied across different columns.

Please tell me this is possible, and if so, how can I do it?

Thanks in advance.

Alex

[martindwilson]

=sumif(c1:c100,"arsenal",e1:e100)/countif(c1:c100,"arsenal")

[ajw1982]

Martin... amazing. Works perfectly. Thank you so much. You've just saved me hours and hours of time! Really appreciate it.

I've only recently started using excel in detail to analyze football for a trading system I'm working on... something very satisfying about putting that formula in and watching the right number pop out!

Thanks again.

[ajw1982]

Hi Martin, all,

Another question if you don't mind. I would now like to be able to get Excel to calculate the number of instances of certain results.

So C contains the home team name, E contains home team goals scored in that match, F contains away team goals scored in that match.

In my analysis sheet I will set up a row for arsenal, and then list all the possible results, 0-0, 1-0, 1-1, 1-2 etc. and would then like Excel to calculate under each the number of instances of e.g. 0-0, 1-0, 1-1 etc. when arsenal were at home.

I know the basic logic should be similar to the previous formula, and perhaps using the "count" function, but at the moment I'm not getting it to work!

Then, to take it one step further... column B contains the date. Would it be possible for Excel to tell me the above for only the last 10 historical games... e.g. under the 0-0 column (on the Arsenal row), the number of instances of 0-0 result when arsenal were at home in the last 10 games?

Thanks,
Alex