Creating Football Stats Database in Excel

[wmvaux]

I run a fantasy football (Australian Rules) game and I'm trying to set up some things so I can easily get the information I want displayed via an excel worksheet, pretty much a database that can add up players stats from all games played and to have things like top 10 goal scorers, top 10 kicks....etc

There are 6 teams and after game has been simulated, the program saves a text file that I have been copying and pasting into excel, this is how it looks for one team

I would like have the end product look like this also, but with totals of all games for each player, one of the issues is not all the same players play each week, so I can't simply just add the same cells type of thing.

There have been 4 rounds so far and I have tried putting all the teams stats one after another for each round and trying to use a pivot table, but I just can't get it to look the way I want it to look.

I know half the battle is setting this up the right way, I think my problem is I try to set it up from how I want it to look.

I realise there are going to be many steps involved with this, but I know the end product will be worth it I'd appreciate some help or advice if people can understand what I'm after

I am using Excel 2010

 

[Sal Paradise]

Assumptions:
1) Your data is located in a sheet called "Database" with Column A being #, B being Player, C being HO, through column L being DT.
2) You are creating a summary sheet called "Summary" with Column A being Player name, and columns B:K being HO through DT.

In Column A of "Summary" make a list of all team names.

In B2, copy this formula:
=SUMIF(Database!$B:$B,Summary!$A2,Database!C:C)

Now copy across to column K for each team you have in your league.

If you also need to have a Games column, just add it (let's say column L) and put the following formula in cell L2:
=COUNTIF(Database!$B:$B,Summary!$A2)

 

[wmvaux]

Assumptions:
1) Your data is located in a sheet called "Database" with Column A being #, B being Player, C being HO, through column L being DT.
2) You are creating a summary sheet called "Summary" with Column A being Player name, and columns B:K being HO through DT.

In Column A of "Summary" make a list of all team names.

In B2, copy this formula:
=SUMIF(Database!$B:$B,Summary!$A2,Database!C:C)

Now copy across to column K for each team you have in your league.

If you also need to have a Games column, just add it (let's say column L) and put the following formula in cell L2:
=COUNTIF(Database!$B:$B,Summary!$A2)

Thanks for that.

That worked for the over all team stats, but is there a way to do it for all of the players?

With relation to the games column you suggested, would that be total games by each player or games played by each team?

 

[Sal Paradise]

Without knowing your sheet format or what you are actually looking for, I'm at a loss.

Could you download an HTML creator and post what you want/what your data looks like?

 

[wmvaux]

Am I able to upload the excel file?

http://www.mediafire.com/?vs0h55h816b23eb

What you will see is the tabs... Totals is how I would like the team lists with players added up from all of the Rounds tabs, so far there have been 4 rounds.
The Rounds are set up in a way I could copy the data and save as an image to post. I copy that again in the database tab, the Summary is what you showed me and the Others tab has what else I would like to have displayed from the database.

 

[Sal Paradise]

I am unable to download your workbook. Sorry I can't be of more help.

 

[wmvaux]

I don't know how to use a HTML maker, so I'll show you some more pictures of what I want to be able to do.

This is the one from the information you gave me (Total Team Stats)

And here is one of the teams stats.... first one is how it should look with adding up Round 1 and Round 2....etc

The only issue with adding the stats for each round is sometimes there are different players, so how do we automatically add a new player?

 

[wmvaux]

Assumptions:
1) Your data is located in a sheet called "Database" with Column A being #, B being Player, C being HO, through column L being DT.
2) You are creating a summary sheet called "Summary" with Column A being Player name, and columns B:K being HO through DT.

In Column A of "Summary" make a list of all team names.

In B2, copy this formula:
=SUMIF(Database!$B:$B,Summary!$A2,Database!C:C)

Now copy across to column K for each team you have in your league.

Can this be used for the players instead of the teams?

 

[Sal Paradise]

Yes.

Just adjust the team name for the player name, and the column for team name with the column for player name. If you read the post you quoted -- it actually says for players (not teams) so you should be able to get it to work. If a new player comes along, just add his name to your player summary table and copy the formulas next to his/her name and it should work out.

The problem with your screenshots is that it doesn't show the sheet names or cell names, so it's hard to write formulas that will work "as-is" for your data.

To use an HTML Maker, just download one (like the one in my sig) and follow the instructions to give copies of your data via HTML (makes it easy to copy-paste and understand how your sheet is structured).

 

[wmvaux]

Ok, sorry for not showing sheet names or cell names

Here is the Database sheet as you can see it has players from 3 teams

Here is the Summary from before, but now I have renamed it Team Summary
and you can also see I have a blank one called Player Summary which I will play with to get the players total summary of stats.