Array formula to give blank

J

Joanne Ramaekers

I have an array formula that is working perfectly:
{=SUM(IF(Quote_Code=A3,Quote_Amt,))}

I would like to make one change, but I don't seem to be able to get it
working. When the locigal test is false I would like to dispaly nothing in
the cell. It's currently displaying 0.00.

I have tried {=SUM(IF(Quote_Code=A3,Quote_Amt," "))} but is still shows 0.00.

Any ideas?
Jo

P.S. I'm using Office 2003
 
C

Corey

TRY Closing the inverted commas Below:
{=SUM(IF(Quote_Code=A3,Quote_Amt,""))}

Corey....
 
M

Max

For a neat, clean view, it might be easier to just suppress the display of
extraneous zeros in the sheet via clicking:
Tools > Options > View tab > Uncheck "Zero values" > OK

Or, perhaps just IF error trap for nothing in A5 in front would suffice for
the purpose. Eg, try array-entered:
=IF(A5="","",SUM(IF(Quote_Code=A5,Quote_amt)))
 
J

JMB

I think you will need to rearrange your if statement

{=IF(SUM(--(Quote_Code=A3)),SUM((Quote_Code=A3)*Quote_Amt),"")}

or, a non-array approach
=IF(COUNTIF(Quote_Code,A3),COUNTIF(Quote_Code,A3)*Quote_Amt,"")
 
J

Joanne Ramaekers

Thanks Max, the Uncheck zero values did the job I needed done.

The other option did not work for me, as there was always going to be
something in A5.

Corey, just removing the space between the "s did not work.
JMB, even though the array formula did work, it upset other formulas that
needed the result from this cell.

Thanks for everyones help.
Jo
 
M

Max

Joanne Ramaekers said:
Thanks Max, the Uncheck zero values did the job I needed done ..
Click to expand...

Glad it did, Joanne ! Thanks for feedback ..

To avoid upsetting downstream formulas pointing to the array formulas,
amending the array to return nothing, i.e.: "" (instead of the default zeros)
wouldn't have been an option.
 
Top