Is there a rankif function

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.

Dan,

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

and copy down.

HTH,
Bernie
MS Excel MVP


"Dan" <[email protected]> wrote in message
news:[email protected]...
> 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.

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




"Bernie Deitrick" wrote:

> Dan,
>
> =1+SUMPRODUCT(($A$2:$A$5=A2)*($B$2:$B$5>B2))
>
> and copy down.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Dan" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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

"Bernie Deitrick" wrote:

> Dan,
>
> =1+SUMPRODUCT(($A$2:$A$5=A2)*($B$2:$B$5>B2))
>
> and copy down.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Dan" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>