How do I sum cells and ignore text

[bucci35]

I have cells A1 through A10 which can contain numbers or text. I want to be able to sum this range while always ignoring the ones with text so as to eliminate the error when summing.

Any ideas?

 

[VoG]

=SUM(A1:A10)

Excel will ignore the non-numeric entries.

 

[bucci35]

I get value error when their is a non numeric in one of the cells?

 

[VoG]

Can you post the values that you have in A1:A10 please?

 

[bucci35]

Sorry,

I see why. It's because I'm attempting to subrtract and/or multiply cells when some have text in them, then I add this column up.
So, the question now is if one of 2 cells that I am subracting and/or multiplying by a factor contains text, how do I get around it?

 

[VoG]

Use a formula like

=IF(AND(ISNUMBER(F1),ISNUMBER(G1)),F1*G1,"")

which will leave the result cell blank if either of the values that you are trying to multiply is non-numeric.

 

[barry houdini]

Can you post the formula that gives you a #VALUE error....and indicate which cells might contain text?

In general you could probably use a formula like

=IF(ISERR(Your_formula),"",Your_formula)

....although you might be able to simplify that if only certain cells can contain text.

Alternatively you can sum C1:C10, while ignoring errors with

=SUM(SUMIF(C1:C10,{">0","<0"}))

 

[bucci35]

The 2 cells I am calculating are formatted as a date, should I be using some other phrase instead of isnumber?

 

[barry houdini]

Dates are simply formatted numbers in Excel so ISNUMBER should work just fine....although you might like to describe exactly what you are tring to achieve and what formula you are currently using

 

[Giordano Bruno]

I don't know if this is relevant to your problem, but I frequently find text substututed for numbers and the easiest way to avoid the problem is to use the SUM function as it ignores text.

Instead of =A1*B1, use =sum(A1)*sum(B1). This will give you a zero in the event that either cell contains text.