Google Sheets – Compute Number of Occurrences in a Column

google sheets

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?

Best Answer

In Google Spreadsheet:

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

See example I've prepared: Find the number of occurrence of a word

Related Topic