# Count occurrences of words in Google Spreadsheet if the words are not known in advance

I have a sheet of names containing two columns: name and tags. Tags are separated by commas plus optional spaces. More than one tags can be in a single cell. Like this:

``````Name    | Tags
--------+--------------------------------
Alice   | female, fast, archer, starred
Bob     | male, starred
Cecile  | female, support
``````

The sheet contains hundreds of names and a 0..5 tags for each name. As output, I need a list of tags and the number of their occurrences. Something like this:

``````Tag     | Occurrences
--------+------------
female  | 2
fast    | 1
archer  | 1
starred | 2
male    | 1
support | 1
``````

How can I do this? I've been googling for hours, but couldn't find the solution so far.

• There is no hard limit. But their number probably will never go above 10. – Khardan Apr 12 '17 at 9:56

If your tags occur in Col B (starting at row 2), paste the below formula in column C (row 2) to get all unique tags in the Col B

``````=unique(transpose(split(CONCATENATE(arrayformula(B2:B&",")),",")))
``````

To get the number of occurrence of certain tag in col B, paste the below in col D row 2

``````=ARRAYFORMULA(if(countif(transpose(split(CONCATENATE((B2:B&",")),",")),C2:C) = 0, "",countif(transpose(split(CONCATENATE((B2:B&",")),",")),C2:C)))
``````

All the formulas used are standard formulas in Google Sheets.

• I could make the first row working by changing the , to ; in split function and adding a space to the 2nd parameter of the split. The second line gives a parse error. I changed commas to semicolons and added that space, but it did not help. – Khardan Apr 12 '17 at 9:58
• Sorry, I don't really get this whole arrayformula thing. What would the arrayformula do here? – Khardan Apr 12 '17 at 10:07
• For commas and semicolons I found that they are locale dependent. I found this working: =ARRAYFORMULA(if(countif(transpose(split(CONCATENATE(arrayformula(B2:B&",")),", ")),C2:C)=0,"",countif(transpose(split(CONCATENATE(arrayformula(B2:B&",")),", ")),C2:C))) What I added is two more arrayformulas inside concatenate. Thank you for your help! – Khardan Apr 12 '17 at 10:29
• From some reason it does not convert to preformatted text if I start the line with 4 spaces, sorry. – Khardan Apr 12 '17 at 10:35
• @khardan. Firstly, your question indicates that tags are separated by commas. So, I designed the formulas to work on commas! If your tags are separated by semicolons, I would change all the split both first and second function to ; and also make the following change all the B2:B&"," to B2:B&"; ". If you have trailing spaces I would look into using trim function. – Jack Brown Apr 12 '17 at 13:04