Ignoring #NA's

[andyb16]

Hi All,

I have got a formula sheet with some known #NA errors. These errors are really there because the formula that has been build automatically populates everymonth and the #NA's would then take care of themselves.

However, I need to to sum all values in a row to find out grand totals.

When I try and do this the Grand Totals output is given as #NA due to some of the #NA's that exist. How can I get to have sum total without removing the #NA's?

Thanks

 

[JackDanIce]

Without knowing what the rest of your sheet/formulae look like, one way could be to change your formula so they can account for errors, e.g.:

=IF(ISERROR(A1/B1),0,A1/B1)

I believe Excel 2007 and 2010 infact has an IFERROR statement, e.g.:

=IFERROR(A1/B1,0,A1/B1)

 

[Dannyh1]

Hi Try

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

 

[andyb16]

Thanks Danny/Jack!

Both are working....the first one would cost me an extra step.

Thanks u very much

 

[Richard Schollar]

You can modify Danny's so that it is slightly shorter:

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