T
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?
way to ignore these cells? Or replace (#n/a) with a zero?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
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
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
M
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
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
T
Troy
doesn't seem to be working. Does it matter that the cells I'm summing are
scattered all over my worksheet?
scattered all over my worksheet?
T
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.
I still want a sum if only one of the many cells I'm summing as a number
value in it.
M
Mike H
Troy,
'doesn't seem to be working' isn't a very useful description of the problem,
post the formula you tried.
Mike
'doesn't seem to be working' isn't a very useful description of the problem,
post the formula you tried.
Mike
T
Troy
Sorry see below.
=SUMIF(H504,H488,H466,">0")
=SUMIF(H504,H488,H466,">0")
R
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---
=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---
M
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
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
P
Per Jessen
Hi
I would go for your second option:
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
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