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

[tywlam]

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

Please help.

tywl

 

[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

 

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

 

[Jarek Kujawa]

another way:

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

Ctrl+Shift+Enter

 

[Jacob Skaria]

Try handling the error for these cells using

=IF(iserror(..

If this post helps click Yes

 

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

 

[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

 

[Jarek Kujawa]

"Couldn't solve my problem!"?

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

 

[tywlam]

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

Thanks a lot.

 

[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