Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting the number of instances of a string within another string | Excel Worksheet Functions | |||
Counting specific character in text string | Excel Worksheet Functions | |||
Frequency of a word string in an excel database | Excel Discussion (Misc queries) | |||
Excel - counting cells containing a text string | Excel Worksheet Functions | |||
Counting every unique text string in a column | Excel Worksheet Functions |