Trying to sum three mulitplied cell pairs (A17*A8,
A20*A21, AND A23*A24), then, multiply that sum by a final
cell value (A34). Problem is, A17, A20, and A23 are
linked to cells in another sheet where the formula is =IF
(B10="","",SUM(AB10:AB13)) - which returns a #Value! error
because of the quotes "" (when the value is blank). Can't
use a zero instead of the quotes, so I'm advised to
substitute function calls, the "Ns", in the below
formula. Can someone help me further - how to set this
up.

"Add some function calls to convert the null string to
zero":
=SUM(((A17*A18)+(N(A20)*N(A21))+(N(A23)*N(A24)))*A 34)

Thanks, Phl

Give this a try

=SUM(PRODUCT(A17,A8),PRODUCT(A20,A21), PRODUCT(A23,A24))*A34

Note that Sum and Product will ignore text. Also avoid "+" within a sum
functions instead separate each addend with a ",")
e.g A1+B2+C3 will give the same result as SUM(A1,B2,C3) except that the
former will result to error when there are text in either of the three cell
while the latter will not.

Regards,

Jon-jon

"Phil Hageman" wrote in message
...
Trying to sum three mulitplied cell pairs (A17*A8,
A20*A21, AND A23*A24), then, multiply that sum by a final
cell value (A34). Problem is, A17, A20, and A23 are
linked to cells in another sheet where the formula is =IF
(B10="","",SUM(AB10:AB13)) - which returns a #Value! error
because of the quotes "" (when the value is blank). Can't
use a zero instead of the quotes, so I'm advised to
substitute function calls, the "Ns", in the below
formula. Can someone help me further - how to set this
up.

"Add some function calls to convert the null string to
zero":
=SUM(((A17*A18)+(N(A20)*N(A21))+(N(A23)*N(A24)))*A 34)

Thanks, Phl

It is a worksheet formula and it is already set up. If you want to do it in
code.

Application.Sum(((Val(Range("A17"))*Val(Range("A18 ")))+(val(Range("A20"))*Va
l(Range("A21")))+(Val(Range("A23"))*Val(Range("A24 "))))*Range("A34"))

of course the Sum is superflous.

((Val(Range("A17"))*Val(Range("A18")))+(val(Range( "A20"))*Val(Range("A21")))
+(Val(Range("A23"))*Val(Range("A24"))))*Range("A34 ")

would work as well.

--
Regards,
Tom Ogilvy




"Phil Hageman" wrote in message
...
Trying to sum three mulitplied cell pairs (A17*A8,
A20*A21, AND A23*A24), then, multiply that sum by a final
cell value (A34). Problem is, A17, A20, and A23 are
linked to cells in another sheet where the formula is =IF
(B10="","",SUM(AB10:AB13)) - which returns a #Value! error
because of the quotes "" (when the value is blank). Can't
use a zero instead of the quotes, so I'm advised to
substitute function calls, the "Ns", in the below
formula. Can someone help me further - how to set this
up.

"Add some function calls to convert the null string to
zero":
=SUM(((A17*A18)+(N(A20)*N(A21))+(N(A23)*N(A24)))*A 34)

Thanks, Phl

Jon Jon, Thanks, I got things working just as I needed.
-----Original Message-----
Give this a try

=SUM(PRODUCT(A17,A8),PRODUCT(A20,A21), PRODUCT(A23,A24))
*A34

Note that Sum and Product will ignore text. Also
avoid "+" within a sum
functions instead separate each addend with a ",")
e.g A1+B2+C3 will give the same result as SUM(A1,B2,C3)
except that the
former will result to error when there are text in either
of the three cell
while the latter will not.

Regards,

Jon-jon

"Phil Hageman" wrote in message
...
Trying to sum three mulitplied cell pairs (A17*A8,
A20*A21, AND A23*A24), then, multiply that sum by a
final
cell value (A34). Problem is, A17, A20, and A23 are
linked to cells in another sheet where the formula is
=IF
(B10="","",SUM(AB10:AB13)) - which returns a #Value!
error
because of the quotes "" (when the value is blank).
Can't
use a zero instead of the quotes, so I'm advised to
substitute function calls, the "Ns", in the below
formula. Can someone help me further - how to set this
up.

"Add some function calls to convert the null string to
zero":
=SUM(((A17*A18)+(N(A20)*N(A21))+(N(A23)*N(A24)))
*A34)

Thanks, Phl


.

Thanks, Tom. I got things to work just as needed.
-----Original Message-----
It is a worksheet formula and it is already set up. If
you want to do it in
code.

Application.Sum(((Val(Range("A17"))*Val(Range("A1 8")))+
(val(Range("A20"))*Va
l(Range("A21")))+(Val(Range("A23"))*Val(Range("A2 4"))))
*Range("A34"))

of course the Sum is superflous.

((Val(Range("A17"))*Val(Range("A18")))+(val(Range ("A20"))
*Val(Range("A21")))
+(Val(Range("A23"))*Val(Range("A24"))))*Range("A3 4")

would work as well.

--
Regards,
Tom Ogilvy




"Phil Hageman" wrote in message
...
Trying to sum three mulitplied cell pairs (A17*A8,
A20*A21, AND A23*A24), then, multiply that sum by a
final
cell value (A34). Problem is, A17, A20, and A23 are
linked to cells in another sheet where the formula is
=IF
(B10="","",SUM(AB10:AB13)) - which returns a #Value!
error
because of the quotes "" (when the value is blank).
Can't
use a zero instead of the quotes, so I'm advised to
substitute function calls, the "Ns", in the below
formula. Can someone help me further - how to set this
up.

"Add some function calls to convert the null string to
zero":
=SUM(((A17*A18)+(N(A20)*N(A21))+(N(A23)*N(A24)))
*A34)

Thanks, Phl


.