SumProduct

M

mldancing

I have a sumproduct formula that return zero, I tried to add

ISBLANK, ISNA, ISERROR

but couldn't replace the zeros with blanks.

Are there any other ways?

Thank you.
 
J

JLatham

By all means, post the formula you're using - it will help us see how to best
advise you.

One typical way of dealing with something returning zero or an error is by
enclosing the operational portion within an if statement and returning "" if
a specific condition occures, otherwise perform the operation. Something
like this:

=IF(SUMPRODUCT(...)=0,"",SUMPRODUCT(...))
where SUMPRODUCT(...) is your formula that works other than returning and
displaying zero when you don't want to see that.
 
M

mldancing

My formula is:

SUMPRODUCT(--($A$3:$A$54="Business"),--($B$3:$B$54="Week 10"),$D$3:$D$54)

The thing is: I need it to display zero when the fomula picks up the value
zero. But even if the formula pick up blanks, this formula will return a
zero. That's the problem, please help!

Thank you.
 
T

Toppers

try:

=IF(SUMPRODUCT(--($A$3:$A$54="Business"),--($B$3:$B$54="Week
10"),--(ISNUMBER($D$3:$D$54)))=0,"",SUMPRODUCT(--($A$3:$A$54="Business"),--($B$3:$B$54="Week 10"),($D$3:$D$54)))
 
R

Roger Govier

Hi

You will always get zero's. That's the way Sumproduct works.
It is multiplying arrays of True or False for each of the 2 tests it is
being given times the set of values in column D.
The True's are coerced to 1 and the False's to 0.
The multiplication of
1*1*Value in column D will produce the value
but any other combination of
0*1*Value
1*0*Value
0*0*Value
is bound to produce a result of 0

You can either suppress ALL 0's with the methods suggested by J Latham
and Toppers, but there is no case where an answer is Null for you to be
able to show it as Null as opposed to Zero.

You can also suppress all zero's if you wish through Tools>Options>View>
untick Zero values.
 
T

Toppers

Roger,
Please test my formula because on my testing it DOES
differntiate between blank and zero !

With columns A, B and D blank, my formula returns blank, whereas the
original returns zero.

With A=""Business" and B=" Week 10" but D blank, I still get blank; the
original gets zero.

If I add 0 into D, I get answer of 0.

Or have I completely lost it!
 
M

mldancing

Thank you so much .... this formula works! :)



Toppers said:
try:

=IF(SUMPRODUCT(--($A$3:$A$54="Business"),--($B$3:$B$54="Week
10"),--(ISNUMBER($D$3:$D$54)))=0,"",SUMPRODUCT(--($A$3:$A$54="Business"),--($B$3:$B$54="Week 10"),($D$3:$D$54)))
Click to expand...
 
Top