#n/a - how can I avoid and format it to number to include in sum

[Rechie]

I am using vlookup but some results are #n/a. How can i avoid it so that my
sum formula will not show #n/a.

Thanks

Rechie

 

[Bob Phillips]

=IF(ISNA(vlookup_formula),"",vlookup_formula)

If you have Excel 2007

=IF(ISERROR(vlookup_formula),"")

 

[JLatham]

"Wrap" your VLOOKUP() in an error trap for #N/A like this:

Your formula:
=VLOOKUP(A1,B1:E5,3,False)
to prevent display of #N/A
=IF(ISNA(VLOOKUP(A1,B1:E5,3,False)),"",VLOOKUP(A1,B1:E5,3,False))

 

[מיכ×ל (מיקי) ×בידן]

Assume your to be summed data is in range D120.
In D21 try the following Array-Firmula:
{=SUM(IF(ISNA(D120),0,D120))}
*** NOTE:
The formula is to be confirmed with CTRL+SHIFT+ENTER rather than with simply
ENTER.
The curly brackets {} are not to be typed manually, those are entered by the
“Excelâ€, when the formula is entered as an Array formula.
Micky

והמשך/×™, × ×, ×œ×§×¨×•× ×ת השורה הב××”:
***********
×× ×ª×’×•×‘×ª×™ עזרה לחץ/×™, × ×, על <כן> בפס ×”×ופקי התחתון!
***********
מיכ×ל ×בידן
מנהל ×¤×•×¨×•× "×ופיס" ב"תפוז"
[Microsoft" Most Valuable Professional [MVP"

 

[מיכ×ל (מיקי) ×בידן]

It looks like: ISERROR but sounds like: IFERROR
Isn't it ?
Micky

 

[Bob Phillips]

Yes that IS my ERROR

--

HTH

Bob

It looks like: ISERROR but sounds like: IFERROR
Isn't it ?
Micky

 

[T. Valko]

{=SUM(IF(ISNA(D120),0,D120))}

Another way to write that:

=SUMIF(D120,"<1E100")

Or:

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

--
Biff
Microsoft Excel MVP

Assume your to be summed data is in range D120.
In D21 try the following Array-Firmula:
{=SUM(IF(ISNA(D120),0,D120))}
*** NOTE:
The formula is to be confirmed with CTRL+SHIFT+ENTER rather than with
simply
ENTER.
The curly brackets {} are not to be typed manually, those are entered by
the
"Excel", when the formula is entered as an Array formula.
Micky

?????/?, ??, ????? ?? ????? ????:
***********
?? ?????? ???? ???/?, ??, ?? <??> ??? ?????? ??????!
***********
????? ?????
???? ????? "?????" ?"????"
[Microsoft" Most Valuable Professional [MVP"

I am using vlookup but some results are #n/a. How can i avoid it so that
my
sum formula will not show #n/a.

Thanks

Rechie