Calculate Sortable Sports Standings with Goals For and Against in Excel
Question details
Create sports standings that remain accurate after sorting by calculating each team's statistics from a normalized game-results table.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to track home and away game scores, calculate team goals, wins, and points, and sort the standings in any order.
- Observed behavior
- Hard-coded cell references can return incorrect standings values after the standings table is sorted.
Enter every match only once in a game-results table, with separate columns for the home team, away team, home score, and away score. Use consistent team names in every row so SUMIFS criteria can match them correctly.
Build standings from a normalized game-results table
Use this method to calculate sortable standings without formulas depending on a team's current row position.
Keep match data separate from the standings table. The standings table should contain one row per team and use team names as the criteria for all calculations, so sorting the standings does not change the results.
On a Results worksheet, create columns named Home Team, Away Team, Home Goals, and Away Goals. Select the data range and choose Insert > Table, then name the table Games from the Table Design tab.
On a Standings worksheet, add a Team column with each team listed once. If you have Microsoft 365, enter =SORT(UNIQUE(VSTACK(Games[Home Team],Games[Away Team]))) in the first team cell; otherwise, copy both team columns to a single list and use Data > Remove Duplicates.
Add a Goals For column. For the team in A2, enter =SUMIFS(Games[Home Goals],Games[Home Team],A2)+SUMIFS(Games[Away Goals],Games[Away Team],A2), then fill the formula down.
Add a Goals Against column. For the team in A2, enter =SUMIFS(Games[Away Goals],Games[Home Team],A2)+SUMIFS(Games[Home Goals],Games[Away Team],A2), then fill down.
Add the remaining statistics, then select the entire standings table and use Data > Sort. Sort by Points from Largest to Smallest and add a second level such as goal difference if your competition uses it.
Add result helper columns to calculate wins and points
Use helper columns in the Games table when you need to calculate wins, draws, losses, and points under a defined scoring system.
Create Sortable Sports Standings in WPS Spreadsheets
WPS Spreadsheets lets you organize match results in a structured table and use SUMIFS formulas to calculate goals, wins, and points for a standings table that can be sorted safely.
- 1. Set up the match-results columns: In WPS Spreadsheets, create columns for Home Team, Away Team, Home Goals, and Away Goals, then enter one match per row.
- 2. Create the standings team list: On a separate sheet, add one unique team name per row in the Team column of the standings table.
- 3. Enter SUMIFS standings formulas: Use SUMIFS formulas to total home and away goals separately for each team, then add the two totals for Goals For and Goals Against.
- 4. Calculate points and sort the table: Add win and point formulas, select the standings range, and choose Data > Sort to rank teams by Points in descending order.
Frequently Asked Questions
Why do my standings formulas change after I sort the table?
This usually happens when formulas refer to fixed cells or nearby rows instead of the team name. Store games in a separate results table and use SUMIFS with the standings Team cell as the criterion.
How do I calculate Goals For when a team can play home or away?
Add the team's Home Goals when it appears in the Home Team column, then add its Away Goals when it appears in the Away Team column. For example: =SUMIFS(Games[Home Goals],Games[Home Team],A2)+SUMIFS(Games[Away Goals],Games[Away Team],A2).
How do I calculate Goals Against for each team?
Sum Away Goals where the team is the home team, then sum Home Goals where it is the away team. For example: =SUMIFS(Games[Away Goals],Games[Home Team],A2)+SUMIFS(Games[Home Goals],Games[Away Team],A2).
Can I calculate league points without manually updating every team?
Yes. Add Home Points and Away Points helper columns to the game-results table, then use SUMIFS to total both columns for each team in the standings table.
What is the best way to sort a sports standings table?
Select the entire standings table and use Data > Sort. Sort first by Points descending, then add tie-breaker levels such as Wins, Goal Difference, or Goals For according to your league rules.
