Remove #n/a in vlookup to sum results

[duketter]

Excel 2007 - I have vlookup formulas in cell c2 and d2. In c2 the result of
the vlookup is 5 and in d2 the results of the vlookup is #n/a. I then need
to sum cell c2 and d2 but I cannot since d2 contains #n/a. How can I
remove/change this so I can sum these two cells together?

Thanks!

 

[Jacob Skaria]

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

You can handle the #na in your VLOOKUP() formula like

=IF(ISNA(vlookupformula),0,vlookupformula)
or
=IF(ISNA(vlookupformula),"",vlookupformula)

If this post helps click Yes

 

[Max]

You could try something like this:
=SUMIF(C22,"<>#N/A")
which will ignore the error
Any good? Hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Per Jessen]

Hi

Use an if statement to check for the error like this:

=If(Iserror(Vlookup(.....)),0,Vlookup(…))

Regards,
Per

 

[Dave Peterson]

You could modify your formula so that it returns a 0 (or "") if there was no
match found.

In xl2007 only:
=iferror(vlookup(...),0)
or
=iferror(vlookup(...),"")

But you could use a different formula that =sum(C2:d2) or =c2+d2:
=sumif(C22,"<1e37")

1E37 is just a giant number (1 followed by 37 0's)

 

[duketter]

When I try the ISNA formula I get the following error:
"You've entered too many arguments for this function.

It highlights the zero in my formula

here is my formula:

=IF(ISNA(VLOOKUP(Users!E2,'2nd door pivot
table'!$A$29:$B$50,2,FALSE),0,VLOOKUP(Users!E2,'2nd door pivot
table'!$A$29:$B$50,2,FALSE))

 

[Jacob Skaria]

Try the below...ISNA() closing braces were missing

=IF(ISNA(VLOOKUP(Users!E2,'2nd door pivot able'!$A$29:$B$50,2,FALSE))
,0,VLOOKUP(Users!E2,'2nd door pivot table'!$A$29:$B$50,2,FALSE))

If this post helps click Yes