Getting the count and unique values of a column of comma separated values?

Without hidden cells is possible to do it with an alternative method than the one proposed by Adam (that did not work in my case). I have tested it with google spreadsheets (from data coming from a google form using multiple selection answers):

=UNIQUE(TRANSPOSE(SPLIT(JOIN(", ";A2:A);", ";FALSE)))

Explanation goes as follows:

  • JOIN to mix all the values from the A column (except A1 that could be the header of the column, if not, substitute it by A:A) separated by a coma
  • SPLIT to separate all the mixed values by their comas
  • TRANSPOSE to transformate the column into rows and viceversa
  • UNIQUE to avoid repeated values

Take into account that my "," coma includes and space character i.e., ", " to avoid incorrect unique values because "Z" y not equal to " Z".


=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN(",",A:A),",")&{"";""}),"select Col1, count(Col2) group by Col1 label count(Col2) ''",0))

QUERY function

TRANSPOSE function

SPLIT function

JOIN function