Google-sheets – Google Sheets: Split row value and count each part

google sheetsgoogle-sheets-query

My Problem

I have a survey result with a checkbox question in which more than one answer could be checked. The results are given in a single column, separated by a | sign.

enter image description here
I would like to run a count QUERY to grade answers by popularity. The expected output for this example is something like (numbers made up):

AWS 20
GCP 5
Pizza racks 1
Azure 1
...

What Have I Tried

  • SPLIT, but then the count spans multiple rows
  • Exporting to CSV and some Python-foo, but it's manual and error-prone

My Question

How can I QUERY COUNT a row which with one or more values in each cell, separated by a delimiter?

Best Answer

You could do it using join and split - and all in one function with an array literal for display:

=arrayformula({UNIQUE(transpose(split(join(" | ",A1:A), " | ",false))), if(istext(UNIQUE(transpose(split(join(" | ",A1:A), " | ",false)))),countif(transpose(split(join(" | ",A1:A), " | ",false)),UNIQUE(transpose(split(join(" | ",A1:A), " | ",false)))),)})

enter image description here

Related Topic