rank subsets of data

[rossg]

hi i would like a formula which ranks all item 1 against its self, would anyone be able to help?

sales units rank
ITEM 1 425 2
item 2 45 3
item 2 80 2
item 2 81 1
ITEM 1 467 1
ITEM 1 51 4
ITEM 1 389 3

[Tony Valko]

Try this...

Data Range

	A	B	C
1	Sales	Units	Rank
2	ITEM 1	425	2
3	item 2	45	3
4	item 2	80	2
5	item 2	81	1
6	ITEM 1	467	1
7	ITEM 1	51	4
8	ITEM 1	389	3

This formula entered in C2 and copied down:

=SUMPRODUCT(--(A$2:A$8=A2),--(B2<B$2:B$8))+1