#N/A Help

[Joseph M. Yonek]

I have a spreadsheet that uses a vlookup function to populate several cells.

In some instances the vlookup function returns a #N/A. I expect that and
that is fine.

However, I am trying to sum this series of cells. With the #N/A in the cell
series the sum function or preferably the subtotal function also returns a
#N/A.

How can a sum or Subtotal the series of cells despite the existence of #N/A
in some of the referenced cells.

Thanks in advance.

Joe

 

[John Wilson]

Joseph,

Best bet would be to go back and redo the formulas so that you
don't get the #N/A

Basic construct:
=IF(ISNA(yourlookup),"",yourlookup)
will return an empty string if the result of the vlookup would have
given you a #N/A

John

 

[tracey]

Try this..........

=IF(ISERROR(VLOOKUP(A5,'Sheet1'!
$A$5:$B$2946,2,0)),0,VLOOKUP(A5,'Sheet1'!$A$5:$B$2946,2,0))

It should enter a zero instead of #N/A

Tracey

 

[Aladin Akyurek]

Since you want to keep #N/A's as is, use...

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

Subtotal cannot do this however.