How to use Google Sheets to create table of Likert scale results grouped by counts

Question

I want to take responses to Likert survey items and create a summary table of the counts of each response (text labels: strongly disagree, disagree, neutral, agree, strongly agree).

(sample data set & manually created desired results table available in this sheet)

I am only able to do one question at a time with QUERY.

=QUERY(likertTable,"select B,count(B) group by B",1)

Creating an array of QUERY functions does not always work when one option is not selected for a question, resulting in mismatched row size.

Is there a way to create the desired table with a single QUERY—or other (e.g., VLOOKUP, COUNTIF)—formula?

Answer 1

try:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(B3:D="",,B2:D2&"×"&B3:D)), "×"), 
 "select Col2,count(Col2) where Col2 is not null group by Col2 pivot Col1"))

to sort, add "sort column" manually and pass through another QUERY:

=QUERY({ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(B3:D="",,B2:D2&"×"&B3:D)), "×"),
"select Col2,count(Col2) where Col2 is not null group by Col2 pivot Col1")),
{"sort";4;2;3;5;1}},
"select Col1,Col2,Col3,Col4 order by Col5",1)