Adding from several columns if ...
 

I have 10 Account Managers that have 8-12 Sales Reps assigned to them. Each
sales rep has estimated their products to sell for 2009. Each AM has
summarized these estimates for their respective districts. I have a
spreadsheet that looks something like:

AM1 AM2 AM3
Product Estimate Product Estimate Product Estimate
Product1 2000 Product2 500 Product1 2000
Product2 1000 Product3 750 Product3 2500

All products are not sold by each rep in each area. How do I summarize
products for 10 of AM's where the function would look down the range of AM1,
find product1, AM2 and find product1, etc. and then add them all up. Then do
the same in the next cell for product2, then product3, etc.? I have used
VLOOKUP extensively before but do I need to define a range for each AM and
then add up 10 VLOOKUP's? Is there an easier way to find all occurances and
sum them?

Thanks,

Todd

Adding from several columns if ...
 

Try this:

=SUMIF(A2:E3,"Product1",B2:F3)

Note how the range references are offset.


--
Biff
Microsoft Excel MVP


"TV" wrote in message
...
I have 10 Account Managers that have 8-12 Sales Reps assigned to them.
Each
sales rep has estimated their products to sell for 2009. Each AM has
summarized these estimates for their respective districts. I have a
spreadsheet that looks something like:

AM1 AM2 AM3
Product Estimate Product Estimate Product Estimate
Product1 2000 Product2 500 Product1
2000
Product2 1000 Product3 750 Product3
2500

All products are not sold by each rep in each area. How do I summarize
products for 10 of AM's where the function would look down the range of
AM1,
find product1, AM2 and find product1, etc. and then add them all up. Then
do
the same in the next cell for product2, then product3, etc.? I have used
VLOOKUP extensively before but do I need to define a range for each AM and
then add up 10 VLOOKUP's? Is there an easier way to find all occurances
and
sum them?

Thanks,

Todd