SumProduct Formula

  • Thread starter Thread starter Phil Hageman
  • Start date Start date
P

Phil Hageman

The following formula works fine when all "B" cells have a
value >0. When a "B" cell is zero, the formula returns
#Value!. Is it possible to modify this formula so that it
ignores the zero value and sums the other "B" products?

=(SUM(PRODUCT(BI35,BI37),PRODUCT(BI38,BI40),PRODUCT
(BI41,BI43)))*AR44

Thanks,
Phil
 
I couldn't reproduce your results unless either:

- The column BI values had #VALUE! errors or

- AR44 had text (which could be a space character)
 
Hi Phil,
I cannot reproduce this problem. The formula works with zeros for me.

I have never used PRODUCT before but did some experiments. With 1,2,3,4 in
B1:B4, the formula PRODUCT(B1:B4) correctly returns 24 (the same as
B1*B2*B3*B4). But when B3 is deleted PRODUCT gives 8 while the * operator
gives 0. When text is entered in B3, PRODUCT ignores it while the operator
formula give #VALUE!
The description of PRODUCT in Help seems wide of the mark!
Bernard
 
Back
Top