How to count the number of times a value appears in a comma separated cell? (Google sheets)-CodePudding

I got data as follows:

I need a formula that can separate, list and the count each unique word so my output is:

I am pretty new to google sheet formulas, especially combining several together. Thanks in advance.

CodePudding user response：

Suppose the header "Genre" is in A1 with your comma-separated list running down from A2.

Find two empty columns side by side and place the following in the top cell of the leftmost of those two empty columns:

=ArrayFormula(QUERY(FLATTEN(TRIM(SPLIT(FILTER(A2:A,A2:A<>""),","))),"Select Col1, COUNT(Col1) WHERE Col1 Is Not Null GROUP BY Col1 ORDER BY COUNT(Col1) DESC LABEL Col1 'Genre', COUNT(Col1) 'Total'"))

FILTER will cut null cells from A2:A, leaving you only with populated cells.

SPLIT will split each cell into separate columns at the commas.

TRIM will remove leading spaces that existed after commas.

FLATTEN will form one column from the results of SPLIT.

QUERY will give you the unique names and COUNT for each in highest-to-lowest (DESCending) order of COUNT (with a default of alphabetizing within groups of equal counts) after eliminating any null results of FLATTEN and will assign meaningful headers (LABELs) to each column.