changing #N/A

[thomas tromp]

I have a lookup value that sometimes gives #N/A back.
But i need to sum those cells, cause sometimes the lookup
gives numbers back. The sum function doesn't do the job
now cause there are #N/A's in some cells.
Hoe do I solve this problem?

Thanks very much,

greetings
thomas tromp

 

[thomas]

On page 31 i found the solution.

 

[akyurek]

=SUMIF(Range,"#N/A")

 

[aj]

I used to have this problem.

my vlookup formula looked like this:

=VLOOKUP(A1,Data,2,False)

If the formula couldn't find a matching cell in
the 'Data' - it would result in #N/A.

I resolved this by amending my formula to read:

=IF(ISERROR(VLOOKUP(A1,Data,2,FALSE)),"",VLOOKUP
(A1,Data,2,FALSE))

basically, if my vlookup brings up "false", then fill the
cell with nothing, otherwise, fill the cell with the
result.

because nothing is being put in the cell, rather than
#n/a, then you can sum up the cells.

hope that makes sense.

AJ