Rank based on criteria

How can I get excel to not only do a ranking using the sales of all the
employees, but also a rank by region as well? The real spreadsheet is much
larger and would take a lot of time to rank by hand. I would like to have a
formula do this, not sorting the list and then filling down. Thanks.

Steve

Hi Steve

I would head column E as Regions, with North, East, South, West in cells
E2:E5
In cell F2 enter
=SUMPRODUCT(--($C$2:$C$13=E2),$B$2:$B$13) and copy down through cells F3:F5
In cell G2 enter
=RANK(F2,$F$2:$F$5) and copy down through G3:G5


--
Regards

Roger Govier


"Steve DeBruin" <[email protected]> wrote in message
news:[email protected]...
> How can I get excel to not only do a ranking using the sales of all the
> employees, but also a rank by region as well? The real spreadsheet is
> much
> larger and would take a lot of time to rank by hand. I would like to have
> a
> formula do this, not sorting the list and then filling down. Thanks.
>
> Steve
>
>
>

Steve,

Try this

=MATCH(B2,LARGE(IF($C$2:$C$13=C2,$B$2:$B$13),ROW(INDIRECT("1:"&COUNTIF($C$2:
$C$13,C2)))),0)

it is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steve DeBruin" <[email protected]> wrote in message
news:[email protected]...
> How can I get excel to not only do a ranking using the sales of all the
> employees, but also a rank by region as well? The real spreadsheet is
much
> larger and would take a lot of time to rank by hand. I would like to have
a
> formula do this, not sorting the list and then filling down. Thanks.
>
> Steve
>
>
>

That worked great Bob, thanks a lot. One more question. How would you do a
reverse rank? So that the lowest sales amount would be ranked #1 and so on.


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Steve,
>
> Try this
>
>
=MATCH(B2,LARGE(IF($C$2:$C$13=C2,$B$2:$B$13),ROW(INDIRECT("1:"&COUNTIF($C$2:
> $C$13,C2)))),0)
>
> it is an array formula, so commit with Ctrl-Shift-Enter
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Steve DeBruin" <[email protected]> wrote in message
> news:[email protected]...
> > How can I get excel to not only do a ranking using the sales of all the
> > employees, but also a rank by region as well? The real spreadsheet is
> much
> > larger and would take a lot of time to rank by hand. I would like to
have
> a
> > formula do this, not sorting the list and then filling down. Thanks.
> >
> > Steve
> >
> >
> >
>
>

What? Steve DeBruin is Steved? No greetings from Steved? :-(

You're going to kick yourself man, it is so obvious (when you see it that
is)

=MATCH(B2,SMALL(IF($C$2:$C$13=C2,$B$2:$B$13),ROW(INDIRECT("1:"&COUNTIF($C$2:
$C$13,C2)))),0)

again an array formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steve D" <[email protected]> wrote in message
news:[email protected]...
> That worked great Bob, thanks a lot. One more question. How would you do
a
> reverse rank? So that the lowest sales amount would be ranked #1 and so
on.
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Steve,
> >
> > Try this
> >
> >
>
=MATCH(B2,LARGE(IF($C$2:$C$13=C2,$B$2:$B$13),ROW(INDIRECT("1:"&COUNTIF($C$2:
> > $C$13,C2)))),0)
> >
> > it is an array formula, so commit with Ctrl-Shift-Enter
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Steve DeBruin" <[email protected]> wrote in message
> > news:[email protected]...
> > > How can I get excel to not only do a ranking using the sales of all
the
> > > employees, but also a rank by region as well? The real spreadsheet is
> > much
> > > larger and would take a lot of time to rank by hand. I would like to
> have
> > a
> > > formula do this, not sorting the list and then filling down. Thanks.
> > >
> > > Steve
> > >
> > >
> > >
> >
> >
>
>