# Compute number of occurrences in a column of a spreadsheet

I have a Google Spreadsheet with a single column that holds string values (Twitter screen names) such as "user1", "user1", "UserX", and I would like to count those values so that I can easily craft a bar chart out of it. So the result should be

``````value        occurrence
-----------------------
user1         2
UserX         1
...          ....
``````

Please note, I only want to look for whole words, and not part words. For example, the words 'on' and 'one' appears in the word 'money' - I would not count this (i.e., only the word 'money' is counted).

Hope that is clear enough. What formula should I use?

• Did you want to find words within words though? EG, if your spreadsheet only consisted of 1 word, which was blabla, do you want to see the results as shown in your post?
– Dave Rook
Dec 14, 2012 at 9:25
• No, no words within words, maybe the example is abit confusing. "bla" and "blabla" are just two different values, could be "bla" and "blu", for example.
– wnstnsmth
Dec 14, 2012 at 9:29
• And lastly, what about capitalization - does that matter. How good are you with VBa? And are all the words just 1 word, or some 2 or 3 words long (sentences)
– Dave Rook
Dec 14, 2012 at 9:31
• Sorry, as per my VBa comment, is it an option to export to Excel? Otherwise, try this link developers.google.com/apps-script/overview
– Dave Rook
Dec 14, 2012 at 9:38
• If you do spreadsheets often, I recommend learning a bit of scripting. Just a little can go a long way. Learning curve typically is hard at first until you get going but persevere :)
– Dave Rook
Dec 14, 2012 at 9:50

1. Prepare all unique items: `=UNIQUE(A1:A10)`
2. Count them: `=COUNTIF(\$A\$1:\$A\$10,B1)`
3. Added them together: `=CONCATENATE(B1," ",C1)`