How can I sum a range that some cells have text and numbers.

[TomA]

How can I sum all the values in a range of cells where the cells contain either a number or text and a number. Please note the following example?

C17=9
D17=4
E17=8TD
F17=3TP
G17=7

I want the total of 31 but excel will ignore the cells with text.

Can you help me please?
Thanks

 

[Erdinç E. Karaçam]

Re: How can I sum a range that some cells have text and numb

How can I sum all the values in a range of cells where the cells contain either a number or text and a number. Please note the following example?

C17=9
D17=4
E17=8TD
F17=3TP
G17=7

I want the total of 31 but excel will ignore the cells with text.

Can you help me please?
Thanks

Hi TomA,

Type this codes to Module1:

Function Split_Digits(CeL)
   Dim i As Integer
   For i = 1 To Len(CeL)
      DiGiT = Mid(CeL, i, 1)
      If IsNumeric(DiGiT) = True Then
         Split_Digits = Split_Digits & DiGiT
         Split_Digits = Split_Digits * 1
      End If
   Next i
End Function

Then;

C18 and slide to right the formula to G18

=Split_Digits(C17)

And for example to C19:

=SUM(C18:G18)

Example table:

<table border=1><tr><td bgcolor=#CCCCFF align=left valign=center><font size=1 color=blue face=Tahoma>Excel Sürümü:</font><font color=#660066 size=1 face=Tahoma> Excel 2003 ..ı|ı.. </font><font size=1 color=blue face=Tahoma>İşletim Sistemi:</font><font size=1 color=#660066 face=Tahoma> XP Pro</font></td></tr><tr valign=top><td><table border=0 bgcolor=Black cellspacing=1 cellpadding=1 align=center><tr align=center valign=center bgcolor=white><td bgcolor=Black align=center><font color=white size=2></font></td><td bgcolor=Black width=60><font color=red size=1 face=Tahoma>C</font></td><td bgcolor=Black width=60><font color=red size=1 face=Tahoma>D</font></td><td bgcolor=Black width=60><font color=red size=1 face=Tahoma>E</font></td><td bgcolor=Black width=60><font color=red size=1 face=Tahoma>F</font></td><td bgcolor=Black width=60><font color=red size=1 face=Tahoma>G</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=Black width=10><font color=red size=1 face=Tahoma>17</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=gray face=Tahoma>9</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=gray face=Tahoma>4</font></td><td bgcolor=white nowrap=true><font size=1 color=gray face=Tahoma>8TD</font></td><td bgcolor=white nowrap=true><font size=1 color=gray face=Tahoma>3TP</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=gray face=Tahoma>7</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=Black width=10><font color=red size=1 face=Tahoma>18</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=gray face=Tahoma><ACRONYM title='=Split_Digits(C17)'>9 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=gray face=Tahoma><ACRONYM title='=Split_Digits(D17)'>4 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=gray face=Tahoma><ACRONYM title='=Split_Digits(E17)'>8 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=gray face=Tahoma><ACRONYM title='=Split_Digits(F17)'>3 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=gray face=Tahoma><ACRONYM title='=Split_Digits(G17)'>7 (ƒx)</ACRONYM></font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=Black width=10><font color=red size=1 face=Tahoma>19</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=gray face=Tahoma><ACRONYM title='=SUM(C18:G18)'>31 (ƒx)</ACRONYM></font></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr></table></td></tr><tr><td bgcolor=yellow align=center><font color=blue size=1 face=Tahoma>Excel'den HTML'ye (Artvin_V2 Sürümü) ..ı|ı.. ExcelTürkiye - 19 Aralık 2006©</font></td></tr></table>

 

[pgc01]

Hi Tom

This is a formula based solution with 1 auxilliary column.

I assumed that the text part is at the end and that the code has a maximum of 20 characters.
Extract the number part to the auxilliary column and then sum it.

In B1:

=MAX(IF(ISNUMBER(--LEFT(A1,ROW($1:$20))),--LEFT(A1,ROW($1:$20))))

This in an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.
Copy down

In C1:
=Sum(B1:B5)

Hope this helps
PGC

Book1
	A	B	C	D
1	9	9	51
2	4	4
3	8TD	8
4	23SWR	23
5	7	7
6
Sheet4

 

[XLGibbs]

Why not just a formula:
=SUMPRODUCT(VALUE(LEFT(C17:H17,1))*1)

Provided all the numbers are single digits...

 

[TomA]

Thanks for your responses but these do not work.
I must be doing something wrong.
Thanks for your help though.
TOMA

 

[Domenic]

If TD and TP are the only text that your data contains, maybe...

=SUM(IF(C17:G17<>"",SUBSTITUTE(SUBSTITUTE(C17:G17,"TD",""),"TP","")+0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[TomA]

=SUM(IF(C17:W17<>"",SUBSTITUTE(SUBSTITUTE(C17:W17,"TD",""),"TP","")+0))

This is what I entered but it returned ### error.
Any ideas?

Thanks.
TOMA

 

[Domenic]

Does it help if the column width is widened?

 

[barry houdini]

Along the same lines as Domenic's suggestion but only requiring ENTER.....

=SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(C17:G17,"TD",""),"TP",""))+0)

 

[TomA]

No I still get a Value error.
The formula below works for single digit numbers only. When a double digit number is entered it only sums' the 1st digit. It does however add all numbers regardless if there is text in the cell or not.
I have tried changing the reference numbers but to no avail.
Any help would be greatly appreciated.
Thanks.
TOMA
=SUM(IF(ISNUMBER(--LEFT(C19:W19,ROW($1:$1))),--LEFT(C19:W19,ROW($1:$1))))