Summing lists that contain N/A without losing formulae

Shane

Board Regular
Joined
Apr 8, 2002
Messages
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
=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)
 
Upvote 0
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")
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top