Sum formula result shows "#N/A"

[Dinesh]

Col "A" Col "B"
8/18/06 Vlookup formula (result is $5)
8/21/06 Vlookup formula (cell not updated yet so result shows
#N/A)
Sum() (result shows #N/A)

Col "B" is linked to another worksheet. 8/21/06 row will get the result of
"#N/A" because the vlookup formula won't get updated until 8/21/06. So the
sum total also gives me the result of "#N/A". How can I get the sum total
result of ($5) on Col "B" row 3..

Thanks.

Dinesh

 

[Trevor Shuttleworth]

Amend your VLOOKUP Formula to be:

=IF(ISNA(your VLOOKUP),0,your VLOOKUP)

So if the LOOKUP fails, you'll get 0 rather than #N/A! and your SUM should
work

Regards

Trevor

 

[Dave F]

You have to configure your vlookup formula to return a null or empty value
instead of N/A.

Example: IF(ISERROR(VLOOKUP([your criteria])),"",VLOOKUP([your criteria]))

Right now since your vlookup returns an error, when you go to sum that
column ovf VLOOKUPS, SUM returns an error as well.

 

[Aladin Akyurek]

=SUM(SUMIF(B:B,{"<0",">0"}))