Sum Function When Formula Produces #N/A
 

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.

Sum Function When Formula Produces #N/A
 

wrote:
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(...),"")

Sum Function When Formula Produces #N/A
 

On Friday, August 17, 2012 10:23:15 AM UTC-4, joeu2004 wrote:
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(...),"")

Thanks. That was very helpful.