SumProduct
 

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.

SumProduct
 

Post your Sumproduct formula

Biff

"mldancing" wrote in message
...
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.

SumProduct
 

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.


"mldancing" wrote:

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.

SumProduct
 

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.

"JLatham" wrote:

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.


"mldancing" wrote:

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.

SumProduct
 

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)))

"mldancing" wrote:

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.

"JLatham" wrote:

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.


"mldancing" wrote:

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.

SumProduct
 

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 ToolsOptionsView
untick Zero values.

--
Regards

Roger Govier


"mldancing" wrote in message
...
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.

"JLatham" wrote:

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.


"mldancing" wrote:

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.

SumProduct
 

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!

"Roger Govier" wrote:

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 ToolsOptionsView
untick Zero values.

--
Regards

Roger Govier


"mldancing" wrote in message
...
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.

"JLatham" wrote:

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.


"mldancing" wrote:

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.

SumProduct
 

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



"Toppers" wrote:

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)))

"mldancing" wrote:

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.

"JLatham" wrote:

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.


"mldancing" wrote:

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.