Sum Function When Formula Produces #N/A

[cmiedaner]

Hello.

I am using Excel 2010.

I have a formula that produces a number or #N/A for each row in my spreadsheet.

I would like to sum this column and come up with a total but the SUM function does not work with the #N/A.

Is there a way to sum the column even though there are #N/A's ?

Thanks in advance.

 

[joeu2004]

I am using Excel 2010.
I have a formula that produces a number or #N/A for each row
in my spreadsheet.
I would like to sum this column and come up with a total but
the SUM function does not work with the #N/A.
Is there a way to sum the column even though there are #N/A's ?

To answer your question, try:

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

But I think it would be better to avoid the #N/A errors in the first place.
Suppose the formula returning #N/A is of the form =VLOOKUP(...). In Excel
2007 and later, you might write:

=IFERROR(VLOOKUP(...),"")

 

[cmiedaner]

=SUMIF(A1:A100,"<>#N/A") But I think it would be better toavoid the #N/A errors in the first place. Suppose the formula returning #N/A is of the form =VLOOKUP(...). In Excel 2007 and later, you might write: =IFERROR(VLOOKUP(...),"")

Thanks. That was very helpful.