Google Sheets: Split row value and count each part

Question

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.

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?

Answer 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)))),)})

Answer 2

=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