Ranking with Tiebreakers

New Member
Need some help ranking, this is just a small sample. I found a formula on here to rank the teams based on columns D & F, but can't figure out how to use L as a tiebreaker instead of order in the spreadsheet. Obviously this is just a small sample of the sheet there are more columns so the formula won't match up completely. I labeled the columns as I have them in my sheet. I hope I did this right.

Code:
``=COUNTIFS(D:D,D2,F:F,">"&F2)+COUNTIFS(D\$2:D2,D2,F\$2:F2,F2)``

 Column D Column F Column L Class PR SF Desired Rank 1A 12 2 1 1A 10 3 2 1A 10 2 3 2A 14 4 1 2A 12 3 2 2A 12 3 3 3A 12 5 1 3A 10 2 2 3A 10 2 3

<tbody>
</tbody>

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Peter_SSs

MrExcel MVP, Moderator
I don't fully understand the requirement.
You said you wanted to use column L as the tiebreaker instead of the order in the sheet. I can see that in the green rows but in the two amber sections you have not used column L to break the ties, you have used something else - perhaps the order in the sheet that you said you did not want to use..

Excel Workbook
DFLM
1ClassPRSFDesired Rank
21A1221
31A1032
41A1023
52A1441
62A1232
72A1233
83A1251
93A1022
103A1023
Rank in Groups

Last edited:

New Member

You are right, it appears that I am using order in the sheet as a tiebreaker still, and technically I am. But as a third tiebreaker, I want L to be the 2nd tiebreaker. This is for football, and once we get 3-4 weeks into the season, these numbers will separate and the need for the third tiebreaker will almost always go away.

Peter_SSs

MrExcel MVP, Moderator
In that case, try this.

Excel Workbook
DFLM
1ClassPRSFRank
21A1221
31A1032
41A1023
52A1441
62A1232
72A1233
83A1251
93A1022
103A1023
Rank in Groups

New Member
Thanks for your help. I had to have this done yesterday so I spent quite a bit of time searching more posts on here and came across one that worked. I created a helper column and added column L (times .0001) to column F, and then ran my original formula on the new column instead of F. It appears to work like a charm. In one instance I had 15 ties on column F, and it sorted them all properly.

I will give yours a try when I get a chance and see how it does.

Replies
3
Views
73
Replies
4
Views
82
Replies
9
Views
112
Replies
2
Views
69
Replies
1
Views
169

1,126,945
Messages
5,621,763
Members
415,854
Latest member
Tutu123

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.

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

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