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?

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

-----Original Message-----
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?
.

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

HTH
Jason
Atlanta, GA

-----Original Message-----
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?
.

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.

--
Regards
Roger Govier
"Daniel R" <Daniel wrote in message
...
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?