0

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?

  • Updated question with an example – Adam Matan Jun 25 '18 at 9:06
1

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

1
=ARRAYFORMULA(QUERY(TRANSPOSE(SPLIT(TEXTJOIN("👾",1,TRIM(SPLIT(A1:A4,"|"))),"👾")),"Select Col1,count(Col1) group by Col1 order by count(Col1) desc",0))
  • Typical JOIN/SPLIT Combo to separate them all to unique rows
  • QUERY to find and sort the count on the newly created array

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.