[Solved] Help creating a rank by, then formula

[darren.stone65]

Hello everyone I have tried in vain to solve this on my own without luck, here's hoping one of you clever people can help!
I have attached my sample for you to look at. thanks in advance.

Name	Score	back 6	back 3	back 1	Rank	Rank should be
John	26	13	5	1	6	6
Howard	26	11	6	0	5	7
Kevin	26	14	5	3	2	5
Declan	26	14	8	4	2	2
Darren	27	12	5	2	1	1
Jim	26	14	8	3	2	3
Fionn	26	14	8	1	2	4
How do I rank the above table, firstly by Score, then if tied by back 6, then if tied by back 3 then if tied by back 1. I have used Rankeq and countifs but can only make it work on score then back 6. the ranking should be as in column G

 

[shamimarita]

Hello Darren,

You can rank your table by Score, then Back 6, then Back 3, then Back 1 (all as tiebreakers, in order) using a single formula with SUMPRODUCT.

Formula:
=1
+SUMPRODUCT(($B$2:$B$8>B3)
+($B$2:$B$8=B3)*($C$2:$C$8>C3)
+($B$2:$B$8=B3)*($C$2:$C$8=C3)*($D$2:$D$8>D3)
+($B$2:$B$8=B3)*($C$2:$C$8=C3)*($D$2:$D$8=D3)*($E$2:$E$8>E3))

• The formula compares each row’s Score, then breaks ties with Back 6, then Back 3, then Back 1, just as you wanted.
• It counts how many rows are ranked ahead of the current row, then adds 1 to give the final rank.

 

[darren.stone65]

Amazing! Shamima, I can now see why it was a little to testing for me wonderfully solved Thank You

 

[shamimarita]

You are most welcome. Glad to hear the the formula solved your problem.
Keep helping each other.