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

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.

DERICKSON Wrote:
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


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=573389

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

as an example

--
HTH

Bob Phillips

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

"DERICKSON" wrote in message
...
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

On Sat, 19 Aug 2006 05:35:01 -0700, DERICKSON
wrote:

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

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

i.e.

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

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

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

=SUM(SUMIF(Range,{"<0","0"}))

DERICKSON wrote:
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

Thank you!

"Aladin Akyurek" wrote:

=SUM(SUMIF(Range,{"<0","0"}))

DERICKSON wrote:
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

Thank you!

"starguy" wrote:


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.

DERICKSON Wrote:
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


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=573389

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" wrote:

On Sat, 19 Aug 2006 05:35:01 -0700, DERICKSON
wrote:

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

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

i.e.

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

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

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________