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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Sales[/TD]
[TD]Amount[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD]850[/TD]
[TD]10000[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD]1000[/TD]
[TD]12000[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD]1000[/TD]
[TD]11000[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Canada[/TD]
[TD]5000[/TD]
[TD]50000[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Canada[/TD]
[TD]4500[/TD]
[TD]45000[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Canada[/TD]
[TD]5000[/TD]
[TD]35000[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the forum.

Try:

Excel 2012

	A	B	C	D
Location	Sales	Amount	Rank
Sydney
Sydney
Sydney
Canada
Canada
Canada

<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>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]850[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]12000[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]11000[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]4500[/TD]
[TD="align: right"]45000[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet1
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: <a href="]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: <a href="]D2
[/TH]
[TD="align: left"]=COUNTIFS(A:A,A2,B:B,"<"&B2)+COUNTIFS(A:A,A2,B:B,B2,C:C,">"&C2)+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[delvin]

Hi Eric,

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

Thanks a lot for your help.

-D

 

[Eric W]

Glad to help!