Summing with #N/A in thr range.

[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?

 

[Ken Wright]

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)

 

[Frank Kabel]

Hi
one way
=IF(ISNA(VLOOKUP(...),"",VLOOKUP(...))

 

[JE McGimpsey]

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 "".