Sum of cells when an #N/A is present
 

Greetings,

I have a VLOOKUP and some of the results are #N/A as there is no data for
that cell. I need to sum the rows of cells, including those with #N/A (those
values would then be 0).When I try to add the results those error cells do
not let me add up the other cells with real values.


What is the best way to do this? I am okay with adding another column if
needed

Thank you in advance for your advice!

Sum of cells when an #N/A is present
 

Steve B,

Add error traping to your VLOOKUP formulas.

Change your VLOOKUP formula to this:

=IF(ISNA(VLOOKUP(lookup value,lookup table,column,match)),0,VLOOKUP(lookup
value,lookup table,column,match))

OR

=IF(ISNA(VLOOKUP(lookup value,lookup table,column,match)),"",VLOOKUP(lookup
value,lookup table,column,match))

Now, if your VLOOKUP results in an #N/A, a 0 or "" will be put in it's
place, allowing your other formulas based on this column to work.

HTH,

Conan




"Steve B" wrote in message
...
Greetings,

I have a VLOOKUP and some of the results are #N/A as there is no data for
that cell. I need to sum the rows of cells, including those with #N/A
(those
values would then be 0).When I try to add the results those error cells do
not let me add up the other cells with real values.


What is the best way to do this? I am okay with adding another column if
needed

Thank you in advance for your advice!

Sum of cells when an #N/A is present
 

hi,

use if(iserror(vlookup(your formula),0,(vlookup(your formula))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Steve B" escreveu:

Greetings,

I have a VLOOKUP and some of the results are #N/A as there is no data for
that cell. I need to sum the rows of cells, including those with #N/A (those
values would then be 0).When I try to add the results those error cells do
not let me add up the other cells with real values.


What is the best way to do this? I am okay with adding another column if
needed

Thank you in advance for your advice!

Sum of cells when an #N/A is present
 

Try this:

=SUMIF(A1:A10,"<#N/A")

However, the best solution is to correct the lookup formulas so they don't
return errors in the first place. OTOH, you may *need* these errors for
charting purposes, or, you may need to see errors when they happen.

--
Biff
Microsoft Excel MVP


"Steve B" wrote in message
...
Greetings,

I have a VLOOKUP and some of the results are #N/A as there is no data for
that cell. I need to sum the rows of cells, including those with #N/A
(those
values would then be 0).When I try to add the results those error cells do
not let me add up the other cells with real values.


What is the best way to do this? I am okay with adding another column if
needed

Thank you in advance for your advice!