Pivot Table of Survey Data

[Guest]

I conducted a survey with over 1500 participants whose answered the questions
with SA, A, D or BD. I would like SA, A, D, DB as headings for the rows,
Questions 17 & 18 as my column heading, and the count for each answer (SA, A,
D, DB) as my data.

If I have 17 & 18 as my column heading, I only receive a grand total count.
If I add Question 17 to my ROW, it will show the SA, A, D, BD, however, the
data for column 18 is not correct.

Can anyone help?

 

[Debra Dalgleish]

If you have each participant's survey results in one row of data, you
won't get the layout that you want by using a pivot table.

Instead, you could create a table on the worksheet with SA,A,D,DB in a
column at the left, and question numbers in a row at the top.
Then, use a COUNTIF formula to count the responses in each question.
For example, with Q17 replies in column R on Sheet1:
=COUNTIF(Sheet1!R$2:R$1501,$A2)

Or, to reorganize the data, you can use the 'unpivot' technique
described by John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

Then, create a pivot table from the restructured data, with Response in
the Row area, SurveyID in the data area, as Count of SurveyID, and
Question in the column area.