sumproduct Column B based on Column A

[Gary]

I have columns A & B and want to Sum the entries in Column B based on the
City in Column A as follows. I'm trying to Sum the number of Sales Reps in
each City:
City # Reps
Col A Col B
City 1 1
City 1 3
City 2 4
City 3 1
City 3 1
City 3 6

So I need to know how many Sales Reps are in City 1, City 2 and City 3, etc,
being able to use any US City name, ie, not "City 1", City 2", rather, say,
San Francisco, Las Vegas, Denver, etc

 

[Don Guillett]

Actually, SUMIF should work for the data given.

 

[Gary]

Thanks everyone. Could you give me an example of the SumIf formula? (Also, I
didn't realize anyone answered my question so I posted it again with another
example.)

 

[Gary]

Thanks! I know how to do formulas, I'm just not sure how to do this
particular formula. Could you give me an example?

 

[Gary]

Thank you teacher! I'll take your advice!

 

[Gary]

Here's my formula:
=SUMIF('Rep List by City'!$B$5:'Rep List by City'!$B$1004,B16,'Rep List by
City'!$C$5:'Rep List by City'!$C$1004)

Here's my columns:

Formula Results
'Rep list by City' 'Rep List by City' 'Master City List'
'Master City List'
Column B Column C Column B
Column C
City Reps City
Number of reps
Boston 1 Albany
0
Denver 2 Augusta
0
Denver 5 Boston
1
Denver 1 Buffalo
0
Los Angeles 3 Denver
0
New York 4 Los Angeles
3
New York
4
etc ...

Notice that Column B 'Master City List' has no correlation to 'Rep List by
City' Column B. Also, 'Master City List' is on another worksheet in the same
workbook as 'Rep List by City'. I want the number of Reps for each city from
the 'Rep List by City' worksheet listed adjacent to each city in the 'Master
City List' worksheet.

The above formula works for each city on the 'Master City List' except for
cities like Denver which is listed multiple times on the 'Rep List by City'
worksheet. Any time I have a city listed more than once, the formula
generates a 0 for that city on the 'Master City List'. The main purpose of
the formula is to add the reps in a city on "Reps List by City' like Denver
and produce a sum for Denver on the 'Master City List"

What do I need to change?

Gary

 

[Gary]

Thanks James! I actually figured it out yesterday. Made one of those silly
little mistakes in a formula. Works like a charm now!