sum of cells that may contain #n/a
 

If I am summing cells that may or may not contain an error (#n/a) is there a
way to ignore these cells? Or replace (#n/a) with a zero?

sum of cells that may contain #n/a
 

Try the below

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

=SUM(IF(ISNA(A1:A10),0,A1:A10))


If this post helps click Yes
---------------
Jacob Skaria


"Troy" wrote:

If I am summing cells that may or may not contain an error (#n/a) is there a
way to ignore these cells? Or replace (#n/a) with a zero?

sum of cells that may contain #n/a
 

Hi,

If all the numbers are positive you can get away with

=SUMIF(A1:A20,"0")

if there may be negatives in the range use

=SUM(SUMIF(A1:A20,{"<0","0"}))

Mike

"Troy" wrote:

If I am summing cells that may or may not contain an error (#n/a) is there a
way to ignore these cells? Or replace (#n/a) with a zero?

sum of cells that may contain #n/a
 

doesn't seem to be working. Does it matter that the cells I'm summing are
scattered all over my worksheet?

"Troy" wrote:

If I am summing cells that may or may not contain an error (#n/a) is there a
way to ignore these cells? Or replace (#n/a) with a zero?

sum of cells that may contain #n/a
 

That's not working.

I still want a sum if only one of the many cells I'm summing as a number
value in it.

"Mike H" wrote:

Hi,

If all the numbers are positive you can get away with

=SUMIF(A1:A20,"0")

if there may be negatives in the range use

=SUM(SUMIF(A1:A20,{"<0","0"}))

Mike

"Troy" wrote:

If I am summing cells that may or may not contain an error (#n/a) is there a
way to ignore these cells? Or replace (#n/a) with a zero?

sum of cells that may contain #n/a
 

Troy,

'doesn't seem to be working' isn't a very useful description of the problem,
post the formula you tried.

Mike

"Troy" wrote:

doesn't seem to be working. Does it matter that the cells I'm summing are
scattered all over my worksheet?

"Troy" wrote:

If I am summing cells that may or may not contain an error (#n/a) is there a
way to ignore these cells? Or replace (#n/a) with a zero?

sum of cells that may contain #n/a
 

Sorry see below.


=SUMIF(H504,H488,H466,"0")




"Mike H" wrote:

Troy,

'doesn't seem to be working' isn't a very useful description of the problem,
post the formula you tried.

Mike

"Troy" wrote:

doesn't seem to be working. Does it matter that the cells I'm summing are
scattered all over my worksheet?

"Troy" wrote:

If I am summing cells that may or may not contain an error (#n/a) is there a
way to ignore these cells? Or replace (#n/a) with a zero?

sum of cells that may contain #n/a
 

Maybe:
=SUM(IF(ISERROR(A1:A6),0,A1:A6))

Enter it with Ctrl+Shift+Enter all at the same time, not just Enter.

Or:
=SUM(IF(ISNUMBER(A1:A6),A1:A6,0))
Same as above; Ctrl+Shift+Enter

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Troy" wrote:

Sorry see below.


=SUMIF(H504,H488,H466,"0")




"Mike H" wrote:

Troy,

'doesn't seem to be working' isn't a very useful description of the problem,
post the formula you tried.

Mike

"Troy" wrote:

doesn't seem to be working. Does it matter that the cells I'm summing are
scattered all over my worksheet?

"Troy" wrote:

If I am summing cells that may or may not contain an error (#n/a) is there a
way to ignore these cells? Or replace (#n/a) with a zero?

sum of cells that may contain #n/a
 

Troy,

There has to be a better way but in the meantime try this array formula

=SUM(IF(ISNA(H466),,H466))+SUM(IF(ISNA(H488),,H488 ))+SUM(IF(ISNA(H504),,H504))


This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike
"Troy" wrote:

Sorry see below.


=SUMIF(H504,H488,H466,"0")




"Mike H" wrote:

Troy,

'doesn't seem to be working' isn't a very useful description of the problem,
post the formula you tried.

Mike

"Troy" wrote:

doesn't seem to be working. Does it matter that the cells I'm summing are
scattered all over my worksheet?

"Troy" wrote:

If I am summing cells that may or may not contain an error (#n/a) is there a
way to ignore these cells? Or replace (#n/a) with a zero?

sum of cells that may contain #n/a
 

Hi

I would go for your second option:

Or replace (#n/a) with a zero?

In excel 2007 use an "IfError" formula in the cells to sum like this:

=IfError(A1/B1,0)

Which will return a the result of A1/B1 or 0 if the formula return an error.

In previous versions use this:

=IF(ISERROR(A1/B1),0,A1/B1)

Hopes this helps.
....
Per