## Team ranking with criteria

Today I´ll show you how to rank teams using excel formulas. The following example ranks soccer teams in the Premier League. But first, how are soccer teams ranked in Premier League?

*Teams are ranked by total points, then goal difference, and then goals scored. At the end of each season, the club with the most points is crowned champion. If points are equal, the goal difference and then goals scored determine the winner. If still equal, teams are deemed to occupy the same position. *Source: Wikipedia

The picture below shows teams and statistics in premier league, sorted by team name.

### Calculating team ranks

**1. Teams are ranked by total points**

Total points are calculated like this, a match winner gets 3 points, a draw gives 1 point. The formula in cell J2:

=D2*3+E2

Ranking points formula in cell K2:

=COUNTIF($J$2:$J$21,">"&J2)+1

**2. Teams are then ranked by goal difference**

Ranking GD formula in cell L2:

=(COUNTIF($I$2:$I$21,">"&I2)+1)/100

**3. Teams are then ranked by goal score**

Ranking GA formula in cell L2:

=(COUNTIF($G$2:$G$21,">"&G2)+1)/10000

**4. Sum rank values**

Total rank in cell N2:

=K2+L2+M2

**Calculating final rank in cell O2:**

=COUNTIF($N$2:$N$21,"<"&N2)+1

### Explaining formulas

The formulas are made so that the ranking calculations can be summed and the total rank shows the ranking order. For example, rank points are between 1-19. Rank Goal difference are between 0.01-0.19. Rank Goals against are between 0.0001 and 0.0019.

The second formula is divided by 100 and the third formula is divided by 10000. So how do you know these numbers? What if the second formula was divided by 10? The numbers would be 0.1-1.9. Numbers equal to and above 1 distorts the first ranking formula. But if there were less than 10 teams the formula would return 0.1-0.9 and that is fine. Therefore, the calculations depend on the total number of teams.

If you want to know how the COUNTIF function works, read this: COUNTIF function

### Sort teams by rank

You can now sort the teams by rank. Select cell range B2:O21 and right click on range. Click "Sort" and then "Custom Sort...".

Sort by Column O, smallest to largest order. Click OK.

**Download excel *.xlsx file**

Team ranking with criteria.xlsx

### Functions in this article

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

### 7 Responses to “Team ranking with criteria”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

1.0302

1.0310

13.1910

1.0101

13.1310

9.0902

1.0310

9.0902

1.0302

1.0102

13.1310

13.1917

13.1317

13.1310

13.1317

9.0902

1.0302

1.0310

9.0902

13.1317

Ab0ve r my total ranks,but the final rank i got with COUNTIF($AC$2:$AC$21,"<"&AC)+1 is 1 for all!!

Hasan,

I believe you have entered this formula as an array formula:

COUNTIF($AC$2:$AC$21,"<"&AC)+1 It is a regular formula, press enter only and then copy it down as far as needed.

Hi Oscar,

I tryed to calculate stanging just from results. For example: we have two teams A and B and we know results between them and want to calculate wins, draws, loses, points and goal difference.

A : B

2 : 1 = wins for A

2 : 2 = draw

2 : 3 = lose for A

1 : 0

3 : 1

1 : 4

Logic is simple, but I failed to find a way to calculate without helper columns

Oscar,

I really appreciate that you make the formula very simple to make ranking format.

But i'm little bit confused about the number you've choose as denominator

for Rank GD AND Rank GF . Can you please elaborate more for me.

And i'm also looking for answer for two more condition ;

if we consider " Concerned teams (Pnt, GF-GA, GF)" for 2nd step and

Coefficient rank for the 5th step.

then what would be the formula ?

If you may please help me out of this prob.

looking forward to your kind co-operation.

Oscar,

I really appreciate that you make the formula very simple to make ranking format.

But i'm little bit confused about the number you've choose as denominator

for Rank GD AND Rank GF . Can you please elaborate more for me.

And i'm also looking for answer for two more condition ;

if we consider " Concerned teams (Pnt, GF-GA, GF)" for 2nd step and

Coefficient rank for the 5th step.

then what would be the formula ?

If you may please help me out of this prob.

looking forward to your kind co-operation.

Thanks

Hi,

If you are going to use the sort function at the end to rank then why not use it on the whole table like:

Sort J by smallest to Largest

Add another Level

Sort G by Largest to smallest

And another level

Sort H by Largest to smallest

Add another Level

Sort I by Largest to smallest

Marq

Yes, you are right.