Hello,

One of the ranges I use in my sumproduct formula includes cells with formula
like

=IF(A1=0, "", A1)

The sumproduct returns a #VALUE! error whenever the cell A1 is 0, otherwise
it works fine.

How to deal with the problem by modifying the sumproduct ?
(The formula IF(A1=0, "", A1) is fixed and cannot be modified)

Any suggestions would be appreciated. I use Excel XP. Thanks.

For better answers, post your sumproduct formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000, Files:358, Subscribers:55
xdemechanik
---
"robot" wrote in message
...
Hello,

One of the ranges I use in my sumproduct formula includes cells with
formula like

=IF(A1=0, "", A1)

The sumproduct returns a #VALUE! error whenever the cell A1 is 0,
otherwise it works fine.

How to deal with the problem by modifying the sumproduct ?
(The formula IF(A1=0, "", A1) is fixed and cannot be modified)

Any suggestions would be appreciated. I use Excel XP. Thanks.

Could it be that the possible result in that particular cell could be blank?

The way you have this formula set it will only fill in the cell if A1 is 0,
otherwise there will be nothing.

"robot" wrote:

Hello,

One of the ranges I use in my sumproduct formula includes cells with formula
like

=IF(A1=0, "", A1)

The sumproduct returns a #VALUE! error whenever the cell A1 is 0, otherwise
it works fine.

How to deal with the problem by modifying the sumproduct ?
(The formula IF(A1=0, "", A1) is fixed and cannot be modified)

Any suggestions would be appreciated. I use Excel XP. Thanks.

Presumably the range with those formulas is the "sum range". Assuming you
have a formula like

=SUMPRODUCT((A1:A10="x")*(B1:B10="y")*C1:C10)

try changing syntax to

=SUMPRODUCT((A1:A10="x")*(B1:B10="y"),C1:C10)

Note , instead of *





"JasonP CCTM LV" wrote:

Could it be that the possible result in that particular cell could be blank?

The way you have this formula set it will only fill in the cell if A1 is 0,
otherwise there will be nothing.

"robot" wrote:

Hello,

One of the ranges I use in my sumproduct formula includes cells with formula
like

=IF(A1=0, "", A1)

The sumproduct returns a #VALUE! error whenever the cell A1 is 0, otherwise
it works fine.

How to deal with the problem by modifying the sumproduct ?
(The formula IF(A1=0, "", A1) is fixed and cannot be modified)

Any suggestions would be appreciated. I use Excel XP. Thanks.

That is exactly what I missed. I should have posted my formula.

Thanks so much! You guys are great!


"daddylonglegs" ...
Presumably the range with those formulas is the "sum range". Assuming you
have a formula like

x =SUMPRODUCT((A1:A10="x")*(B1:B10="y")*C1:C10)

try changing syntax to

=SUMPRODUCT((A1:A10="x")*(B1:B10="y"),C1:C10)

Note , instead of *





"JasonP CCTM LV" wrote:

Could it be that the possible result in that particular cell could be
blank?

The way you have this formula set it will only fill in the cell if A1 is
0,
otherwise there will be nothing.

"robot" wrote:

Hello,

One of the ranges I use in my sumproduct formula includes cells with
formula
like

=IF(A1=0, "", A1)

The sumproduct returns a #VALUE! error whenever the cell A1 is 0,
otherwise
it works fine.

How to deal with the problem by modifying the sumproduct ?
(The formula IF(A1=0, "", A1) is fixed and cannot be modified)

Any suggestions would be appreciated. I use Excel XP. Thanks.