Can a single pivot table in Google Sheets summarize multiple Likert Scale responses?

Question

I have a data set with multiple columns that contain Likert Scale responses. In the header of each of these columns is a Likert Scale question such as, "How much do you agree with this statement?". The values of each of these columns contain Likert Scale answers that range from: "Strongly Disagree", "Disagree", "Neutral", "Agree", and "Strongly Agree".

I cannot provide a working Google Sheet sample at this time (on my work computer and away from home without access to a personal computer) but I can create sample tables below showing my expected Pivot Table result and a sample data set.

What I would like to do is create a Pivot Table using the data from these Likert Scale questions to analyze the total number of Likert Scale answers for each question. The problem I am facing is that I would like to accomplish this using only one Pivot Table as opposed to creating a Pivot Table for each individual question. So, ideally, I would like a Pivot Table that looks similar to this with the Likert Answers as columns and Likert Questions as rows (or vice-versa) like so:

	Strongly Disagree	Disagree	Neutral	Agree	Strongly Agree
Question 1	2	0	1	1	0
Question 2	1	1	0	0	2
Question 3	0	0	0	0	4

And the data set sample I would like to Pivot looks like this:

Question 1	Question 2	Question 3	Year Received
Strongly Disagree	Strongly Disagree	Strongly Agree	2020
Strongly Disagree	Disagree	Strongly Agree	2021
Neutral	Strongly Agree	Strongly Agree	2020
Agree	Strongly Agree	Strongly Agree	2022

Any help/input would be greatly appreciated even if the answer is simply, "Sorry, I do not think that this is possible with a single Pivot Table." which is the conclusion I have drawn.

As far as I am aware, this cannot be done in a single Pivot Table and instead requires an individual Pivot Table to be created for each question (which is not acceptable) or for this table to be created with a formula instead (which I have already done according to this method because I couldn't figure this one out via Pivot Table). I personally rarely, if ever, use Pivot Tables so I could just be missing something basic here and I am curious if any of you Pivot Table heroes out there can solve this one.

EDIT: To clarify, I am hoping to use a Pivot Table because I have Slicers connected to the source data to be able to filter the output table by different criteria such as YEAR.

Answer 1

try if it works for you with slicers:

=INDEX({"", "Strongly Disagree", "Disagree", "Neutral", "Agree", "Strongly Agree"; 
 FLATTEN(A1:C1), QUERY(QUERY(SPLIT(FLATTEN(A2:C&"×"&A1:C1), "×"), 
 "select count(Col2) where Col2 is not null group by Col2 pivot Col1", ), 
 "select Col5,Col2,Col3,Col1,Col4 offset 1", )*1})

---

update:

=INDEX({"", "Strongly Disagree", "Disagree", "Neutral", "Agree", "Strongly Agree"; 
 SORT(FLATTEN(A1:C1)), QUERY(QUERY(SPLIT({FLATTEN(A2:C&"×"&A1:C1&"×"&D2:D); 
 {"Strongly Disagree"; "Disagree"; "Neutral"; "Agree"; "Strongly Agree"}&"×'×"&
 IF(G1="", 1, G1)}, "×"), 
 "select count(Col2) where Col3 is not null "&
 IF(G1="",," and Col3 >="&G1)&
 IF(G2="",," and Col3 <="&G2)&
 "group by Col2 pivot Col1", ), 
 "select Col5,Col2,Col3,Col1,Col4 offset 2", )*1})

demo sheet