SUMPRODUCT using a condition

Hi,

I would like to create a formula in D7 that calculates the weighted average growth for products (column A) manufactured by Bob (column B). In J5 I’ve inserted the expected result using a SUMPRODUCT function.

Can someone please tell me how to create a formula in D7 that will calculate from the data in B6:D6 the same result as in J5. i.e. How do I make the SUMPRODUCT function only consider data in columns C and D when Bob is the manufacturer?

Thanks very much!

Directly, it's pretty simple.

=SUMPRODUCT(range1, range2, --(range3="Bob") )

range3 will return a boolean TRUE/FALSE for each element that's the same as text string "Bob" or not, and the --() forces it into a number 1/0 instead; so it multiplies rows where it is Bob by 1 and rows where it isn't by 0.

However, I don't quite understand what you'e trying to do with the weighting; are you trying to make it a function of just that person?

In that case I would probably create a dummy column for "Weight (%) (Individual Staff)" like this:
=C3/SUMPRODUCT($C$3:$C$6,--($B$3:$B$6=B3))

and then use that in the weighting range.