SumProduct Formula
 

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),PRODUC T
(BI41,BI43)))*AR44

Thanks,
Phil

SumProduct Formula
 

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)


In article ,
"Phil Hageman" wrote:

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),PRODUC T
(BI41,BI43)))*AR44

Thanks,
Phil

SumProduct Formula
 

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

"Phil Hageman" wrote in message
...
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),PRODUC T
(BI41,BI43)))*AR44

Thanks,
Phil

SumProduct Formula
 

Are you sure it is 0 and not "O" then?

Bernard V Liengme wrote:

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

"Phil Hageman" wrote in message
...

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),PROD UCT
(BI41,BI43)))*AR44

Thanks,
Phil