How do I sum a range which includes the "#N/A" VLOOKUP return valu

Excell 2002

I am using numerous VLOOKUP functions (including the "FALSE" option) accross
a row to extract exact values from the same range, which may or may not
contain the look-up value. I then want to sum accross the row to give me a
running total. The problem is that VLOOKUP returns "#N/A" when an exact
match is not found; which then returns a "#N/A" error in the sum function.

Hi Sailor,

> I am using numerous VLOOKUP functions (including the "FALSE" option)
> accross a row to extract exact values from the same range, which may
> or may not contain the look-up value. I then want to sum accross the
> row to give me a running total. The problem is that VLOOKUP returns
> "#N/A" when an exact match is not found; which then returns a "#N/A"
> error in the sum function.

you have to avoid #n/a with a formula like

=if(iserror(vlookup(something), 0, vlookup(something))

here the " 0 " is shown instead of the error, this will allow you to
sum up everything. Instead of " 0 " you can use anything that will
allow the sum-function to work (eg. texts like "not available", "---",
"", etc).

arno

Instead of using say, in B1: = VLOOKUP(A1,Sheet2!A:B,2,0)

Use an " =IF(ISNA(VLOOKUP(...)),0, VLOOKUP(...))"
error-trap construct to return zeroes for any non-matches instead of #NAs.

For example, you could use in B1:

=IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0,VLOOKUP(A1,Sheet2!A:B,2,0))

Downstream SUMs, etc will now work ok.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Sailor" <[email protected]> wrote in message
news:[email protected]...
> Excell 2002
>
> I am using numerous VLOOKUP functions (including the "FALSE" option)
accross
> a row to extract exact values from the same range, which may or may not
> contain the look-up value. I then want to sum accross the row to give me
a
> running total. The problem is that VLOOKUP returns "#N/A" when an exact
> match is not found; which then returns a "#N/A" error in the sum function.

ooops,
> =if(iserror(vlookup(something), 0, vlookup(something))

correct:
=if(iserror(vlookup(something)), 0, vlookup(something))

Hi!

If you do want the #N/A's to display try this:

=SUMIF(A1:L1,"<>#N/A")

Biff

"Sailor" <[email protected]> wrote in message
news:[email protected]...
> Excell 2002
>
> I am using numerous VLOOKUP functions (including the "FALSE" option)
> accross
> a row to extract exact values from the same range, which may or may not
> contain the look-up value. I then want to sum accross the row to give me
> a
> running total. The problem is that VLOOKUP returns "#N/A" when an exact
> match is not found; which then returns a "#N/A" error in the sum function.

Alternatively, instead of correcting the source VLOOKUP returns
you could also try an array " SUM(IF(ISNUMBER(...), ... )" formula,
instead of the normal SUM formula

E.g.: instead of say, in E1: =SUM(C1:D1)

Put in E1, and array-enter (press CTRL+SHIFT+ENTER):
=SUM(IF(ISNUMBER(C1:D1),C1:D1))
which will ignore any "#NA" returns in C1:D1

Then just fill E1 down, as per normal
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Sailor wrote:
> Excell 2002
>
> I am using numerous VLOOKUP functions (including the "FALSE" option) accross
> a row to extract exact values from the same range, which may or may not
> contain the look-up value. I then want to sum accross the row to give me a
> running total. The problem is that VLOOKUP returns "#N/A" when an exact
> match is not found; which then returns a "#N/A" error in the sum function.

=SUMIF(Range,"<>#N/A")