google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

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

Supposing all I have is the column A below

               +         +
     A         |    B    |    C
+--------------|---------|----------+
               |         |
  X, Y, Z      |   X     |     3
               |         |
  X, Z         |   Y     |     2
               |         |
  X, Y         |   Z     |     2
               +         +

How do I generate columns B and C - where the B column grabs the unique elements from A, and the C column generates a count of those values.

Solution

  • =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