Can anyone show me a formula to sum a product of two arrays only when there
is a match. Suppose I want to add up the product of A and C for Apple
(answer 3%) and Orange (answer 3.5%)

eg.
A B C
1% Apple 100%
2% Orange 50%
3% Pear 25%
4% Apple 50%
5% Orange 50%

=SUMPRODUCT(--($B$1:$B$5="Apple"),$A$1:$A$5,$C$1:$C$5)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"rl" wrote in message
...
Can anyone show me a formula to sum a product of two arrays only when
there
is a match. Suppose I want to add up the product of A and C for Apple
(answer 3%) and Orange (answer 3.5%)

eg.
A B C
1% Apple 100%
2% Orange 50%
3% Pear 25%
4% Apple 50%
5% Orange 50%

Assuming your data is in rows 1:5 then
=SUMPRODUCT((B1:B5="apple")*A1:A5*C1:C5) produces 3% and
=SUMPRODUCT((B1:B5="orange")*A1:A5*C1:C5) produces 3.5% in cells formatted
as percents with 1 place of decimal. Also
=SUMPRODUCT((B1:B5={"apple","orange"})*A1:A5*C1:C5 ) produces 6.5% in a cell
formatted as percent with 1 place of decimal. You might want to increase the
decimal places to 2.

Tyro

"rl" wrote in message
...
Can anyone show me a formula to sum a product of two arrays only when
there
is a match. Suppose I want to add up the product of A and C for Apple
(answer 3%) and Orange (answer 3.5%)

eg.
A B C
1% Apple 100%
2% Orange 50%
3% Pear 25%
4% Apple 50%
5% Orange 50%