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

[Intuit]

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?

[Biff]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Intuit]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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!