Sum Cells picking only numeric values and ignoring text

[queckey]

i need help how i can sum up this cell

Type

SP: 90

DC: 20

PC: 22

(TOTALSUM)

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

Thanks.

 

[Meesam_ali]

SUM works only with numeric values..What are you trying to achieve exactly?

 

[steve the fish]

Re: sum number with text in same cell

Try this:

=SUMPRODUCT(--(MID(A2:A4,FIND(":",A2:A4)+1,99)))

 

[Fluff]

Re: sum number with text in same cell

@queckey
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. (rule 12 here: Forum Rules).
I have merged both threads

 

[queckey]

Re: sum number with text in same cell

hi steve,
thanks for your help, i tried with this formula, it came out as #value error.
I need to sum out all the numbers and ignore the text: 90+20+22

Thanks

 

[queckey]

I need to sum out all the numbers and ignore the text: 90+20+22

 

[queckey]

Re: sum number with text in same cell

It works, but i found out the error is caused by the some of the cells have 0 as for the value

for example :

Type

SP: 90

DC: 20

0

(TOTALSUM)

<tbody>
</tbody>

Thank you.
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Type

SP: 90

DC: 20

PC: 22

(TOTALSUM)

<tbody>
</tbody>

</body>