CODEWITHSUNDEEP
excelvba
Siraj Samsudeen
Siraj Samsudeen

Reputation: 1694

How to rank within category in Excel?

I have a list of customers by region with sales value. I want to create an overall ranking of all customers by sales values as well as the ranking of customers by sales value within the region and use both the ranking to calculate a score. is there a way to do this in Excel?

Either formulae or VBA code would be helpful for me.

Upvotes: 4

Views: 17733

Answers (1)

barry houdini
barry houdini

Reputation: 46341

Clearly for overall rank you can use RANK function, e.g. with Customers in A2:A100, Regions in B2:B100 and Sales value in C2:C100 you can use this formula in D2 for overall customer rank by sales value (highest ranks 1)

=RANK(C2,C$2:C$100)

for RANK within region you can use this version in E2 copied down

=SUMPRODUCT((B$2:B$100=B2)*(C$2:C$100>C2))+1

Upvotes: 12

Related Questions