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?
posted by clockzero to Computers & Internet (8 answers total) 1 user marked this as a favorite
 
Pivot tables! Sounds complicated but will let you do this in less than a minute. Here's a tutorial from Microsoft.
posted by beyond_pink at 7:01 PM on March 26, 2013 [1 favorite]


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


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]


If the items were in column A, then put in B1:


=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


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


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


Thanks, everyone. Much appreciated.
posted by clockzero at 8:30 PM on March 26, 2013


« Older Interview brainteasers?   |   Which road to take? Newer »
This thread is closed to new comments.