{=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