# Ignoring the #NA output

#### ckroon

##### Board Regular
Hey all, I searched but found no answer so I put it to you.
I need to sum up the results of formulas to do further calculations, but some cells have #NA in them if the data was not entered. I need the #NA to show up as zero, can I add this to the formula that is there already?

Thanks

#### cfree36

##### Board Regular
You could use this array formula for your sum which would exclude the errors:

{=SUM(IF(ISERROR(Data),"",(Data)))}

You don't key the '{' you just type =SUM(IF(ISERROR(Data),"",(Data))) and use Ctrl+Shift+Enter to enter the formula.

Change 'Data' to reflect your range.

Or you can get rid of the N/A in the actual formula using if(iserror

EXAMPLE:

=(if(iserror(sum(A1:A22)),0,(sum(A1:A22)))

#### Oaktree

##### MrExcel MVP
Try something like
Code:
``=IF(ISNA(OLD_FORMULA),0,OLD_FORMULA)``
or, to sum a column with #N/A's, try
Code:
``=SUMIF(A1:A100,"<>#N/A")``

#### ckroon

##### Board Regular
THanks

Oaktree that worked great. Thanks!

=SUMIF(A1:A100,"<>#N/A")
Now I can sum the columns if there are #NA's in it.