sum column with na from v lookup

D

DERICKSON

The column I am trying to sum contains the formula
=VLOOKUP(B620,'S:\Accounting\Donna\SAP Go Live\[Go Live Inventory Download
080806 Rec to 1550 081706 dje.xls]1550 not loaded final'!$B$3:$F$125,5,FALSE)

Is there a Sum Formula I can write that excludes the #N/A values which are
showing in the column as a result of the vlookup? I would like to find a sum
function so that I do not have to use the auto filter on my large spreadsheet
and the sort it would require.

Donna
 
S

starguy

if all values are positive then simply use SUMIF function
=SUMIF(A5:A100,">0")
i supposed that your data which you want to sum is in the range o
A5:A100
you can adjust this range according to your data.
The column I am trying to sum contains the formula
=VLOOKUP(B620,'S:\Accounting\Donna\SAP Go Live\[Go Live Inventor
Download
080806 Rec to 1550 081706 dje.xls]1550 not loade
final'!$B$3:$F$125,5,FALSE)

Is there a Sum Formula I can write that excludes the #N/A values whic
are
showing in the column as a result of the vlookup? I would like to fin
a sum
function so that I do not have to use the auto filter on my larg
spreadsheet
and the sort it would require.

Donn
Click to expand...
 
B

Bob Phillips

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

as an example

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
D

DERICKSON

Thank you!

Aladin Akyurek said:
=SUM(SUMIF(Range,{"<0",">0"}))
The column I am trying to sum contains the formula
=VLOOKUP(B620,'S:\Accounting\Donna\SAP Go Live\[Go Live Inventory Download
080806 Rec to 1550 081706 dje.xls]1550 not loaded final'!$B$3:$F$125,5,FALSE)

Is there a Sum Formula I can write that excludes the #N/A values which are
showing in the column as a result of the vlookup? I would like to find a sum
function so that I do not have to use the auto filter on my large spreadsheet
and the sort it would require.

Donna
Click to expand...
Click to expand...
 
D

DERICKSON

Thank you!

starguy said:
if all values are positive then simply use SUMIF function
=SUMIF(A5:A100,">0")
i supposed that your data which you want to sum is in the range of
A5:A100
you can adjust this range according to your data.
The column I am trying to sum contains the formula
=VLOOKUP(B620,'S:\Accounting\Donna\SAP Go Live\[Go Live Inventory
Download
080806 Rec to 1550 081706 dje.xls]1550 not loaded
final'!$B$3:$F$125,5,FALSE)

Is there a Sum Formula I can write that excludes the #N/A values which
are
showing in the column as a result of the vlookup? I would like to find
a sum
function so that I do not have to use the auto filter on my large
spreadsheet
and the sort it would require.

Donna
Click to expand...
Click to expand...
 
D

DERICKSON

l'll try this next time I do a vlookup when I do not need to also see the
value of -0-. I actually do need to see the #N/A for comparison reasons in
this spreadsheet.

I will have many other times this will be useful. Thank you!

Richard Buttrey said:
The column I am trying to sum contains the formula
=VLOOKUP(B620,'S:\Accounting\Donna\SAP Go Live\[Go Live Inventory Download
080806 Rec to 1550 081706 dje.xls]1550 not loaded final'!$B$3:$F$125,5,FALSE)

Is there a Sum Formula I can write that excludes the #N/A values which are
showing in the column as a result of the vlookup? I would like to find a sum
function so that I do not have to use the auto filter on my large spreadsheet
and the sort it would require.

Donna
Click to expand...

Try wrapping your lookup formulae in an If(Iserror) formula.

i.e.

=IF(ISERROR(Vlookup(yourextremelylongformula)),0,Vlookup(yourextremelylongformula))

Then you won't have any #N/As and a Sum will work fine.

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
Click to expand...
 
Top