adding cells that contain formulas that have returned error messag

[Guest]

I have a spreadsheet taht contains many v-lookups. When no match is
returned, it displays a #n/a. If this is displayed in a range that is sumed
up, if causes the sum to also appear as #n/a. The sum function will ignore
actual text in it's calculation, but not this "error" message. How do I make
it so that the "#n/a" value will not stop SUM formulas from adding the cells
that did return a numerical value?

 

[Biff]

Hi!

Change your lookup formulas to return "" or zero if the
lookup value is not found then SUM will work.

If you want #N/A to be displayed:

If the values are always positive:

=SUMIF(A1:A10,">0")

If the values can be negative (array entered with the key
combo of DTRL,SHIFT,ENTER)

=SUM(IF(ISNUMBER(A1:A10),A1:A10))

Biff

 

[Jason Morin]

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

HTH
Jason
Atlanta, GA

 

[Roger Govier]

Hi Daniel

Try wrapping your current vlookup formula within a test for N/A
=IF(ISNA(your_formula),"",(your_formula))

This will put a null character in place of the #n/a and your Sum should
work.
Alternatively, you could put a 0 rather than the "" between the commas in
the formula to put zeros in the cells.