• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

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
0
oldrin
Asked:
oldrin
  • 2
1 Solution
 
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
 
barry houdiniCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now