sum numbers and ignore text

D

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
 
D

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).
 
D

ditchy

DDM said:
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,
Click to expand...
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
 
F

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
 
D

ditchy

Frank Kabel said:
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
Click to expand...
worked perfectly and solved my problem
much appreciated
Ditchy
 
Top