Count games played

Here's the file I'm using. I've looked at the COUNTIF and SUMIF functions and I don't quite understand how to get it to do what I want. I guess I might understand if the examples given in the help section actually worked and I could see how it works.

Basically if a score is recorded (even if the score was zero) on the schedule, I want the GP column to show how many games they played.

Eventually I would like to populate the other fields as well. How many points a team scored, how many were scored against them, how many wins, loses, and ties. Also I would like to have how many points the team has, 3 points for a win, 1 point for a tie, and zero points for a loss. On my Standings sheet I would like the teams to be listed according to who has the highest points.

Is this too much for Excel maybe?

Thanks. But now how do I count them ONLY if they have played and logged a score on the schedule sheet.

Sorry, yes on the table sheet.

Thanks. It works, just not correctly. I know how many games should have been played by this point and it's not counting it right. It counts 10 when it should come up with 22. It appears to be only counting the games in column C or the left column.

I moved the score for the home team (column on the left) to the right side of the teams, and the score for the away team (column on the right) to the right side of the teams and it works.

So how do I now get it to sum up the scores each team scores in the GF column on the Table page? I've been fiddling with it and it's not giving me anything. Keeps saying there is a problem with the formula. I obviously don't understand formulas very well...

K, got that. Now, if I wanted goals scored against the team, how would I do that?

Now here's the one that totally blows my mind. How do I count only the games they won, the games they lost and the games they tied?

I've tried using a couple variations of =COUNTIFS(Schedule!B:D,B4,Schedule!A:C,"<>")
But I don't understand well enough how the formula works, in general.

I thought:
=COUNTIFS(Schedule!B:D,B4,Schedule!A:C,">")
But that didn't work. I get 0, it should be 2.

Then I thought:
=COUNTIF(Schedule!B:D,B4)>=COUNTIFS(Schedule!B3:D308,B4,Schedule!C3:E308,"<>")
But that gives me: TRUE
Then I put the COUNTIF formula on the end, after the COUNTIFS formula.

Am I even on the right track?

(BTW I changed columns around. Column A became C and C became E)

So I was way off... Hahaha! Told ya, I'm not very good with this. Let me try my hand at the losses.

I'm getting 12... It should be 2.

Ya, I'm working at it. I just know so little about Excel. I found something to challenge myself with while I have some serious downtime...

I get the concept of the code but not how to correct it so it shows 2 wins, not 12 wins.

Heh... I found it and was going to post that I found it to find you had just posted you found it. But, hey I found it!

So in this calculation to get LOSSES would I flip the greater than sign to less than?

I got LOSSES by flipping the greater than sign, it worked. But when I tried to do Equal instead of a greater than or less than sign it gave a way wrong number. I got 25 and it should be 13.

Okay, so to not count games not played then I use this thingy "<>". But I don't know where to add it and what all goes with it. I've tried adding it in a few places throughout and then adding with a Schedule!RANGE"<>" and I'm not getting it.

I did... I guess I thought it was post 16 which gave me the wins... I'm tired, it's WAY past my bedtime. (I'm currently in the middle east). Tomorrow I'll work on the Points and sorting by point leader.

Thanks for all your help. I'm starting to understand a little bit better.

Yes, everything is working. I managed to get the points and sorting working. Now I'm going to work on a record for at home win/loss/tie and then an away win/loss/tie and then after that a last 5 matches. But it may have to wait a bit as some very important work stuff just came up.

So, here's my file. I have only a couple things left to put on there. I'm so lost with how Excel works though so I I know I'll need some more help. The first item on my list is not that important, I mostly want the bracket set up. My intent is to be able to reuse this later too if I wish. I can just add teams to the list and it should auto populate the rest. Here are the two things I would like to get working.

I want to add a home win/loss/tie and an away win/loss/tie as well as a last 5 games played.

The second thing I want to get is the bracket set up to auto fill. The bracket is set up so the top 6 teams populate from the standings page. Those are easy. The next part is not so easy. The wild card teams are determined by points. So basically the first wild card is the team with the 7th highest amount of points irregardless of conference.

Those blank boxes on the standings sheet are for the home win/loss/tie, away win/loss/tie and the result of the last 5 games played. Those aren't really a big deal to me but I would like to eventually have them work. Basically the home win/loss/tie would show the home record in the format of X-X-X. It's just fun to be able to see their stats at home and away. The last 5 games isn't that big of a deal, but you can see a micro trend with it. The first column with the team names on the schedule page is the home team, and the second column with teams is the away team.

If there is a tie for any of the positions 7-10 the tie breaker would be determined by the GD column on the standings sheet. GD being the Goal Differential. The team with the higher GD wins the tie.

Am I asking too much of Excel to be able to do that?

My thought was to use the formulas for the W, L, and T columns, which would make sense but I don't know how to make the numbers display in the format X-X-X...

The Last 5 is the result of the last 5 games played. So, it would look like W-L-L-T-W, for example.

Also, I need to go back over the scores I've input the last couple weeks because two of the teams stats aren't matching up for some reason... Probably an error on my part.

It's really not that big of a deal. If I want it I'll come back for it.

I'm having an issue with the rankings, and the auto filling for the Wildcard spots. It's not pulling the right teams.

I've replaced the Last 5 column with a Rankings column and moved over the information from column Q to column M. I fixed the formula in the Sheet 1 page to pull the teams from, but it still isn't pulling the right teams.

It looks like it's pulling correctly, there's just a hiccup in the way the bracket is formed. For example, the 6th rank team, Real Salt Lake, is not in the top 6 which would automatically qualify in the tournament. Instead they are the number two seed for the wild cards... So, I guess it's not exactly the #7-10 teams but rather the 1st-4th teams AFTER the first 3 from each conference... Does that make sense? If not see below, if so ignore below.

As it sits right now it would look like:
W1 - LA Galaxy
W2 - FC Dallas
W3 - Seattle Sounders FC
E1 - Columbus Crew
E2 - Philadelphia Union
E3 - Sporting Kansas City
WC1 - Colorado Rapids
WC2 - Real Salt Lake
WC3 - Houston Dynamo
WC4 - New York Red Bulls

It seems to be working perfectly. With some corrections to my data entry, of course. Thanks! You've been awesome to help me out. If only I knew half as much as you did about this... You're the bomb!

I'm going to mark this thread as solved for now. If I decide later that I really want the Last 5 column I'll come back and revive it.

So I've missed a couple details I want to go back and cover...

First, my sorting isn't automatic. Is there a way to make it automatic?

Second, on the Bracket page, I want to have it autofill the bracket as I put scores in. So in the first game, the winner gets put into the box for the next game.

Good questions, I was half asleep... Definitely not thorough enough with my explanation.

On the Standings Page each conference would be sorted by who is in the lead according to points. I know how to do that manually but for some reason I was thinking it would do it on its own after I set it the first time.

Yes, I would enter them in the C6 or C10 on the Bracket page. I figure it easiest that way so I don't have to change anything when I reuse the sheet.

Okay, I ran into a hiccup with the bracket page. They released the rules for the wildcards. It's kind of complicated so I'll try to explain it the best I can... Here goes! Sorry for the double explanation... It's complicated!

There is an award to the team with the best regular season record, determined by who has the most points. This award is called the Supporter's Shield.

Wildcards: Wildcard 1 plays Wildcard 4. Wildcard 2 plays Wildcard 3. This round is called the Play-In round. The LOWEST seeded wildcard team to win in the Play-In round plays the winner of the Supporter Shield. The wildcard with the HIGHEST seed plays the Conference winner of the conference opposite to the Supports shield winner. SO....

If LA wins the Supporters Shield then the HIGHEST seeded Wildcard team enters the EAST conference and plays the top East team. The LOWEST seeded team after the play in round plays LA. So....

If WC1 plays WC4 and WC2 plays WC3.... Lets pretend WC4 wins, they play LA (or the SS winner). WC3 wins, they play the first place team from the East conference.

Does this make sense?

Also, my Standings page has an issue... It's sorting the teams right except I have an issue with a tie in points. Currently the #1 and #2 teams in the East Conference have the same number of points. The #2 team (Sporting Kansas City) has the better Goal Differential (GD) and thus they should be the #1 team. On the far right the rankings column is accurate, it's just not sorting the standings page correctly. It is however, putting the teams in their correct place on the Bracket page so there is no issue there. I think I failed to mention that criteria for sorting.

Here is my file. Thanks for looking.

Thanks a lot. I changed the macro, I see what you did, it really wasn't as complicated as I thought it would be.

So here is my updated file. I made some changes to the bracket page in hopes of making it make more sense. One thing I tried to do is make a box for the winner of the cup. In the top middle of the page is a box for this. I attempted to make it work but it's not working. What did I do wrong with the code?

Sure, no problem. I wanted to put the winner of the cup game (K10vP10) in cell L3. I put a formula in there but it's not working so I was wondering what I did wrong. All other formulas on the Bracket page are working as expected to. When I populate scores into the appropriate boxes the correct result populates in the next box in the bracket.

The ranges in the macro were supposed to be for B column but I fixed it.

Did you want another explanation of the Wildcard/Supporter's Shield rules?
Here goes anyway.

The Supporter's Shield Winner is the team with the highest points at the end of the regular season. I want this team to go in cell L17.

At the bottom of the page I have the Wildcard Play-In round. Each team is labeled as such:
WC1
WC2
WC3
WC4

WC 1 plays WC4 and WC2 plays WC3.
The winners will be placed in cells L28 and P28 with the WC# in the cell to the left (K28 and O28 respectively)

From there, the team with the lowest WC# (lowest seed) will play the team who wins the Supporter's Shield. Here's an example.

If WC1 and WC2 both win their games they advance to the regular playoff tournament. Who they play is determined by their seed. Thus, WC2 is the LOWEST seed and will play the winner of the Supporter's Shield.

The Supporter's Shield winner will be either from the West or the East. The WC# that is highest, in this scenario WC1, will play the conference winner from the conference opposite of the Supporter's Shield winner. If the team from the West, LA, wins the Supporter's Shield, WC1 will play the winner from the East Conference (currently Sporting Kansas City).

Does that make sense?

So, my formula was off by one thing... I put a comma where a colon should have been... Not bad I say.

Close. In L28 and P28, the cells to the left of each, K28 and O28 I wanted the Wildcard number. So just keep the two cells, WC1 and Real Salt Lake together. Does that make sense?

The results go in cells B8 and O28. As long as the lowest seed team plays the Supporter's Shield winner and the highest seed team plays the other conference winner. Does that make sense?

The K28 and O28 formulas are not showing correctly. They just show the formula.

The lowest seed team of the wildcards would be WC4, the highest would be WC1 because the team with the best record gets the WC1 spot and the team with the worst (qualifying) record gets WC4...

I'm not sure where to put the rest of the codes... It's not guaranteed that LA or anyone else from the West Conference for that matter, will win the Supporter's Shield...

Nevermind, K28 and O28 are showing correctly, copy and paste didn't work right.

Nevermind on the other stuff too. I got it. I'm now doing a complete test to make sure it is correctly showing how it should.

That is it. Everything works the way it should work. If I wanted to change the color of the lines on the standings page, how would I do that? I tried just changing them but that didn't work and then I looked in the Macro (I didn't think it would be there) and didn't see it there. I'm not sure where else to look.

Thanks to NBVC who, if you read this whole threat, did all the work. I didn't realize how complicated some of the formulas were. Thanks for being so patient and willing to help me out.

This is a tracker for the US Major League Soccer. There are three tabs, Schedule, Standings, and Bracket. The file is designed to automatically populate on its own, where you only need put data into the Schedule page. The data needed to be input is the date, home team (left column) away team (right column) and the result of the game. The date is not entirely important except that if there is a game to be played today the date will be highlighted yellow.

Once the score of the game is put in the correct spot the Standings page will populate. Based on the standings page the Bracket page will also populate.

The Wildcard rules are based on the 2011 season rules which may or may not be the same in future years. They have been fairly similar for years so this should work. The only reason any of this will need to be changed is with the addition of new teams, which happens every couple years. To add another team just insert a cell for the proper conference and type in the name of the team. How the tournament rules change may determine whether this bracket will work or not.

I added a couple pictures for the fun of it, and to give it a little aesthetics.

Thanks for all the help.

Perfect, that works nicely. Thanks!

I tested and double tested. It works as it should.

Wow... So it doesn't work 100%, it's about 99% though. I found an issue I didn't consider. In the standings I have two teams with the same number of points and the same GD (Goal Differential) but they are from different conferences. So, I have Houston from the East who is in the second place spot for the East Conference and I have Colorado who is the 5th place team in the West Conference.

The problem is that because Houston is tied with Colorado for 6th place overall (in the ranking created for this spreadsheet) Houston is showing up in the #2 Wild Card spot, but they are also showing up in the East Conference #2 spot. Being the #2 in the conference puts them on the bracket already thus they don't need to be considered for a wildcard spot.

It should look like this:
East Conference
Philadelphia
Houston
Sporting Kansas City

West Conference
LA
Seattle
Real Salt Lake

Wild Cards
Dallas
Colorado
Columbus
New York

Okay, that fixed that issue... Man these tiebreakers are breaking this thing...

I saw another thread on here about how to decide a tiebreaker based on the head-to-head rule. Which is whichever team has the best winning record against the team they are tied against. At this point, can this be incorporated into the spread sheet?

Sporting KC and Philadelphia are both tied in points and the first tie breaker is whoever has the best record against the other team. Since the two teams play each other twice it could still end in a tie. If the first tiebreaker fails then the second is who has the better Goal Differential.

In this particular case, Sporting KC and Philadelphia both have 48 points, and they tied each other in both of their games, and they both have a GD of 9, so the next tie breaker is total goals. Is it possible to incorporate all of this in this sheet?

Here are the first 3 tiebreaker rules.
The highest position shall be awarded to the team with the better win–loss record in current regular-season games against all other teams equal in points. (head-to-head competition)

If the teams are still equal in the standings, the highest position shall be awarded to the team with the greater goal difference against all other teams during the regular season. (goal differential)

If the teams are still equal in the standings, the highest position shall be awarded to the team scoring the greatest number of total goals against all other teams during the regular season. (total goals)