Rank within a category

[Caezar]

Dear Excel gurus,

1) I have been trying to rank customers by amount of sales. Each
customer belongs to a region and its rank is relative to its peers from
the same region, not the whole list of clients. While this problem is
simple, I have yet to find an elegant solution.

Region Customer Sales Rank
West ABC 100 1
West DEF 80 3
West GHI 85 2
East QWE 20 3
East ERT 40 1
East TYU 30 2

I wanted to use the Rank() function, but it ranks a customer within the
whole population. Is there a formula that would calculate the ranking
for a customer whithin its region? Or is there a way to use a pivot
table for this purpose?

2) In my dataset, the sales amount depends on another variable. A
change of this variable impacts the sales to each customer and the rank
of the customer within a region. I would like to create a pivot table
based on data such as above (outer row field = region, inner row field
= customer, data field = sales), and use some VBA code to automatically
sort the customers by sales every time the pivot table is refreshed.

Thank you.

Caezar

 

[Bernie Deitrick]

1) For your example table, enter this is cell D2

=1+SUMPRODUCT(($A$2:$A$7=A2)*($C$2:$C$7>C2))

and copy down to match.

2) Set your Pivot Table to sort based on Sales. Right click on Customer, choose Field Settings,
click on Advanced, choose Sort Descending and base it on Sales.

HTH,
Bernie
MS Excel MVP

 

[Caezar]

Thank you very much Bernie. I tried it and it works. But how comes this
formula works without entering it as an array formula? I am puzzled.

 

[Bernie Deitrick]

Caezar,

You're welcome.

The formula works as a general formula rather than an array formula because SUMPRODUCT basically
works as an array-wrapper - multiplying the arrays that are passed to it and summing them up.

HTH,
Bernie
MS Excel MVP