Sum Function When Formula Produces #N/A

  • Thread starter Thread starter cmiedaner
  • Start date Start date
C

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.
 
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 ?
Click to expand...

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(...),"")
 
=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.
 
Back
Top