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

 

[Help_Me_Excel]

I copied your formula and I am getting a # value error, I also tried retyping it and I get a #Value error,

could you please look at it one more time,....I really appreciate this.

Thank You

 

[barry houdini]

What values do you have in C17:C20?

 

[Help_Me_Excel]

In cell c 17 I have the values of 35K 13.0% 50K 13.5% 100K 14.0%
In cell c 18 I have the values of 5K 12.5% 10K 13.0%
In cell c 19 empty
In cell c 20 empty
Which should add to 200

 

[T. Valko]

Duplicate threads?

http://www.mrexcel.com/forum/showthread.php?t=557045

 

[barry houdini]

As Jason points out in your other thread - I didn't take account of situations where nnK or similar might be at the start - this should fix it......

=SUM(("000"&TRIM(MID("00"&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","")))))-1,3)))+0)

still confirmed with CTRL+SHIFT+ENTER

 

[Help_Me_Excel]

Thanks for all the help, that works.

Sorry for the duplication on the threads