Adding from several columns if ...

[TV]

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

 

[T. Valko]

Try this:

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

Note how the range references are offset.