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

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

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

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

If you have Excel 2007

=IF(ISERROR(vlookup_formula),"")

--

HTH

Bob

"Rechie" wrote in message
...
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

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

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


"Rechie" wrote:

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

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

Assume your to be summed data is in range D1:D20.
In D21 try the following Array-Firmula:
{=SUM(IF(ISNA(D1:D20),0,D1:D20))}
*** 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"


"Rechie" wrote:

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

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

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


"Bob Phillips" wrote:

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

If you have Excel 2007

=IF(ISERROR(vlookup_formula),"")

--

HTH

Bob

"Rechie" wrote in message
...
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


.

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

Yes that IS my ERROR

--

HTH

Bob

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
It looks like: ISERROR but sounds like: IFERROR
Isn't it ?
Micky


"Bob Phillips" wrote:

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

If you have Excel 2007

=IF(ISERROR(vlookup_formula),"")

--

HTH

Bob

"Rechie" wrote in message
...
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


.

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

{=SUM(IF(ISNA(D1:D20),0,D1:D20))}

Another way to write that:

=SUMIF(D1:D20,"<1E100")

Or:

=SUMIF(D1:D20,"<#N/A")

--
Biff
Microsoft Excel MVP


"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Assume your to be summed data is in range D1:D20.
In D21 try the following Array-Firmula:
{=SUM(IF(ISNA(D1:D20),0,D1:D20))}
*** 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"


"Rechie" wrote:

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