Google Sheets – How to Count Occurrences of Unknown Words

google sheets

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.

Best Answer

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.

Related Topic