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

 

[kelvin p]

Neither worked. I think I gave bad example. I tried using the above but entering a symbol instead of ad. I have something similar working earlier it counted the numbers with just the symbol ignored other numbers with different symbols but it included numbers with no symbols.

 

[ANKIT PRIYADARSHI]

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=9D17=4E17=8TDF17=3TPG17=7I want the total of 31 but excel will ignore the cells with text.Can you help me please?Thanks

=SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C17:G17,"A",""),"B",""),"C",""),"D",""),"E",""),"F",""),"G",""),"H",""),"I",""),"J",""),"K",""),"L",""),"M",""),"N",""),"O",""),"P",""),"Q",""),"R",""),"S",""),"T",""),"U",""),"V",""),"W",""),"X",""),"Y",""),"Z",""),"",""))+0)



use this formula, its long but simple, all the 26 alphabets are substituted.... add more substitutes for symbols