Summing lists that contain N/A without losing formulae

Shane · Apr 9th, 2002, 08:29 AM

Hi! This is probably a common problem but I don't know the answer.

I have a list of values, each based upon a lookup to another worksheet. Some of the values returned are good, others are N/A when no value is found. In order to sum this list, I typically past value these cells and replace "N/A" with "0", thus enabling the sum function.

Is there a way to sum this list while keeping the lookup formulae, thereby avoiding the copy value & replace method I currently use?

Anne Troy · Apr 9th, 2002, 08:40 AM

=if(isna(myvlookupformula),0,myvlookupformula)

Mark W. · Apr 9th, 2002, 08:42 AM

Quote:

On 2002-04-09 07:29, Shane wrote:

Is there a way to sum this list while keeping the lookup formulae, thereby avoiding the copy value & replace method I currently use?

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

where "range" is the cell range of your list

Andrew70 · Apr 9th, 2002, 08:42 AM

=ISNA(A1) tests cell A1 for the NA error and will return a true or false answer.

Substitute A1 with your current formula to test its result.

=IF(ISNA(YOURFORMULA)=TRUE,0,YOURFORMULA)

Dave Patton · Apr 9th, 2002, 08:48 AM

Try =SUMIF(A2:A11,"<>#N/A")

or

Array enter with Ctrl-Shift-Enter (CSE)

=SUM(IF(ISERROR(A1:A10),"",A1:A10))
Array enter with Ctrl-Shift-Enter (CSE)

Edit the references as necessary.
With the array formula, you must Array enter after edit as well.
or try

=SUMIF(A2:A11,"<>#N/A")

Shane · Apr 9th, 2002, 08:53 AM

Sorry guys, I misled you a little. I am actually using the formula "LARGE" rather than "SUM".

Based on your advise, I tried using the formula =LARGEIF($C2:$I2,1,"<>N/A") but that yields the result #NAME?

What would you recommend I try?

Mark W. · Apr 9th, 2002, 09:01 AM

Quote:

On 2002-04-09 07:53, Shane wrote:
Sorry guys, I misled you a little. I am actually using the formula "LARGE" rather than "SUM".

Based on your advise, I tried using the formula =LARGEIF($C2:$I2,1,"<>N/A") but that yields the result #NAME?

What would you recommend I try?

{=LARGE(IF(ISNUMBER(C2:I2),C2:I2),1)}

or

{=MAX(IF(ISNUMBER(C2:I2),C2:I2))}

Note: These are array formulas which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

[ This Message was edited by: Mark W. on 2002-04-09 08:03 ]

Shane · Apr 9th, 2002, 09:14 AM

Also, I tried the formula

=IF(ISNA(LARGE($C2:$I2,1))=TRUE,0,LARGE($C2:$I2,1))

but it returns “0” which is not the correct result.

Mark W. · Apr 9th, 2002, 09:15 AM

Shane, see my latest posting above!

Shane · Apr 9th, 2002, 09:18 AM

Also, I tried the formula

=IF(ISNA(LARGE($C2:$I2,1))=TRUE,0,LARGE($C2:$I2,1))

but it returns “0” which is not the correct result.

Apr 9th, 2002, 08:40 AM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	=if(isna(myvlookupformula),0,myvlookupformula) __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 08:29 AM	#1
Shane Board Regular Join Date: Apr 2002 Location: Dublin, Ireland Posts: 51	Hi! This is probably a common problem but I don't know the answer. I have a list of values, each based upon a lookup to another worksheet. Some of the values returned are good, others are N/A when no value is found. In order to sum this list, I typically past value these cells and replace "N/A" with "0", thus enabling the sum function. Is there a way to sum this list while keeping the lookup formulae, thereby avoiding the copy value & replace method I currently use?

Apr 9th, 2002, 08:42 AM	#4
Andrew70 New Member Join Date: Apr 2002 Posts: 30	=ISNA(A1) tests cell A1 for the NA error and will return a true or false answer. Substitute A1 with your current formula to test its result. =IF(ISNA(YOURFORMULA)=TRUE,0,YOURFORMULA)

Apr 9th, 2002, 08:48 AM	#5
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	Try =SUMIF(A2:A11,"<>#N/A") or Array enter with Ctrl-Shift-Enter (CSE) =SUM(IF(ISERROR(A1:A10),"",A1:A10)) Array enter with Ctrl-Shift-Enter (CSE) Edit the references as necessary. With the array formula, you must Array enter after edit as well. or try =SUMIF(A2:A11,"<>#N/A")

Apr 9th, 2002, 08:53 AM	#6
Shane Board Regular Join Date: Apr 2002 Location: Dublin, Ireland Posts: 51	Sorry guys, I misled you a little. I am actually using the formula "LARGE" rather than "SUM". Based on your advise, I tried using the formula =LARGEIF($C2:$I2,1,"<>N/A") but that yields the result #NAME? What would you recommend I try?

Apr 9th, 2002, 09:14 AM	#8
Shane Board Regular Join Date: Apr 2002 Location: Dublin, Ireland Posts: 51	Also, I tried the formula =IF(ISNA(LARGE($C2:$I2,1))=TRUE,0,LARGE($C2:$I2,1)) but it returns “0” which is not the correct result.

Apr 9th, 2002, 09:15 AM	#9
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Shane, see my latest posting above!

Apr 9th, 2002, 09:18 AM	#10
Shane Board Regular Join Date: Apr 2002 Location: Dublin, Ireland Posts: 51	Also, I tried the formula =IF(ISNA(LARGE($C2:$I2,1))=TRUE,0,LARGE($C2:$I2,1)) but it returns “0” which is not the correct result.