Excel 0 and Blank fields.

I am working on a spreadsheet that is relatively simple, but I need to have
empty cells and cells input with a 0 to give a referenced cell different
values. I have used the following formulas to do this for a cell with a 0
in it but I have found that this interprets an empty cell the same as a cell
with a 0 in it.



Cell C2 Ex.1: =IF (A2+A3=0), " " , SUM (A2:A3)

RESULTS: If I enter a 0 in A2 or A3 or if I leave A2 or A3 empty the
returned value will be blank.



Cell C2 Ex.2: =IF (A2+A3= " " ), " " , SUM (A2:A3)

RESULTS: I get the same results as Ex.1.




A
B
C

1
Input Data

Output Data

2




3
00








What I need is: IF (A2 AND A3= Blank ) THEN return blank, ELSE SUM (A2:A3)
[even if the value(s) entered into A2 and/or A3 is/are 0 or any combination
of zeros and blanks, I need it to return a 0.]



Thanks for any input.

These seem to satisfy your requirement
=IF(AND(ISBLANK(A2),ISBLANK(A3)),"",A2+A3)
=IF(AND(ISBLANK(A2),ISBLANK(A3)),"",SUM(A2:A3))

Note
(a) SUM(A2:A3) is overkill for A2+A3 unless you need to get a numeric
result even when one cell has text
(b) A cell with a formula such as =IF(MONTH(TODAY()) =3, "", MONTH(TODAY()))
which displays a blank in March will still fail the ISBLANK test because a
cell with a formula is not blank even when it displays a blank (clear?)

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

<[email protected]> wrote in message
news:[email protected]...
>
> What I need is: IF (A2 AND A3= Blank ) THEN return blank, ELSE SUM
> (A2:A3)
> [even if the value(s) entered into A2 and/or A3 is/are 0 or any
> combination
> of zeros and blanks, I need it to return a 0.]
>
>

Thanks all for the info. All 3 formulas seem to proof out correctly. Sorry
for the multiple posts though. I was getting an error message saying it
couldn't connect to the news server.
<[email protected]> wrote in message
news:[email protected]...
>
>
> I am working on a spreadsheet that is relatively simple, but I need to
have
> empty cells and cells input with a 0 to give a referenced cell different
> values. I have used the following formulas to do this for a cell with a 0
> in it but I have found that this interprets an empty cell the same as a
cell
> with a 0 in it.
>
>
>
> Cell C2 Ex.1: =IF (A2+A3=0), " " , SUM (A2:A3)
>
> RESULTS: If I enter a 0 in A2 or A3 or if I leave A2 or A3 empty the
> returned value will be blank.
>
>
>
> Cell C2 Ex.2: =IF (A2+A3= " " ), " " , SUM (A2:A3)
>
> RESULTS: I get the same results as Ex.1.
>
>
>
>
> A
> B
> C
>
> 1
> Input Data
>
> Output Data
>
> 2
>
>
>
>
> 3
> 00
>
>
>
>
>
>
>
>
> What I need is: IF (A2 AND A3= Blank ) THEN return blank, ELSE SUM
(A2:A3)
> [even if the value(s) entered into A2 and/or A3 is/are 0 or any
combination
> of zeros and blanks, I need it to return a 0.]
>
>
>
> Thanks for any input.
>
>