Tallying categorical values in pivot table

Copper Contributor
I have a spreadsheet of data from survey results. There are 5 statements, and respondents respond with Strongly agree, Agree, Disagree, and strongly disagree for each:

respondent statement 1 statement 2 statement 3 statement 4 statement 5
1 Agree Disagree Strongly agree Agree Strongly disagree
2 Disagree Agree Agree Agree Agree
3 Disagree Agree Agree Disagree Agree
4 Agree Agree Agree Agree Disagree
5 Agree Disagree Disagree Strongly agree Disagree
6 Strongly agree Strongly disagree Disagree Strongly disagree Disagree
7 Strongly agree Strongly agree Agree Agree Strongly agree


I want a pivot table to give me the following summary:
Strongly Agree Agree Disagree Strongly disagree
statement 1 2 54 75 65
statement 2 65 74 75 6
statement 3 64 57 35 10
statement 4 35 53 5 63
statement 5 6 86 53 6


I know i can use countifs instead, but i want to use pivot filters and pivot charts for more manipulation so i want to stick to pivot table.

How can i do it? Thanks for your help!
1 Reply