COUNTIF Help

[TheKutts]

Hi, I am a first time poster in this forum. I have an issue regarding the COUNTIF formula:-

I have a column containing codes and text - for example - "00000 - To be confirmed".
Some rows in the column contain two or more codes and text separated by a semi-colon - for example - "95950 - Finger injury; 95960 - Hip and thigh injury"/
I need to count the number of times a code appears in the column and there is 129512 rows and the formula I am using is as follows:-
=COUNTIF($K$2:$K$129513,"*00000*")

If I use the formula above, I would need to change the code in the formula manually but there is about 4800 codes. What I would like to do is:-
- Strip the codes out of column K into column L and then use a COUNTIF formula to reference the codes in column L - something like:-

=COUNTIF($K$2:$K$129513,L2) - so that the formula searches column K2 to K129513 using L2 as the criteria.

Any help would be appreciated.
I am using Excel 2010.
Thank you. ​

 

[Eric W]

Welcome to the Board.

Try:

=COUNTIF($K$2:$K$129513,"*"&L2&"*")

 

[TheKutts]

Welcome to the Board.

Try:

=COUNTIF($K$2:$K$129513,"*"&L2&"*")

Thank you - it looks as if it worked.

 

[Eric W]

You're welcome.