Count and Rank problem

[tonystowe]

Here is the run down:

Col A = Date - Self explanatory
Col B = Office - This column will list the same 10 offices multiple times as
reports indicate.
Col C = Report - This column will show the details reported on an specific
office.

In Col D I am using =COUNTIF($B$2:$B$500,"Sales") in order to count the number of instances that SALES is entered into Col B.

Problem

I want to use the RANK function to look at the cell Counting the number of instances of "SALES" and RANK it in comparison to the number of times the other offices have been listed.

** I wanted to add that I have in COL E the function =RANK(D2,$D$2:$D$1000,1) in an attempt to get the RANK part correct. The problem is that it lists the office least mentioned as #1 and the office with the most reports as being in last place. This isn't necessarily a major problem, however it would be nice to see the office with the most reporting showing up as the #1 problem area.

If its possible to combine these two functions into one cell that would be even better.

--------------------

Thanks before hand for anyone interested in helping me with this problem.

Tony

[Toppers]

Assuming D2 to D11 contains the counts (COUNTIF($B$2:$B$500,"<office>"), then
you could use:

=RANK(COUNTIF($B$2:$B$500,"sales"),D2:D11)

Or if D2 contains Sales count

=RANK(COUNTIF(D2,D2:D11)

HTH

"tonystowe" wrote:

>
> Here is the run down:
>
> Col A = Date - Self explanatory
> Col B = Office - This column will list the same 10 offices multiple
> times as
> reports indicate.
> Col C = Report - This column will show the details reported on an
> specific
> office.
>
> In Col D I am using =COUNTIF($B$2:$B$500,"Sales") in order to count the
> number of instances that SALES is entered into Col B.
>
> Problem
>
> I want to use the RANK function to look at the cell Counting the number
> of instances of "SALES" and RANK it in comparison to the number of times
> the other offices have been listed.
>
> If its possible to combine these two functions into one cell that would
> be even better.
>
> --------------------
>
> Thanks before hand for anyone interested in helping me with this
> problem.
>
> Tony
>
>
> --
> tonystowe
> ------------------------------------------------------------------------
> tonystowe's Profile: http://www.excelforum.com/member.php...o&userid=33162
> View this thread: http://www.excelforum.com/showthread...hreadid=530202
>
>

[tonystowe]

Assuming D2 to D11 contains the counts (COUNTIF($B$2:$B$500,"<office>"), then
you could use:

=RANK(COUNTIF($B$2:$B$500,"sales"),D2:D11)

Or if D2 contains Sales count

=RANK(COUNTIF(D2,D2:D11)

HTH

>[/color]

Thanks for your reply. I could not get the =Rank(countif(d2,d2:d11) to work as an error message indicating too few arguments pops up.

While =RANK(COUNTIF($B$2:$B$500,"sales"),D2:D11) did work with only "SALES" as its que, how do I make it rank all offices against one another? How do I account for ALL offices added and for the formula to rank each as the reports are added?

Thanks

Also, how do I make the RANK function rank the highest number FIRST and the lowest number LAST??

[Toppers]

Sorry ... complete aberation ..
should be

=rank(d2,d2:d11)

d2 will contain =COUNTIF($B$2:$B$500,"sales")
d3 will contain =COUNTIF($B$2:$B$500,"finance")

etc

Again, my apologies.

"tonystowe" wrote:

>
> Toppers Wrote:
> > Assuming D2 to D11 contains the counts (COUNTIF($B$2:$B$500,"<office>"),
> > then
> > you could use:
> >
> > =RANK(COUNTIF($B$2:$B$500,"sales"),D2:D11)
> >
> > Or if D2 contains Sales count
> >
> > =RANK(COUNTIF(D2,D2:D11)
> >
> > HTH
> >
> > >
>
> Thanks for your reply. I could not get the =Rank(countif(d2,d2:d11) to
> work as an error message indicating too few arguments pops up.
>
> While =RANK(COUNTIF($B$2:$B$500,"sales"),D2:D11) did work with only
> "SALES" as its que, how do I make it rank sales when I have other
> offices such as shipping, receiving, orders, etc. I do account for ALL
> offices and for the formula to rank each as the reports are added?
>
> Thanks
>
>
> --
> tonystowe
> ------------------------------------------------------------------------
> tonystowe's Profile: http://www.excelforum.com/member.php...o&userid=33162
> View this thread: http://www.excelforum.com/showthread...hreadid=530202
>
>[/color]

[tonystowe]

Thanks while I couldn't figure that out on my own it is beginning to make sense. Again, Thanks

Tony