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

CodePudding user response：

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"))