Excel: Ignoring Specific data in a column

[Avi]

I have Excel data (raw) that I am importing into an Excel sheet which
has my formulas). I am trying to add up a column of numbers, but some
of the fields have the letter's "N/A"

Presently I am using formula =sum(A10:A15000), but it is not working
due to the "N/A". How can I get Excel to ignore the "N/A" and just add
the cell's that do have the numbers.

For reference, the numbers do have decimal points (xx.xx)

Thanks,

-Avi

 

[Guest]

If the N/A are just text, then select the column and use find/replace to make
them all zeros.

If the N/A are the result of functions then replace
=function
with
=IF(ISNA(function),0,function)

This efffectively makes them zero.

 

[Avi]

Addendum:

I would like to keep the solution in the formula that add's in the
numbers. Since this will be a form that will be utilized many times, I
would prefer to keep away from macro's/scripts. Basically, I would
like to download the data, and the formula ignores the "N/A"

 

[Dave Peterson]

If it's really the text N/A, then you're sum should work ok.

If it's really #N/A (an error), you could use:

=sum(if(isnumber(a10:a15000),a10:a15000))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

 

[Avi]

I figured it out. Apparantly Excel ignores it anyhow, my problem is
the data imported, the numbers, are imported as text.

So my question will be different in another post.

 

[quimrider]

Thanks Dave Peterson!

I used your solution with a similar problem I had. I needed to sum up
values in a range that were calculated with formulas. I needed to sum
them up even though I didn't have the input for all the formulas which
yielded a bunch of #VALUE errors mixed in with my results.