Counting the frequency of a text string

  • Thread starter Thread starter Michael Styles
  • Start date Start date
M

Michael Styles

I've inherited some data to work with that I'm not sure how to approach.
Basically, multiple answers are contained within a cell. I need to extract
a sum of each individual answer. The data looks like this:


Special Education Students;#Economically Disadvantaged Students;#Minority
Students;#Regular Classroom Students;#Gifted and Talented Students
Special Education Students;#Economically Disadvantaged Students;#Minority
Students
Special Education Students;#Economically Disadvantaged Students;#Minority
Students;#Regular Classroom Students;#Gifted and Talented Students
Special Education Students;#Economically Disadvantaged Students;#Regular
Classroom Students
Special Education Students;#ESOL Students;#Regular Classroom Students
Special Education Students

I need to know how often "Special Education Students" occurs in 70 rows of
data; how often "Minority Students" occur, how often "Regular Classroom
Students" occur, etc...

I've tried CountIf but that only works if the cell only contains "Special
Education Students"

Any assistance would be greatly appreciated.
 
You can use wildcards (if the string you're looking for doesn't occur too far
into the cell (past the first 255 characters):

=COUNTIF(A:A,"*"&"Special Education Students"&"*")

Or

=COUNTIF(A:A,"*"&B1&"*")
if B1 contains: Special Education Students

=countif() will not find the substrings if they occur after the first 255 (0r
256???) characters.
 
Perfect. Thanks for your quick input!

Dave Peterson said:
You can use wildcards (if the string you're looking for doesn't occur too
far
into the cell (past the first 255 characters):

=COUNTIF(A:A,"*"&"Special Education Students"&"*")

Or

=COUNTIF(A:A,"*"&B1&"*")
if B1 contains: Special Education Students

=countif() will not find the substrings if they occur after the first 255
(0r
256???) characters.
Click to expand...
 
Back
Top