Counting letters in a series

[ashagape]

Hello,

I watched Excel Magic Trick #223 by ExcelisFun on YouTube about counting single characters in a text string. I know I am pretty close to getting the information I need out of a LARGE spreadsheet I have with tons of data in it. Each of my main cells has a series of letters that are codes for other things. I need to extract the codes and sum them up at the bottom of the column. For instance, some of my cells look like this:

PCP, TP, N, NP, PA
N, NP, PA
PCP, S, PH
NP, PH, TP

I need my end result to be this:

PCP = 2
TP = 2
N = 2
PA = 2
etc.

Is there a way to do this? I have 65 columns and 41 rows of information so I would prefer not to have to add any columns. If I need to change my coding to just one character instead of a string (P instead of PCP) I could.

I hope all of this makes since. Thanks for your help.

 

[schielrn]

Welcome to the Board!

Something like this would work to an extent, but will not count if it appears more than twice in a cell and also would count N in something like NP as well as juse N by itself, so this may not meet your complete needs, but will count some:

=countif(A1:BM45,"*PCP*")

or replace PCP with a cell reference like:

=countif(A1:BM45,"*" & A50 & "*")

For just N you would probably need to check for " N,", but then again if N is the first one it would not count it and if it was the last one it would not count it.

Hope that helps to get you started.

 

[pgc01]

Hi

Can a code appear more than once in a cell?

 

[barry houdini]

Assuming no repeat codes in a single cell try

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&F2&","," "&A$1:D$10&",")))

Where F2 contains the code and A1:D10 the range.

 

[ashagape]

Schielrn,

Thank you. I tried your suggestions and they do produce my desired end result; however, to make my data work with the formulas you provided I would have to change my codes to one letter references. Do you know of a way to get the same information without having to change my codes? I can change them if I have to, but I would prefer to leave them as is if possible.

Thanks.

 

[ashagape]

Barry houdini,

You formula works great!! Thanks!! I have one more question. I put the formula in my first cell and got it set-up to return my data, then I dragged the cell corner down to incorporate all of the other data. To get the same formula to work for the next column's data do I need to change the first formula from A1 to B1, for example, and then drag it down or is there something I can change in the formula to make it work from one column to another automatically?

Thanks.

 

[ashagape]

Never mind I think I fixed it!! It had to do with the placement of the $ sign right?

I changed it to =SUMPRODUCT(--ISNUMBER(SEARCH(" "&$B44&","," "&D5:D38&",")))

Does that look right? It should always use B44 as reference to my code and then I can check that code total column by column.

 

[ashagape]

Wait I see what you did with the $ sign in the D$5:D$38 spot. It helped always keep it rows 5-38 instead of it descending to D6:D39, D7:D40, etc. So I want to lock the row range but allow it to copy into the next column. How do I get it to change column letters but not row numbers?

 

[ashagape]

Got it all working great!! Thanks for your help!