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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.