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!

Try
=SUMIF(C2:D2,"<#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
---------------
Jacob Skaria


"duketter" wrote:

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!

You could try something like this:
=SUMIF(C2:D2,"<#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
---
"duketter" wrote:
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?

Hi

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

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

Regards,
Per


On 15 Sep., 15:47, duketter
wrote:
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!

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(C2:D2,"<1e37")

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



duketter wrote:

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!

--

Dave Peterson

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,'2n d door pivot
table'!$A$29:$B$50,2,FALSE))




"Jacob Skaria" wrote:

Try
=SUMIF(C2:D2,"<#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
---------------
Jacob Skaria


"duketter" wrote:

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!

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
---------------
Jacob Skaria


"duketter" wrote:

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,'2n d door pivot
table'!$A$29:$B$50,2,FALSE))




"Jacob Skaria" wrote:

Try
=SUMIF(C2:D2,"<#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
---------------
Jacob Skaria


"duketter" wrote:

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!