SumProduct problem

[robot]

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.

 

[Max]

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

 

[JasonP CCTM LV]

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.

 

[daddylonglegs]

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 *

 

[robot]

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

Thanks so much! You guys are great!

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)