Rank within an unsorted list

[liybpg]

I have a large set of data. Entries are put into categories (e.g. 1 and 2 in table below) and what I am looking to do is to rank entries according to their sales within the category.

An illustrative example below, but my dataset is quite a bit more complicated not additional sorting etc can be done.

Thanks for your help!

	A	B	C
1	Category	Sales	Rank
2	1	105,000
3	1	489,391
4	1	419,478
5	2	254,024
6	2	190,338
7	2	11,836
8	1	838,957
9	1	254,024
10	2	111,964
11	2	7,500
12	2	838,957
13	1	559,304
14	1	635,060
15	1	307,069
16	2	36,848
17	2	49,184
18	1	1,398,261
19	1	444,542
20	2	447,855
21	2	33,589
22	1	3,076,175

<tbody>
</tbody>

 

[Andrew Poulsom]

In C2 copied down:

=COUNTIFS(A$2:A$22,A2,B$2:B$22,">="&B2)

 

[MARK858]

Does the below give the results expected?

Excel Workbook
	A	B	C
1	Category	Sales	Rank
2	1	105,000	11
3	1	489,391	6
4	1	419,478	8
5	2	254,024	3
6	2	190,338	4
7	2	11,836	9
8	1	838,957	3
9	1	254,024	10
10	2	111,964	5
11	2	7,500	10
12	2	838,957	1
13	1	559,304	5
14	1	635,060	4
15	1	307,069	9
16	2	36,848	7
17	2	49,184	6
18	1	1,398,261	2
19	1	444,542	7
20	2	447,855	2
21	2	33,589	8
22	1	3,076,175	1
Sheet8

Edit: slower than Andrew and a worse solution (for 2007+). Will leave it up just as an poor example and in case anyone is using an older version

 

[liybpg]

Andrew - ohh this is clever, didn't think about it like that. Thank you very much

 

[liybpg]

Mark, this one works very well as well. Thank you for your help

 

[liybpg]

Sorry to come back to this, but is there any way to have the rankings not skip ranks, if there are repeated values (didn't come up in the table above)

For example, the ranks would go 1, 2, 2, then 4, 5, 6 etc if second and third values are repeated
I want them to go 1, 2, 2, 3, 4, 5 instead

Thank you

 

[Andrew Poulsom]

How about?

=SUMPRODUCT(((A$2:A$22=A2)*(B$2:B$22>B2))/COUNTIFS(A$2:A$22,A$2:A$22,B$2:B$22,B$2:B$22))+1

 

[liybpg]

Andrew, it does seem to work, thank you very much for that. It takes very long time to calculate though, I have about 30,000 rows so hopefully it would work for that purpose.