Rank() by criteria 1

[Cage]

I have the following data:

Client Type Sales
A Red 12
B Blue 17
C Blue 3
D Green 43
E Blue 27
F Red 6
G Green 30

I would like to add a rank column which ranks the Type (red, blue, green) depending on Sales for each client.

E.g

Client Type Sales Rank by Type
A Red 12 1
B Blue 17 2
C Blue 3 3
D Green 43 1
E Blue 27 1
F Red 6 2
G Green 30 2

Does anyone know how to rank like this?

 

[GlennUK]

An array formula will do what you want ( entered using Ctrl-Shift-Enter instead of Enter ). Try this in cell D2:

=SUM(IF($B$2:$B$8=B2,IF($C$2:$C$8>C2,1,0),0))+1

entered using Ctrl-Shift-Enter, and copy down.

Entering a array formula using Ctrl-Shift-Enter will show the formula surrounded by curly brackets. This is just Excel's way of showing you that the cell contains an array formula.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[bluedragon2]

or sorting on type then number will rank your data

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...