Summing with #N/A in thr range.

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

I have a spreadsheet where I use vlookup to populate a
number of fields based on a drop down box. In the vlookup
I am using the FALSE in the end to ensure I pick the
corrcet data however this returns #N/A if there is no
data and I can not then sum the range. How do I exclude
the #N/A from the sum or have the vlookup return 0 when
there is no value in the vlookup range?
 
Whilst you can always have the sum exclude the errors by means of formulas, it
is usually better to fix the problem at source and prevent those errors from
appearing at all, eg:-

With whatever foprmulas you have returning your errors, make them

=IF(ISNA(your_formula),0,your_formula)

or

=IF(ISNA(your_formula),"",your_formula)
 
I'd suggest you have your formula return a null string if thre is no
value in the lookup range (SUM() will ignore text, including null
strings)):


=IF(ISNA(MATCH(A1,J1:J100,0)),"",VLOOKUP(A1,J1:K100,2,0))

If you want zero, substitute 0 for "".
 
Back
Top