Array formula to give blank

[Guest]

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

 

[Corey]

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

Corey....

 

[Guest]

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)))

 

[Guest]

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,"")

 

[Guest]

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

 

[Guest]

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

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.

---

 

[Biff]

Do you really need an array formula?

=SUMIF(Quote_Code,A5,Quote_amt)

Biff