Sum of cells, where there are not errors in them

[Ivan]

Hello

In a column A1:A100 I have formulas, where the results are numeric number
or an error of type #REF!. I would like to have a sum of all cells, where
there not an error in them and I would like to have this sum in one cell
(with only one formula - somehow SUMIF ?)?

Example for cells A1:A7:
--------------------
A B
1 3
2 4
3 1
4 #REF!
5 2
6 #REF!
7 5
--------------------

Sum = 3 + 4 +1 +2 + 5 = 15

Any idea?


Ivan

 

[Francis]

try
=SUMIF(A2:A100,"<1E100") or

an array formula, need to confirm by Ctrl, Shift and Enter
=SUM(IF(ISNUMBER(A2:A100),A2:A100))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Mike H]

Hi,

I wouldn't start from there. i'd get rid of the errors but to sum a range
with those errors try this array formula

=SUM(IF(ISERROR(A1:A7)=FALSE,A1:A7))

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

 

[Teethless mama]

=SUMIF(A:A,"<>#REF!")

 

[T. Valko]

Yet another one:

=SUMIF(A1:A7,"<1E100")

 

[T. Valko]

Ooops!

I see this version was already suggested.

 

[Ivan]

Don't work - try!

=SUMIF(A:A,"<>#REF!")

 

[Ivan]

Thank you Francis!

Ivan

try
=SUMIF(A2:A100,"<1E100") or

an array formula, need to confirm by Ctrl, Shift and Enter
=SUM(IF(ISNUMBER(A2:A100),A2:A100))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Ivan]

Thank you Mike

Ivan

Hi,

I wouldn't start from there. i'd get rid of the errors but to sum a range
with those errors try this array formula

=SUM(IF(ISERROR(A1:A7)=FALSE,A1:A7))

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