"#VALUE!" in SUM

[Phoenix]

Hi!

Example:
A1=1
A2=2
A3=NA
A4=4

If i use SUM(A1:A4) = 7
If i use A1+A2+A3+A4 = #VALUE!

How can i get Excel to summarize by using A1+A2+A3+A4 without
getting #VALUE! ?

Phoeni

 

[Frank Kabel]

Hi
just using the simple addition (not using the SUM function) will throw
an error if you are summing text strings (e.g. in your example cell
A3).
Either use SUM or remove text values from you summing range

 

[Phoenix]

I understand, but in this case i really need to use A1+A2+A3+A4.
The text cell A3 can in the future be a number, but for now I need tex
there. And I can't use the SUM formula, cause it's cells between tha
is not suppose to be summorized (A1:A4 was just an example). And m
sheet is huge...so it will be too much to correct each formula when
add number in there.

But an alternative is just to keep the #VALUE! there :-

 

[JulieD]

Hi Phoenix

is it possible to use another solution such as a sumif ? is there anything
else on the row that is common and could be used to sumif on?

Cheers
JulieD

 

[BrianB]

providing the NA is the only problem :-

=SUMIF(A1:A4,"<>NA"

 

[Frank Kabel]

Hi
a little bit kludgy but maybe
=SUM(A1,A2,A3,A4)
will help