Thread: 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!

Many Thanks to Aladin Akyurek for his solution and to Henrik for correctly asking his question!!!!!

Aladin's answer was just what I needed. All I had to do was change "<" to ">" to rank from high value to low value by category. He also corrected, as I see it, the formula put forth by Ola (?) in an earlier thread.

Again, thanks!