Ignoring the #NA output

[ckroon]

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

 

[DominicB]

Good evening ckroon

If you want a specific answer to this question you're going to have to be a bit more precise about what your formulae are.

Other than that, take a look here.

http://www.mrexcel.com/board2/viewtopic.php?t=179528&highlight=

HTH

DominicB

 

[cfree36]

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]

Try something like

=IF(ISNA(OLD_FORMULA),0,OLD_FORMULA)

or, to sum a column with #N/A's, try

=SUMIF(A1:A100,"<>#N/A")

 

[ckroon]

THanks

Oaktree that worked great. Thanks!

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