SumProduct Formula

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
 
J

J.E. McGimpsey

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)
 
B

Bernard V Liengme

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
 
M

Mo

Are you sure it is 0 and not "O" then?
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
Click to expand...
 
Top