Sum formula result shows "#N/A"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dinesh - 18 Aug 2006 18:40 GMT

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

Reply to this Message

Trevor Shuttleworth - 18 Aug 2006 18:44 GMT

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

> Col "A" Col "B"
> 8/18/06 Vlookup formula (result is $5)
[quoted text clipped - 11 lines]
>
> Dinesh

Reply to this Message

Dave F - 18 Aug 2006 18:46 GMT

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.

Signature

Brevity is the soul of wit.

> Col "A" Col "B"
> 8/18/06 Vlookup formula (result is $5)
[quoted text clipped - 10 lines]
>
> Dinesh

Reply to this Message

Aladin Akyurek - 19 Aug 2006 16:03 GMT

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

> Col "A" Col "B"
> 8/18/06 Vlookup formula (result is $5)
[quoted text clipped - 10 lines]
>
> Dinesh

Reply to this Message