Ranking Excel data within a group using 2 columns

[delvin]

Hi All,

I am looking to get a rank column as shown below using some rank functions.

We have to group all same locations and rank within the group.

Then, the lowest sales should get the first rank. If the sales are same, the rank should be given based on the second Column Amount.

For example, Sydney have sales as 1000 in two cases. So we checked Amount and given rank 2 to the entry that is having higher amount.

Basically, Sales in ascending and amount in descending order within a group(Same Location). you help is highly appreciated.

Location	Sales	Amount	Rank
Sydney	850	10000	1
Sydney	1000	12000	2
Sydney	1000	11000	3
Canada	5000	50000	2
Canada	4500	45000	1
Canada	5000	35000	3

<tbody>
</tbody>

 

[Eric W]

Welcome to the forum.

Try:

Excel 2012

	A	B	C	D
1	Location	Sales	Amount	Rank
2	Sydney	850	10000	1
3	Sydney	1000	12000	2
4	Sydney	1000	11000	3
5	Canada	5000	50000	2
6	Canada	4500	45000	1
7	Canada	5000	35000	3

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1
​

Worksheet Formulas Cell Formula D2 =COUNTIFS(A:A,A2,B:B,"<"&B2)+COUNTIFS(A:A,A2,B:B,B2,C:C,">"&C2)+1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[delvin]

Hi Eric,

Perfect!! That's exactly what I was looking for.

Thanks a lot for your help.

-D

 

[Eric W]

Glad to help!