Rankif formula for multiple columns of data

[Obewan]

I have the following:
AY = a list of codes (i.e. unique identifiers)
BM:BR - columns of data

In BS I have a formula as follows which I can copy down that column (starts on row 7):
=COUNTIFS($AY$7:$AY$100000,$AY7,$BN$7:$BN$100000,">"&BN7)+1

This works and ranks all numbers in column BN according to correpsonding code in AY

What I want to do is extend this formula so it covers all data in columns BN:BR for the code - I changed the formula to
=COUNTIFS($AY$7:$AY$100000,$AY7,$BN$7:$BR$100000,">"&BN7)+1

but just get an error message #VALUE!

Any suggestions?

Thanks in advance

Seamus

 

[ravi2628]

You are getting the right answers but I am not - it still appears to be ranking all the blank cells as 1 then the numbers tart getting ranked thereafter - this is weird as it's working for you?

but as per your previous post, there is no ranking value visible in the data

 

[ravi2628]

Is there a way it could count all the blank cells and then we deduct them from the ranks?

I am Attaching the file link for your reference please check.

Rank.xlsx

Shared with Dropbox

www.dropbox.com

 

[Obewan]

Excellent. Thanks for your help - got it sorted!!