conditional ranking

I am using sumproduct function for conditional ranking.  

But the ranking is not in sequence when there is a repetition of same amounts.

Please find enclosed sample.

Could you please fine tune the formula so that the ranking by the function is matching with the ranking in the example column ?

Thanks in Advance.
sample-for-conditional-ranking.xlsx
oldrinAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
OK, yes, this version refers to 100 rows but should still work if you have data in fewer than 100

=IF(A2="","",SUMPRODUCT(((B$2:B$100<B2)*(A$2:A$100=A2))/COUNTIFS(A$2:A$100,A$2:A$100&"",B$2:B$100,B$2:B$100&""))+1)

regards, barry
0
 
barry houdiniCommented:
Try this formula in B2 copied down

=SUMPRODUCT(((B$2:B$73<B2)*(A$2:A$73=A2))/COUNTIFS(A$2:A$73,A$2:A$73,B$2:B$73,B$2:B$73))+1

regards, barry
0
 
oldrinAuthor Commented:
Thanks Barry,

Your solution is working and results are as per example.

When I increase the range to above 73 and if the cells are blank then an error is resulted.

Could you please help to resolve this ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.