T
tywlam
How do I sum the values in cells without adding those #N/A in some cells?
Please help.
tywl
Please help.
tywl
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
For summing Range A1:A10 use the below formula...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),"",A1:A10))
If this post helps click Yes
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),"",A1:A10))
If this post helps click Yes
J
Jacob Skaria
For summing Range A1:A10 use the below formula...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),"",A1:A10))
If this post helps click Yes
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),"",A1:A10))
If this post helps click Yes
T
tywlam
Thanks. But how can I get a result of a simple formula of adding different
cells (instead of a range) that have some #N/A?
For example: = a1+a3+b4+c5+f6, but b4 and f6 have #N/A !
Regards,
cells (instead of a range) that have some #N/A?
For example: = a1+a3+b4+c5+f6, but b4 and f6 have #N/A !
Regards,
T
tywlam
Thanks. But how can I get a result of a simple formula of adding different
cells (instead of a range) that have some #N/A?
For example: = a1+a3+b4+c5+f6, but b4 and f6 have #N/A !
Regards,
cells (instead of a range) that have some #N/A?
For example: = a1+a3+b4+c5+f6, but b4 and f6 have #N/A !
Regards,
J
Jarek Kujawa
another way:
=SUM(IF(ISNUMBER(A1:A10),A1:A10,""))
Ctrl+Shift+Enter
=SUM(IF(ISNUMBER(A1:A10),A1:A10,""))
Ctrl+Shift+Enter
J
Jarek Kujawa
another way:
=SUM(IF(ISNUMBER(A1:A10),A1:A10,""))
Ctrl+Shift+Enter
=SUM(IF(ISNUMBER(A1:A10),A1:A10,""))
Ctrl+Shift+Enter
J
Jacob Skaria
Try handling the error for these cells using
=IF(iserror(..
If this post helps click Yes
=IF(iserror(..
If this post helps click Yes
J
Jacob Skaria
Try handling the error for these cells using
=IF(iserror(..
If this post helps click Yes
=IF(iserror(..
If this post helps click Yes
T
tywlam
Couldn't solve my problem! I don't want to handle the cells with #N/A value
but the adding formula that can function correctly without being affected by
those #N/A cells!
but the adding formula that can function correctly without being affected by
those #N/A cells!
T
tywlam
Couldn't solve my problem! I don't want to handle the cells with #N/A value
but the adding formula that can function correctly without being affected by
those #N/A cells!
but the adding formula that can function correctly without being affected by
those #N/A cells!
J
Jacob Skaria
=IF(ISNA(A1),0,A1)+IF(ISNA(A3),0,A3)+IF(ISNA(B4),0,B4)+IF(ISNA(C5),0,C5)+IF(ISNA(F6),0,F6)
If this post helps click Yes
If this post helps click Yes
J
Jacob Skaria
=IF(ISNA(A1),0,A1)+IF(ISNA(A3),0,A3)+IF(ISNA(B4),0,B4)+IF(ISNA(C5),0,C5)+IF(ISNA(F6),0,F6)
If this post helps click Yes
If this post helps click Yes
J
Jarek Kujawa
"Couldn't solve my problem!"?
I DON'T think Jacob (and anyone) HAS to do it in the first place...
I DON'T think Jacob (and anyone) HAS to do it in the first place...
J
Jarek Kujawa
"Couldn't solve my problem!"?
I DON'T think Jacob (and anyone) HAS to do it in the first place...
I DON'T think Jacob (and anyone) HAS to do it in the first place...
T
tywlam
It works although I have to change the formula a lot!
Thanks a lot.
Thanks a lot.
T
tywlam
It works although I have to change the formula a lot!
Thanks a lot.
Thanks a lot.
M
Max
Fwiw, this also seems to work over a rectangular range, ignoring #N/As:
=SUMIF(A1:F6,"<>#N/A")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
=SUMIF(A1:F6,"<>#N/A")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
M
Max
Fwiw, this also seems to work over a rectangular range, ignoring #N/As:
=SUMIF(A1:F6,"<>#N/A")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
=SUMIF(A1:F6,"<>#N/A")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.