Tips > Create an Excel Football League Table

# Calculate the League Table Statistics

The Calculations sheet will perform all the working out for presentation to the League Table sheet. We need to calculate number of games won, goals scored etc.

## Number of Games Played

To calculate the number of games played in cells D3:D22, you will need the COUNT function. The example below shows adding up the games played for Aston Villa.

=COUNT('Aston Villa'!\$C3:\$C40)

## Games Won, Lost and Drawn

The COUNTIFS function should be used to count the number of games each team has won, drawn and lost at home and away. COUNTIFS can handle both conditions.

The example below shows the COUNTIFS function counting the number of home wins for Arsenal.

=COUNTIFS(Arsenal!\$F\$3:\$F\$40,"W",Arsenal!\$B\$3:\$B\$40,"H")

## Add Goals Scored and Conceded

To add the goals scored and conceded by each team, we will use the SUMIF function.

The SUMIF function is used to add all the values that meet a condition. The example below shows the number of goals scored at home by Arsenal.

It looks in column B of the Arsenal sheet for "H" to represent a home game and adds the value in column C.

=SUMIF(Arsenal!\$B\$3:\$C\$40,"H",Arsenal!\$C\$3:\$C\$40)

## Work out the Goal Difference

The goal difference for home and away games can be calculated by subtracting the goals against from the goals scored.

To find out the overall goal difference, add the home and away goal differences together.

## Calculate the Points Total for each Team

To calculate the points total for the home and away games:

1. Multiply the number of wins by the cell storing the number of points awarded for a win on the Data sheet.
2. Multiply the number of draws by the cell storing the number of points awarded for a draw on the Data sheet

The example below shows a formula for the above. The formula calculates the points earned at home by Arsenal.

=\$E3*Data!\$B\$2+Calculations!\$G3*Data!\$B\$3

• E3 holds the number of wins
• B2 holds the points earned for a win
• G3 holds the number of draws
• B3 holds the points awarded for a draw

To find the overall points total, add together the total points earned at home and the total earned from away games.