Multiple ranking

[douglaswarden]

What I am trying to do is give the rank in column D based on the values in columns B and C. Some of the values in column B will have then same rank, and as such I want to add further criteria on which to rank them. I would first like to rank the values in column B and then rank the values in column C, which should give the rank in column D. For example Dog and Frog have the same value of 400 from the Non UK column. Therefore, rather than having these as both rank 1, I want them to be ranks 1 and 2, so want to add another criteria (UK). As Dog is greater than Frog in the UK (i.e. 10>7), I would like to rank Dog as 1 and Frog as 2. Goat will be ranked as 3 because it had the thrid highest value in the Non UK. I would be greatful for any suggestions.

Thanks

A B C D
1 Non UK UK Rank
2 Cat 200 5 5
3 Dog 400 10 1
4 Eel 200 11 4
5 Frog 400 7 2
6 Goat 300 2 3

[NBVC]

If you add a helper column,

e.g. in D2: =B2+C2 copied down

then in E2: =RANK(D2,$D$2:$D$6) copied down

Then hide column D.

[Domenic]

Try...

D2, copied down:

=RANK(B2,$B$2:$B$6)+SUMPRODUCT(--($B$2:$B$6=B2),--($C$2:$C$6>C2))

Hope this helps!

[douglaswarden]

Domenic - that is what I was looking for. Many thanks!!!

[Domenic]

You're very welcome!

[adeeshc]

how can this be extended to compar/rank 4 columns one after the other...that is rank first column, in case of ties look for second col, and if that too is tie a third col is considered...likewise