Array Formula

[SteveMarine]

I need to perform a SUMPRODUCT on columns B and C based on the result
of a lookup in column A. I thought an array formula lik
{SUMPRODUCT(IF(a1:a100="yes",b1:b100,c1:c100))} would work, but n
luck. Anyone know what I'm doing wrong or a better way to approach it
Thank

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(A1:A100="Yes")*(B1:C100))

just enter this with 'ENTER'

 

[Bernard V Liengme]

Hi Steve,
Try this =SUMPRODUCT(--(A1:A100="yes"),B1:B100,C1:C100)
Note this is NOT an array formula
The double negation coerces the Boolean values (FALSE & TRUE) to numeric (0
&1)
Best wishes
Bernard