Sumproduct Column B based on Column A

[Gary]

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.

 

[Shane Devenshire]

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.

 

[Gary]

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

 

[Gary]

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.