sum returns #N/A

[Guest]

I am getting a return of #N/A when I try to sum a column. Its a column of
formulas which sometimes return #N/A also. How do I get around this problem
and sum the values I do have?

Thanks.

Todd

 

[Peo Sjoblom]

Probably better to fix the na in the columns but here goes

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


replace A:A with your column

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Bob Phillips]

Change the formulas to trap NA and output 0

=-IF(ISNA(formula),0,formula)

Here is one way if you don't want to correct the data

=SUM(IF(NOT(ISERROR(J1:J10)),J1:J10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

In your formulae, add error checking to remove the #N/As; for example set the
cells to blank if there is an error.

Generically, something like:

=IF(ISNA(Myformula),"",Myformula)

HTH