simple excel 2007 question
March 26, 2013 6:57 PM Subscribe
I need a solution to a pretty simple problem with counting instances of different text phrases in Excel.
So I have a 3000+ row excel database that I'm working on for a project, with each row representing an instance of an official award and details about how the recipient earned it in each column. The final column in the spreadsheet has one of several set phrases for each entry describing what the reason for the award was (there are about 4 or 5 reasons total, and each instance of each reason is indicated with the same set phrase, eg. Went Above and Beyond). So here's my question: how can I easily and quickly make a simple tally of the total number of each reason?
So I have a 3000+ row excel database that I'm working on for a project, with each row representing an instance of an official award and details about how the recipient earned it in each column. The final column in the spreadsheet has one of several set phrases for each entry describing what the reason for the award was (there are about 4 or 5 reasons total, and each instance of each reason is indicated with the same set phrase, eg. Went Above and Beyond). So here's my question: how can I easily and quickly make a simple tally of the total number of each reason?
You could delete all of the text but the award name so it is uniform across the entire column, put it into a pivot table with the award name in the row section and recipients name in the values section and set the summary to count.
posted by munchingzombie at 7:02 PM on March 26, 2013
posted by munchingzombie at 7:02 PM on March 26, 2013
Best answer: If you know all the phrases, you can use the COUNTIF function.
So if the data is in column D and one phrase is "Went Above And Beyond", then in another column you would use the function
=COUNTIF(D:D, "Went Above And Beyond")
You would repeat this for every phrase.
If there are thousands of unique entries, Pivot tables would be faster, but for 4 or 5 unique entries I'd probably be mentally lazy and do it this way.
posted by muddgirl at 7:05 PM on March 26, 2013 [2 favorites]
So if the data is in column D and one phrase is "Went Above And Beyond", then in another column you would use the function
=COUNTIF(D:D, "Went Above And Beyond")
You would repeat this for every phrase.
If there are thousands of unique entries, Pivot tables would be faster, but for 4 or 5 unique entries I'd probably be mentally lazy and do it this way.
posted by muddgirl at 7:05 PM on March 26, 2013 [2 favorites]
If the items were in column A, then put in B1:
then fill down and sum.
posted by pompomtom at 7:05 PM on March 26, 2013
=1/COUNTIF(A:A,A1)
then fill down and sum.
posted by pompomtom at 7:05 PM on March 26, 2013
Sorry, I misread,. Muddgirl has it.
(my formula counts the number of distinct entries).
posted by pompomtom at 7:06 PM on March 26, 2013
(my formula counts the number of distinct entries).
posted by pompomtom at 7:06 PM on March 26, 2013
I'd do a pivot table just out of I don't trust that it's perfectly "Went Above and Beyond" and never "Went Above Nd Beyond" or various other accidental misspellings.
posted by skittlekicks at 7:51 PM on March 26, 2013
posted by skittlekicks at 7:51 PM on March 26, 2013
That can be addressed by using a filter to get a unique list of all things (alt-d f a alt-r and then put it somewhere), and making the second term of the countif refer to that items in that list.
If you don't mind duplicates, then simply referring to each item in the original list works too. Faster than pivots.
posted by pompomtom at 7:56 PM on March 26, 2013
If you don't mind duplicates, then simply referring to each item in the original list works too. Faster than pivots.
posted by pompomtom at 7:56 PM on March 26, 2013
Response by poster: Thanks, everyone. Much appreciated.
posted by clockzero at 8:30 PM on March 26, 2013
posted by clockzero at 8:30 PM on March 26, 2013
This thread is closed to new comments.
posted by beyond_pink at 7:01 PM on March 26, 2013 [1 favorite]