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

 

[TomA]

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

This works! Thanks Barry Houdini!

Thanks to all who have helped.

 

[Domenic]

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

For what it's worth, the above formula needs to be confirmed with CONTROL+SHIFT+ENTER...

 

[TomA]

Thanks for all your help.

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

Well I have another problem. Although the above formula posted does work and solves one problem I have now encountered an additional problem later in the worksheet I am putting together. I am hoping you can help. I need to now be able have the text "I" and "INT" included with "TD" & "TP". The only difference is that "I" and "INT" will be alone in each cell. No number will be with I or INT. The above formula works but only if there are numbers and text. It produces a ### error when a cell contains just text.
Can you help please?
Thanks.
TOMA

 

[Domenic]

Try...

=SUM(IF(C17:G17<>"",IF(ISNA(MATCH(C17:G17,{"I","INT"},0)),SUBSTITUTE(SUBSTITUTE(C17:G17,"TD",""),"TP","")+0)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[barry houdini]

Or, extending my suggestion above

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

 

[pgc01]

Hi

This is a bigger formula but it doen't depend on specific suffixes.
This formula considers the codes composed of some digits (0 or more), followed by some non-digits(0 or more).

It may be interesting if you think that new suffixes may come up in the future.

=SUM(IF(ISNUMBER(--LEFT(0&C17:G17&"A",ROW($1:$19)))*NOT(ISNUMBER(--LEFT(0&C17:G17&"A",ROW($2:$20)))),--LEFT(0&C17:G17&"A",ROW($1:$19))))
This in an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Hope this helps
PGC

 

[ozeinstein]

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

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)

This actually worked for me! Thank you very much!

 

[kelvin p]

For what it's worth, the above formula needs to be confirmed with CONTROL+SHIFT+ENTER...

I was searching for a formula for my needs. I have range ending in different numbers eg 123ab 123cd what if i wanted to sum numbers only ending in ad.

 

[MAHMOUD-LEE]

Try this formula
=SUMPRODUCT(--(MID($A$1:$A$5;FIND("=";A1)+1;1)))

 

[vlady]

also
=SUM(IF(RIGHT(A1:A5,2)="ad",VALUE(LEFT(A1:A5,LEN(A1:A5)-2)),0))
array entered (ctrl + shift + enter ) not just enter