If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting the frequency of a text string
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. |
#2
|
|||
|
|||
Counting the frequency of a text string
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. Michael Styles wrote: 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. -- Dave Peterson |
#3
|
|||
|
|||
Counting the frequency of a text string
Perfect. Thanks for your quick input!
"Dave Peterson" wrote in message ... 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. Michael Styles wrote: 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. -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|