ranking within group

Hi,

I have the following groups and values

a 5
b 6
c 7
a 35
a 36
c 32
c 9
b 8

What I'd like to do is rank each value but within its own group (rank all the As together, B's together, and C's together).

any ideas?

I wish there was a rankif(), but no such luck.

thanks!

-joel

Assuming your data is in A1:B8 and you want to rank the highest number within each category as 1 then try this formula in C1 copied down

=SUMPRODUCT(($A$1:$A$8=A1)*($B$1:$B$8>B1))+1

Originally Posted by daddylonglegs

Assuming your data is in A1:B8 and you want to rank the highest number within each category as 1 then try this formula in C1 copied down

=SUMPRODUCT(($A$1:$A$8=A1)*($B$1:$B$8>B1))+1

this is awesome. Can you explain why this works? I understand how the basic sumproduct works, but I'm not understanding why it does the count when you have the =a1 and the >b1.

thanks for your help!

It's probably easiest to understand when you know that RANK function can be replicated by COUNTIF, so for a straight RANK of numbers in B1:B8 you can use this formula.

=COUNTIF(B$1:B$8,B1)+1

This works because the rank of a number is the same as the count of numbers within the range greater than that number.....plus 1

Now you want to add another condition so that you are only ranking for each specific category in column A. To add another condition you need to switch to SUMPRODUCT (or you can use COUNTIFS function in Excel 2007).

The SUMPRODUCT function gives a count of numbers greater than that to be ranked (>A1)....but also within the category (=B1) then adds 1 (otherwise the top rank would be zero).

Note that just like the RANK function, if two numbers are tied at 1 then there will be no rank 2, next rank will be 3.

thanks for the help and the explanation.

question, it appears as though there is a limit to how many rows you can use. I tried 1,000 and no luck.

any idea how to get around that? I'm looking at 26000+ records. :D

-joel