sum of cells that may contain #n/a

[Troy]

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?

 

[Jacob Skaria]

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

 

[Mike H]

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]

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

 

[Troy]

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]

Troy,

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

Mike

 

[Troy]

Sorry see below.


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

 

[ryguy7272]

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

 

[Mike H]

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

 

[Per Jessen]

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