Is there a rankif function

D

Dan

I want to rank a column only if another column value is equal to something.
Does anyone know a workaround for this, I know there is no rankif.

Example below:
column 1 column 2
h 2
h 3
a 1
a 5

I want a formula to tell me the rank of column 2 depending on column 1
being true.
 
B

Bernie Deitrick

Dan,

=1+SUMPRODUCT(($A$2:$A$5=A2)*($B$2:$B$5>B2))

and copy down.

HTH,
Bernie
MS Excel MVP
 
D

Dan

It does not seem to work with the following data set... Or does it?

acet 89
acet 76
acet 42
acet 62
pro 82
pro 81
pro 54
pro 4
oxy 73
oxy 94
oxy 52
 
D

Dan

Gotit!!! Thanks Bernie, I use the sumproduct to get multiple sumifs, but
this is cool, I did not think of using it this way.
 
Top