Pivot Table for Likert Scale Type Questions

[reader]

I am trying to do something identical to the question outlined below using pivot tables, but instead of having words such as "strongly disagree, disagree, etc." I have the numbers 1, 2, 3, 4, and 5.

http://www.mrexcel.com/forum/excel-...ons-rows-count-question-responses-values.html

Please see my sheet here - https://www.dropbox.com/s/bxz3ffrjxsgrssk/data.xlsx?dl=0

For example, I have questions P1_1, P1_2, ....P1_10 where each has an answer from 1 to 5. When I create a pivot table on the "Tables" sheet, it seems that it only uses the values from whatever P1_x I put in the rows part of the pivot table. I have simplified it and used only three of the P1 questions, but I want to be able to have it work for all 10.

It works if I generate the pivot table for each questions individually, but obviously this will be an incredible amount of work.

I want the pivot table to show the correct count for each number from 1 through 5 for each question PER1_1, PER2_2, ...PER2_10 in the table. Please let me know. Thank you!

 

[reader]

Bump -- please take a look!

 

[shg]

I don't understand any part of that workbook.

 

[reader]

To clarify and correct my initial mistake about the labeling of the questions: The Likert scale questions are labeled as PER1_1, PER1_2, PER1_3, all the way up to PER1_10. For each of those questions a respondent chooses a number from 1 to 5. I have cleaned out extra data and updated the spreadsheet (see here) with what I am trying to accomplish in table form with "xx" where the data I am looking for will go.

You can see that my attempts to use a pivot table do not to give the counts for each of the questions PER1_1, PER1_2, etc. but only gives the count for whichever question I choose to use as the rows entry for the pivot table.

 

[shg]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr][tr][td]

5​

[/td][td]

Response​

[/td][td]

PER1_1​

[/td][td]

PER1_2​

[/td][td]

PER1_3​

[/td][td]

PER1_4​

[/td][td]

PER1_5​

[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

1​

[/td][td]

28​

[/td][td]

4​

[/td][td]

3​

[/td][td]

54​

[/td][td]

76​

[/td][td][/td][td]B6: =COUNTIF(Data!A:A, 'What I Want'!$A6)[/td][/tr]
[tr][td]

7​

[/td][td]

2​

[/td][td]

49​

[/td][td]

10​

[/td][td]

27​

[/td][td]

63​

[/td][td]

55​

[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

3​

[/td][td]

44​

[/td][td]

35​

[/td][td]

52​

[/td][td]

45​

[/td][td]

34​

[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

4​

[/td][td]

53​

[/td][td]

87​

[/td][td]

55​

[/td][td]

16​

[/td][td]

18​

[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

5​

[/td][td]

15​

[/td][td]

53​

[/td][td]

52​

[/td][td]

11​

[/td][td]

6​

[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

Sum​

[/td][td]

189​

[/td][td]

189​

[/td][td]

189​

[/td][td]

189​

[/td][td]

189​

[/td][td][/td][td]B11: =SUM(B6:B10)[/td][/tr]
[/table]