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

 

[jamescox]

Seems like the =SUMIF() worksheet function would do what you need -
check the help on it and see...

 

[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.)

 

[jamescox]

Excel itself will walk you through that.

Click on the fx symbol in the little area just to the left of the
formula bar. This opens an "Insert Function" window. In the 'search
for a function' box, type SUMIF, then click the Go button.

This highlights the SUMIF entry in the box below. Click on the OK
button and you will get a window that guides you through setting up the
formula, or you could click the Help on this function hyperlink/hotspot
to get help with more information on how to use SUMIF.

It's always good to check with this built-in guide to functions when
you are trying to do something...

 

[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?

 

[jamescox]

Did you read the help on SUMIF?

What's the part that you don't understand and what simple worksheet
tests did you make to see if you could figure it out?

(No offense intended, but I'm shooting for you to have a learning
experience here - if you just want me to give you the answer, you're out
of luck.)

 

[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

 

[jamescox]

General rule of development - make a simple example work, THEN integrate
it into your real workbook. Therefore, in a blanl worksheet create the
following:

1. In cell A4, type Cities and in A5 through A11, type Boston, Denver,
Los Angeles, Denver, New York, Denver, New York.

2. In cell B4 type Reps and in B5 through B11 type 1,2,3,5,4,1,2

3. In Cell E5 type Cities and in E5 through E8 type Boston, Denver,
Los Angeles, New York.

4. In cellF5 type the following formula:

=SUMIF($A$5:$A$11,E5,$B$5:$B$11)

5. Copy that formula down into F6 through F8.

Compare this working example with the help file writeup and see if you
can figure out where you went astray and what you need to change in your
workbook to get it working...

Happy 4th!

 

[Gary]

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