#N/A Help

  • Thread starter Thread starter Joseph M. Yonek
  • Start date Start date
J

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
 
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
 
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
 
Since you want to keep #N/A's as is, use...

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

Subtotal cannot do this however.
 
Back
Top