SumProduct problem

  • Thread starter Thread starter robot
  • Start date Start date
R

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.
 
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.
 
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 *
 
That is exactly what I missed. I should have posted my formula.

Thanks so much! You guys are great!


daddylonglegs said:
Presumably the range with those formulas is the "sum range". Assuming you
have a formula like
Click to expand...
x> =SUMPRODUCT((A1:A10="x")*(B1:B10="y")*C1:C10)
 
Back
Top