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 man
products were sold in a month for a specific company. If no product
are sold, it produces a $0.00. I would rather this left blank. Her
is my array that I need to have produce a blank when it ends up as
0.

{=SUM(IF('Shipping Log'!$D$2:$D$3255=$A3,IF('Shippin
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 leav
it blank instead? I know it has something to do wit
=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]

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

in
message news:[email protected]...

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!