Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 <atyahoo<dotcom ---- "Sailor" wrote in message ... 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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
ooops,
=if(iserror(vlookup(something), 0, vlookup(something)) correct: =if(iserror(vlookup(something)), 0, vlookup(something)) |
#5
|
|||
|
|||
Hi!
If you do want the #N/A's to display try this: =SUMIF(A1:L1,"<#N/A") Biff "Sailor" wrote in message ... 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. |
#6
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#7
|
|||
|
|||
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
Dynamic Print Range Help | Excel Worksheet Functions | |||
Define a range based on another named range | Excel Worksheet Functions | |||
named range refers to: in a chart | Excel Discussion (Misc queries) |