# Possible Rank Function and Sorting by 2 columns in a Scoring Ladder

#### cRx45

##### New Member
Hi All.

With the help of BrianMH I was able to get an issue sorted with my scoring spreadsheet in a previous thread.

Another issue has been brought to my attention now which I can't get my head around.

The file with dummy data can be found here. (Sheets are locked but have no password)

The issue is with sheet '3. Ladders'. If you look at the equal 1st and equal 5th teams, you'll notice that the 2 teams that had more 'wins' are underneath the teams with 'less' wins. So even though the first 2 are equal first on points, they need to be sorted by wins also (this would them make them 1st and 2nd, not equal 1st).

The same issue can bee seen with the players ladder where one of the equal 2nd players has 3 losses but is above a player that only has 1 loss (again resulting in that player now being 4th and the other 2 equal 2nd).

I'm using the Rank function (on sheet 2., columns A and O) to rank them by the points column (C) and wins column (O) respectively.

Does anyone have any ideas how to solve this without messing up any of BrianMH's fixes? Or do I just need to add more criteria to the Rank functions on sheet 2.???

Thanks

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
....Or do I just need to add more criteria to the Rank functions on sheet 2.?....

Yes, that's what I'd do....

Try this formula in sheet 2 cell A2 copied down

=IFERROR((RANK(C2,\$C\$2:\$C\$17)+COUNTIFS(\$C\$2:\$C\$17,C2,\$D\$2:\$D\$17,">"&D2)+COUNTIFS(\$C\$2:\$C2,\$C2,\$D\$2:\$D2,\$D2)-1),"")

and you can do a similar thing in column O except you would use < in place of > because you are dealing with losses rather than wins

=IFERROR((RANK(C2,\$C\$2:\$C\$17)+COUNTIFS(\$C\$2:\$C\$17,C2,\$D\$2:\$D\$17,">"&D2)+COUNTIFS(\$C\$2:\$C2,\$C2,\$D\$2:\$D2,\$D2)-1),"")

Thanks Barry! You are a legend.

That worked a charm.

Replies
1
Views
364
Replies
5
Views
1K
Replies
4
Views
349
Replies
0
Views
184
Replies
2
Views
273

1,196,235
Messages
6,014,146
Members
441,807
Latest member
sjkenjalo

### 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.

### Which adblocker are you using?

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