Sum of cells when an #N/A is present

[Steve B]

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!

 

[Conan Kelly]

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

 

[Marcelo]

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:

 

[T. Valko]

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.