Creating Football Stats Database in Excel

wmvaux

New Member
Joined
Sep 8, 2011
Messages
26
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
1rro8n.jpg


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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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)
 
Upvote 0
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.

55i2yw.jpg


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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.

2rp4euq.jpg


This is the one from the information you gave me (Total Team Stats)
21az5fr.jpg


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


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?
 
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0
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
2rmbfj9.jpg



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.
ws0efs.jpg
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top