How to rank within category in Excel?

Question

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.

Solution

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