Counting letters in a series

ashagape

New Member
Joined
Mar 11, 2009
Messages
8
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top