Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Column B based on Column A
I'm trying to count the number of sales reps in each city. I have two
columns. Column A is the City. Column B is the number of sales reps in a city. Sometimes a city is listed more than once and each city can have multiple sales reps as follows: Col A Col B City Reps City 1 3 City 1 1 City 2 4 City 3 1 City 3 6 I want to add the number of reps in each city, being able to use any city name, rather than "City 1", "City 2", etc. In other words, I may have 50 cities and need to count the reps in all the cities. As the sales force grows, I am constantly adding new cities, so I need the flexibility to count reps in an unlimited number of cities. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Column B based on Column A
Gary;400448 Wrote: I'm trying to count the number of sales reps in each city. I have two columns. Column A is the City. Column B is the number of sales reps in a city. Sometimes a city is listed more than once and each city can have multiple sales reps as follows: Col A Col B City Reps City 1 3 City 1 1 City 2 4 City 3 1 City 3 6 I want to add the number of reps in each city, being able to use any city name, rather than "City 1", "City 2", etc. In other words, I may have 50 cities and need to count the reps in all the cities. As the sales force grows, I am constantly adding new cities, so I need the flexibility to count reps in an unlimited number of cities. Hi, have you tried a Pivot Table ? Cheers -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111738 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Column B based on Column A
Hi,
Enter a list of all the unique cities starting in cell D1 and then use =COUNTIF(B$1:B$100,D1) Copy down as far as needed. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Gary" wrote: I'm trying to count the number of sales reps in each city. I have two columns. Column A is the City. Column B is the number of sales reps in a city. Sometimes a city is listed more than once and each city can have multiple sales reps as follows: Col A Col B City Reps City 1 3 City 1 1 City 2 4 City 3 1 City 3 6 I want to add the number of reps in each city, being able to use any city name, rather than "City 1", "City 2", etc. In other words, I may have 50 cities and need to count the reps in all the cities. As the sales force grows, I am constantly adding new cities, so I need the flexibility to count reps in an unlimited number of cities. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Column B based on Column A
Thanks Shane!
Is there a way to automatically add a new city to column D when I add a new city in column A? If I do this, my formula would have to ignore blank cells (I think) as I would leave the entire Column D open in the formula for new cities and don't want to count blank cells. Also, if possible, I would prefer not to introduce an new Column D, as you suggest, if there is an easier way to accomplish my task. Any ideas? Gary "Shane Devenshire" wrote: Hi, Enter a list of all the unique cities starting in cell D1 and then use =COUNTIF(B$1:B$100,D1) Copy down as far as needed. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Gary" wrote: I'm trying to count the number of sales reps in each city. I have two columns. Column A is the City. Column B is the number of sales reps in a city. Sometimes a city is listed more than once and each city can have multiple sales reps as follows: Col A Col B City Reps City 1 3 City 1 1 City 2 4 City 3 1 City 3 6 I want to add the number of reps in each city, being able to use any city name, rather than "City 1", "City 2", etc. In other words, I may have 50 cities and need to count the reps in all the cities. As the sales force grows, I am constantly adding new cities, so I need the flexibility to count reps in an unlimited number of cities. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Column B based on Column A
I have not tried a pivot table. I've briefly read about tpivot tables. Could
you give me an example of how one might work in my situation, ie, how I would quickly identify the number of reps in a particular city. "Pecoflyer" wrote: Gary;400448 Wrote: I'm trying to count the number of sales reps in each city. I have two columns. Column A is the City. Column B is the number of sales reps in a city. Sometimes a city is listed more than once and each city can have multiple sales reps as follows: Col A Col B City Reps City 1 3 City 1 1 City 2 4 City 3 1 City 3 6 I want to add the number of reps in each city, being able to use any city name, rather than "City 1", "City 2", etc. In other words, I may have 50 cities and need to count the reps in all the cities. As the sales force grows, I am constantly adding new cities, so I need the flexibility to count reps in an unlimited number of cities. Hi, have you tried a Pivot Table ? Cheers -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111738 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct Column B based on Column A | Excel Discussion (Misc queries) | |||
Count Unique Values in 1 Column based on Date Range in another Column | Excel Worksheet Functions | |||
Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
sumproduct 2 columns based on criteria in 3rd column | Excel Discussion (Misc queries) |