Sum Cells with multiple Numbers and multiple Text, within a cell

[Help_Me_Excel]

I am trying to sum a few cells that contain numbers and text, but I only want to sum the numbers that have a K in behind it. For example one cell could have 30K 12.5% 25K 13.0% and I want it to add to 55, just what is in front of the K's. I am using Excel 2007<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I tried using <o></o>
=SUM(IF(C17:C18<>"",SUBSTITUTE(SUBSTITUTE(C17:C18,"K",""),"%","")+0))
Unfortunately that does not work, anybody have any suggestions??

Cell C
Row 17 30K 12.5% 25K 13.0%


Row 18 50K 14.0%


Row 19
=SUM(IF(C17:C18<>"",SUBSTITUTE(SUBSTITUTE(C17:C18,"K",""),"%","")+0))

Should total 105

 

[barry houdini]

bit long, but....for a vertical range of cells try this

=SUM((0&MID(TRANSPOSE(C17:C20),FIND("^^",SUBSTITUTE(TRANSPOSE(C17:C20),"K","^^",ROW(INDIRECT("1:"&MAX(LEN(C17:C20)-LEN(SUBSTITUTE(C17:C20,"K",""))))))&REPT("00^^",MAX(LEN(C17:C20)-LEN(SUBSTITUTE(C17:C20,"K","")))))-2,2))+0)

confirmed with CTRL+SHIFT+ENTER

assumes that values will be only 1 or 2 digits....

 

[Help_Me_Excel]

Thank You barry houdini for your response, it works great!

 

[Help_Me_Excel]

This equation assumes 1 or 2 digits, what if it were 3 digits what would I have to change or add in this formula?

=SUM((0&MID(TRANSPOSE(C17:C20),FIND("^^",SUBSTITUTE(TRANSPOSE(C17:C20),"K","^^",ROW(INDIRECT("1:"&MAX(LEN(C17:C20)-LEN(SUBSTITUTE(C17:C20,"K",""))))))&REPT("00^^",MAX(LEN(C17:C20)-LEN(SUBSTITUTE(C17:C20,"K","")))))-2,2))+0)

 

[barry houdini]

can it be 1, 2 or 3....or just 2 or 3?

 

[Help_Me_Excel]

I would like the option of 1,2,or 3

 

[barry houdini]

Try this version.....

=SUM((0&TRIM(MID(TRANSPOSE(SUBSTITUTE(C17:C20," "," ")),FIND("^^",SUBSTITUTE(TRANSPOSE(SUBSTITUTE(C17:C20," "," ")),"K","^^",ROW(INDIRECT("1:"&MAX(LEN(C17:C20)-LEN(SUBSTITUTE(C17:C20,"K",""))))))&REPT("000^^",MAX(LEN(C17:C20)-LEN(SUBSTITUTE(C17:C20,"K","")))))-3,3)))+0)

still confirmed with CTRL+SHIFT+ENTER

 

[Help_Me_Excel]

I tried this formula and I get a #value

 

[Help_Me_Excel]

any idea why I am getting a #value error, when I use this array

=SUM((0&TRIM(MID(TRANSPOSE(SUBSTITUTE(C17:C20," "," ")),FIND("^^",SUBSTITUTE(TRANSPOSE(SUBSTITUTE(C17:C20," "," ")),"K","^^",ROW(INDIRECT("1:"&MAX(LEN(C17:C20)-LEN(SUBSTITUTE(C17:C20,"K",""))))))&REPT("000^^",MAX(LEN(C17:C20)-LEN(SUBSTITUTE(C17:C20,"K","")))))-3,3)))+0)

I used CTRL + SHIFT + ENTER

 

[barry houdini]

OK sorry, yes, the formula works OK but when I post it to the board some of the double spaces become single spaces - this should fix it

=SUM((0&TRIM(MID(TRANSPOSE(SUBSTITUTE(C17:C20," ",REPT(" ",2))),FIND("^^",SUBSTITUTE(TRANSPOSE(SUBSTITUTE(C17:C20," ",REPT(" ",2))),"K","^^",ROW(INDIRECT("1:"&MAX(LEN(C17:C20)-LEN(SUBSTITUTE(C17:C20,"K",""))))))&REPT("000^^",MAX(LEN(C17:C20)-LEN(SUBSTITUTE(C17:C20,"K","")))))-3,3)))+0)