Need to get rid of 0's and replace with Blanks
 

I have a formula that is an array I think which calculates how many
products were sold in a month for a specific company. If no products
are sold, it produces a $0.00. I would rather this left blank. Here
is my array that I need to have produce a blank when it ends up as a
0.

{=SUM(IF('Shipping Log'!$D$2:$D$3255=$A3,IF('Shipping
Log'!$A$2:$A$3255=C$1,'Shipping Log'!$M$2:$M$3255,0),0))}

this formula is in box c13. If this will result in 0.00 how do I leave
it blank instead? I know it has something to do with
=if(???="","",sum....

but its not working for some reason. Can anyone help?


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=508271

Need to get rid of 0's and replace with Blanks
 

Hi!

You don't need to use an array formula for that:

=SUMPRODUCT(--('Shipping Log'!$D$2:$D$3255=$A3),--('Shipping
Log'!$A$2:$A$3255=C$1),'Shipping Log'!$M$2:$M$3255)

To suppress a zero return will make the formula twice as long:

=IF(SUMPRODUCT(--('Shipping Log'!$D$2:$D$3255=$A3),--('Shipping
Log'!$A$2:$A$3255=C$1),'Shipping
Log'!$M$2:$M$3255)=0,"",SUMPRODUCT(--('Shipping
Log'!$D$2:$D$3255=$A3),--('Shipping Log'!$A$2:$A$3255=C$1),'Shipping
Log'!$M$2:$M$3255))

*OR*

Use the first formula and format the cell to not display the zero:

Custom format: 0;-0;;@

Note: the zero is still in the cell, it's just not being displayed. This
might matter if you're doing other downstream calcs that use this cell.

Biff

"Intuit" wrote in
message ...

I have a formula that is an array I think which calculates how many
products were sold in a month for a specific company. If no products
are sold, it produces a $0.00. I would rather this left blank. Here
is my array that I need to have produce a blank when it ends up as a
0.

{=SUM(IF('Shipping Log'!$D$2:$D$3255=$A3,IF('Shipping
Log'!$A$2:$A$3255=C$1,'Shipping Log'!$M$2:$M$3255,0),0))}

this formula is in box c13. If this will result in 0.00 how do I leave
it blank instead? I know it has something to do with
=if(???="","",sum....

but its not working for some reason. Can anyone help?


--
Intuit
------------------------------------------------------------------------
Intuit's Profile:
http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=508271

Need to get rid of 0's and replace with Blanks
 

Biff Wrote:
Hi!

You don't need to use an array formula for that:

=SUMPRODUCT(--('Shipping Log'!$D$2:$D$3255=$A3),--('Shipping
Log'!$A$2:$A$3255=C$1),'Shipping Log'!$M$2:$M$3255)

To suppress a zero return will make the formula twice as long:

=IF(SUMPRODUCT(--('Shipping Log'!$D$2:$D$3255=$A3),--('Shipping
Log'!$A$2:$A$3255=C$1),'Shipping
Log'!$M$2:$M$3255)=0,"",SUMPRODUCT(--('Shipping
Log'!$D$2:$D$3255=$A3),--('Shipping Log'!$A$2:$A$3255=C$1),'Shipping
Log'!$M$2:$M$3255))

*OR*

Use the first formula and format the cell to not display the zero:

Custom format: 0;-0;;@

Note: the zero is still in the cell, it's just not being displayed.
This
might matter if you're doing other downstream calcs that use this
cell.

Biff

"Intuit" wrote
in
message ...

I have a formula that is an array I think which calculates how many
products were sold in a month for a specific company. If no
products
are sold, it produces a $0.00. I would rather this left blank.
Here
is my array that I need to have produce a blank when it ends up as a
0.

{=SUM(IF('Shipping Log'!$D$2:$D$3255=$A3,IF('Shipping
Log'!$A$2:$A$3255=C$1,'Shipping Log'!$M$2:$M$3255,0),0))}

this formula is in box c13. If this will result in 0.00 how do I
leave
it blank instead? I know it has something to do with
=if(???="","",sum....

but its not working for some reason. Can anyone help?


--
Intuit

------------------------------------------------------------------------
Intuit's Profile:
http://www.excelforum.com/member.php...o&userid=30901
View this thread:
http://www.excelforum.com/showthread...hreadid=508271

Hey again Biff. Man you got this excel thing down pat! I obviously
opted for the second option, but when I do the custom format, my $$$ go
away. Anyway to still format these values (when there is a value) as
currency?


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=508271

Need to get rid of 0's and replace with Blanks
 

Intuit Wrote:
Hey again Biff. Man you got this excel thing down pat! I obviously
opted for the second option, but when I do the custom format, my $$$ go
away. Anyway to still format these values (when there is a value) as
currency?
I figured it out. Thanks!


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=508271