Array formula to give blank

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

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

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

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)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Joanne Ramaekers" wrote:
> 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
>

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

"Joanne Ramaekers" wrote:

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

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

"Max" wrote:

> 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)))
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Joanne Ramaekers" wrote:
> > 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
> >

"Joanne Ramaekers" wrote:
> 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Do you really need an array formula?

=SUMIF(Quote_Code,A5,Quote_amt)

Biff

"Joanne Ramaekers" <[email protected]> wrote in
message news:[email protected]...
> 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
>
> "Max" wrote:
>
>> 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)))
>> --
>> Max
>> Singapore
>> http://savefile.com/projects/236895
>> xdemechanik
>> ---
>> "Joanne Ramaekers" wrote:
>> > 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
>> >