software

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(???=quot;quot;,quot;quot;,sum....

but its not working for some reason. Can anyone help?--
Intuit
------------------------------------------------------------------------
Intuit's Profile: www.excelforum.com/member.php...oamp;userid=30901
View this thread: www.excelforum.com/showthread...hreadid=508271Hi!

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,quot;quot;,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

quot;Intuitquot; gt; wrote in
message ...
gt;
gt; I have a formula that is an array I think which calculates how many
gt; products were sold in a month for a specific company. If no products
gt; are sold, it produces a $0.00. I would rather this left blank. Here
gt; is my array that I need to have produce a blank when it ends up as a
gt; 0.
gt;
gt; {=SUM(IF('Shipping Log'!$D$2:$D$3255=$A3,IF('Shipping
gt; Log'!$A$2:$A$3255=C$1,'Shipping Log'!$M$2:$M$3255,0),0))}
gt;
gt; this formula is in box c13. If this will result in 0.00 how do I leave
gt; it blank instead? I know it has something to do with
gt; =if(???=quot;quot;,quot;quot;,sum....
gt;
gt; but its not working for some reason. Can anyone help?
gt;
gt;
gt; --
gt; Intuit
gt; ------------------------------------------------------------------------
gt; Intuit's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30901
gt; View this thread: www.excelforum.com/showthread...hreadid=508271
gt;

Biff Wrote:
gt; Hi!
gt;
gt; You don't need to use an array formula for that:
gt;
gt; =SUMPRODUCT(--('Shipping Log'!$D$2:$D$3255=$A3),--('Shipping
gt; Log'!$A$2:$A$3255=C$1),'Shipping Log'!$M$2:$M$3255)
gt;
gt; To suppress a zero return will make the formula twice as long:
gt;
gt; =IF(SUMPRODUCT(--('Shipping Log'!$D$2:$D$3255=$A3),--('Shipping
gt; Log'!$A$2:$A$3255=C$1),'Shipping
gt; Log'!$M$2:$M$3255)=0,quot;quot;,SUMPRODUCT(--('Shipping
gt; Log'!$D$2:$D$3255=$A3),--('Shipping Log'!$A$2:$A$3255=C$1),'Shipping
gt; Log'!$M$2:$M$3255))
gt;
gt; *OR*
gt;
gt; Use the first formula and format the cell to not display the zero:
gt;
gt; Custom format: 0;-0;;@
gt;
gt; Note: the zero is still in the cell, it's just not being displayed.
gt; This
gt; might matter if you're doing other downstream calcs that use this
gt; cell.
gt;
gt; Biff
gt;
gt; quot;Intuitquot; gt; wrote
gt; in
gt; message ...
gt; gt;
gt; gt; I have a formula that is an array I think which calculates how many
gt; gt; products were sold in a month for a specific company. If no
gt; products
gt; gt; are sold, it produces a $0.00. I would rather this left blank.
gt; Here
gt; gt; is my array that I need to have produce a blank when it ends up as a
gt; gt; 0.
gt; gt;
gt; gt; {=SUM(IF('Shipping Log'!$D$2:$D$3255=$A3,IF('Shipping
gt; gt; Log'!$A$2:$A$3255=C$1,'Shipping Log'!$M$2:$M$3255,0),0))}
gt; gt;
gt; gt; this formula is in box c13. If this will result in 0.00 how do I
gt; leave
gt; gt; it blank instead? I know it has something to do with
gt; gt; =if(???=quot;quot;,quot;quot;,sum....
gt; gt;
gt; gt; but its not working for some reason. Can anyone help?
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Intuit
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Intuit's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=30901
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=508271
gt; gt;
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: www.excelforum.com/member.php...oamp;userid=30901
View this thread: www.excelforum.com/showthread...hreadid=508271
Intuit Wrote:
gt; Hey again Biff. Man you got this excel thing down pat! I obviously
gt; opted for the second option, but when I do the custom format, my $$$ go
gt; away. Anyway to still format these values (when there is a value) as
gt; currency?
I figured it out. Thanks!--
Intuit
------------------------------------------------------------------------
Intuit's Profile: www.excelforum.com/member.php...oamp;userid=30901
View this thread: www.excelforum.com/showthread...hreadid=508271