If there is no option to tell it what to put in the vlookup when it fails then how a bout this...
make an extra blank column. Make an if statement that fills the column with the same values as the old column.
Like :
If a1 = #n/a then 0 else a1.
Think it'd go like this, if(a1="#n/a",0,a1)
for the #n/a you just type in the text '#n/a'
So when it sees the #n/a it puts a zero and when it sees the number it just copies the number to the new column. Then add up that column or hide it and add it up under the correct column.
:hail
EDIT:
Also, if you format the column for numbers only, does it put the #n/a when it comes up with a bad answer or does it put 'zero'? I cant remember off hand.
make an extra blank column. Make an if statement that fills the column with the same values as the old column.
Like :
If a1 = #n/a then 0 else a1.
Think it'd go like this, if(a1="#n/a",0,a1)
for the #n/a you just type in the text '#n/a'
So when it sees the #n/a it puts a zero and when it sees the number it just copies the number to the new column. Then add up that column or hide it and add it up under the correct column.
:hail
EDIT:
Also, if you format the column for numbers only, does it put the #n/a when it comes up with a bad answer or does it put 'zero'? I cant remember off hand.