sum column with na from v lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DERICKSON - 19 Aug 2006 13:35 GMT

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

Reply to this Message

starguy - 19 Aug 2006 13:57 GMT

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.

DERICKSON Wrote:

> The column I am trying to sum contains the formula
> =VLOOKUP(B620,'S:\Accounting\Donna\SAP Go Live\[Go Live Inventor
[quoted text clipped - 11 lines]
>
> Donn

Reply to this Message

DERICKSON - 20 Aug 2006 12:58 GMT

Thank you!

> if all values are positive then simply use SUMIF function
> =SUMIF(A5:A100,">0")
[quoted text clipped - 18 lines]
> >
> > Donna

Reply to this Message

Bob Phillips - 19 Aug 2006 14:55 GMT

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

as an example

Signature

HTH

Bob Phillips

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

> The column I am trying to sum contains the formula
> =VLOOKUP(B620,'S:\Accounting\Donna\SAP Go Live\[Go Live Inventory Download
[quoted text clipped - 6 lines]
>
> Donna

Reply to this Message

Richard Buttrey - 19 Aug 2006 15:19 GMT

>The column I am trying to sum contains the formula
>=VLOOKUP(B620,'S:\Accounting\Donna\SAP Go Live\[Go Live Inventory Download
[quoted text clipped - 6 lines]
>
>Donna

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
__________________________

Reply to this Message

DERICKSON - 20 Aug 2006 13:01 GMT

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!

> >The column I am trying to sum contains the formula
> >=VLOOKUP(B620,'S:\Accounting\Donna\SAP Go Live\[Go Live Inventory Download
[quoted text clipped - 21 lines]
> Grappenhall, Cheshire, UK
> __________________________

Reply to this Message

Aladin Akyurek - 19 Aug 2006 16:06 GMT

=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
[quoted text clipped - 6 lines]
>
> Donna

Reply to this Message

DERICKSON - 20 Aug 2006 12:28 GMT

Thank you!

> =SUM(SUMIF(Range,{"<0",">0"}))
>
[quoted text clipped - 8 lines]
> >
> > Donna

Reply to this Message