Rank() based on category
 

Is it possible to rank a value within its respective category? For instance,
if I have a dataset that is structured similar to the one below, I want to a
formula that returns each oberservation's rank within its respective category.

1 a
4 a
5 a
6 b
7 b
7 b
7 a
3 a


The first observation would be ranked 1 within category a
The fourth observation would be ranked 1 within category b
The last oberservation would be ranked 2 within category a
and so forth....

I think that I somehow need to use an array function (ctrl + shift + enter),
but I haven't had any success using rank() submitted as an array!

Hope my question makes sense. Thank, in advance, for your help!

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

where A2:A9 houses the sample figures and B2:B9 categories.

Henrik wrote:
Is it possible to rank a value within its respective category? For instance,
if I have a dataset that is structured similar to the one below, I want to a
formula that returns each oberservation's rank within its respective category.

1 a
4 a
5 a
6 b
7 b
7 b
7 a
3 a


The first observation would be ranked 1 within category a
The fourth observation would be ranked 1 within category b
The last oberservation would be ranked 2 within category a
and so forth....

I think that I somehow need to use an array function (ctrl + shift + enter),
but I haven't had any success using rank() submitted as an array!

Hope my question makes sense. Thank, in advance, for your help!