Pivot Table of Survey Data
 

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?

Pivot Table of Survey Data
 

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.

eastlaketech wrote:
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
Contextures
http://www.contextures.com/tiptech.html