Thanks:  0
Likes:  0

# Thread: Summing lists that contain N/A without losing formulae

1. 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?

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

3. 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

4. =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)

5. 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")

6. 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?

7. 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 ]

8. 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.

9. Shane, see my latest posting above!

10. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•