Sum in rows or columns that have #N/A in some cells!
 

How do I sum the values in cells without adding those #N/A in some cells?

Please help.

tywl

Sum in rows or columns that have #N/A in some cells!
 

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


"tywlam" wrote:

How do I sum the values in cells without adding those #N/A in some cells?

Please help.

tywl

Sum in rows or columns that have #N/A in some cells!
 

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,


"Jacob Skaria" wrote:

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


"tywlam" wrote:

How do I sum the values in cells without adding those #N/A in some cells?

Please help.

tywl

Sum in rows or columns that have #N/A in some cells!
 

another way:

=SUM(IF(ISNUMBER(A1:A10),A1:A10,""))

Ctrl+Shift+Enter



On 22 Maj, 09:46, Jacob Skaria
wrote:
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
---------------
Jacob Skaria



"tywlam" wrote:
How do I sum the values in cells without adding those #N/A in some cells?

Please help.

tywl- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Sum in rows or columns that have #N/A in some cells!
 

Try handling the error for these cells using

=IF(iserror(..

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


"tywlam" wrote:

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,


"Jacob Skaria" wrote:

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


"tywlam" wrote:

How do I sum the values in cells without adding those #N/A in some cells?

Please help.

tywl

Sum in rows or columns that have #N/A in some cells!
 

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!



"Jacob Skaria" wrote:

Try handling the error for these cells using

=IF(iserror(..

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


"tywlam" wrote:

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,


"Jacob Skaria" wrote:

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


"tywlam" wrote:

How do I sum the values in cells without adding those #N/A in some cells?

Please help.

tywl

Sum in rows or columns that have #N/A in some cells!
 

=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
---------------
Jacob Skaria


"tywlam" wrote:

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!



"Jacob Skaria" wrote:

Try handling the error for these cells using

=IF(iserror(..

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


"tywlam" wrote:

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,


"Jacob Skaria" wrote:

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


"tywlam" wrote:

How do I sum the values in cells without adding those #N/A in some cells?

Please help.

tywl

Sum in rows or columns that have #N/A in some cells!
 

"Couldn't solve my problem!"?

I DON'T think Jacob (and anyone) HAS to do it in the first place...



On 22 Maj, 11:24, tywlam wrote:
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!



"Jacob Skaria" wrote:
Try handling the error for these cells using

=IF(iserror(..

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

"tywlam" wrote:

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,

"Jacob Skaria" wrote:

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

"tywlam" wrote:

How do I sum the values in cells without adding those #N/A in some cells?

Please help.

tywl- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Sum in rows or columns that have #N/A in some cells!
 

It works although I have to change the formula a lot!

Thanks a lot.


"Jacob Skaria" wrote:

=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
---------------
Jacob Skaria


"tywlam" wrote:

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!



"Jacob Skaria" wrote:

Try handling the error for these cells using

=IF(iserror(..

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


"tywlam" wrote:

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,


"Jacob Skaria" wrote:

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


"tywlam" wrote:

How do I sum the values in cells without adding those #N/A in some cells?

Please help.

tywl

Sum in rows or columns that have #N/A in some cells!
 

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
---
"tywlam" wrote:
It works although I have to change the formula a lot!