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

Array formula to give blank
 

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

Corey....
"Joanne Ramaekers" wrote in
message ...
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

Array formula to give blank
 

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

Array formula to give blank
 

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

Array formula to give blank
 

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

Array formula to give blank
 

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

Array formula to give blank
 

Do you really need an array formula?

=SUMIF(Quote_Code,A5,Quote_amt)

Biff

"Joanne Ramaekers" wrote in
message ...
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