sum numbers and ignore text

[ditchy]

Hi there, I need some help please. I have a row of numbers and text
and I need to sum the numbers only. How to do?
a1 a2 a3 a4 a5
1 dnr 3 4 dnr total 8
thanks in anticipation
ditchy

 

[DDM]

Ditchy, a simple SUM formula will do it, since the SUM worksheet function
ignores text values. So in your example, it's =SUM(A1:A5).

 

[ditchy]

Ditchy, a simple SUM formula will do it, since the SUM worksheet function
ignores text values. So in your example, it's =SUM(A1:A5).

-- thank you for the reply,

have used the =sum (A1:AL1) with no success, it brings up the wrong
total. some of the cells have text and some have numbers,
have formated as text, then numbers then general but the same result?
=sum results is 35 and true answer is 51.
I do have some cells with no data could that be the problem?
thank you

 

[Frank Kabel]

Hi
sounds like some of your values are actually stored as 'Text'. Try the
following:
- first format the data range as 'General'
- select an empty cell and copy this cell
- select your numeric values
- goto 'Edit - Paste Special' and choose 'Add'

now try your formula again

 

[ditchy]

Hi
sounds like some of your values are actually stored as 'Text'. Try the
following:
- first format the data range as 'General'
- select an empty cell and copy this cell
- select your numeric values
- goto 'Edit - Paste Special' and choose 'Add'

now try your formula again

Thank you Frank

worked perfectly and solved my problem
much appreciated
Ditchy