Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count and Rank problem
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count and Rank problem
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count and Rank problem
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 [/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 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count and Rank problem
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] |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count and Rank problem
Thanks while I couldn't figure that out on my own it is beginning to make sense. Again, Thanks Tony -- tonystowe ------------------------------------------------------------------------ tonystowe's Profile: http://www.excelforum.com/member.php...o&userid=33162 View this thread: http://www.excelforum.com/showthread...hreadid=530202 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with formula please | Excel Worksheet Functions | |||
Ranking Problem | Excel Discussion (Misc queries) | |||
Best way to tackle this problem... | Excel Worksheet Functions | |||
Display the max, then the next down, then the next down, etc. | Excel Discussion (Misc queries) | |||
Stock Count. Adding to a pile | New Users to Excel |