Pivot Table to count values

[Piloulondon]

Hello everybody;

I am having difficulties to set up a pivot table that could help me
analyzing responses received from a questionnaire. I have fields "Districts"
and "Subdistricts" (both coded with numbers) and all the questions code with
"Q1", Q2a", etc... as column headers.
All the responses have been coded with numbers from 1 "very satisfied" to 12.
What I am trying to achieve is to be able to count how many 1, 2, 3, ... for
each question. Once I have this, I would aslo like to have an idea of the
values in percentage (% compared to the total amount of questionnaires).

Could somebody give me a hand. I hope the question is clear enough.

Many thanks

 

[Jim Thomlinson]

Your source data is in the wrong format for what you want to do. You are set
up like this...

District SubDistrict Q1 Q2 Q3
abc def 4 7 9

You want it to look like this...
District SubDistrict Question Answer
abc def Q1 4
abc def Q2 7
abc def Q3 9

If you can change the source file then everything you have asked for becomes
quite easy.

 

[Piloulondon]

Thanks Jim;

I will try to change the format of the data and will let you know. Thanks
again.

 

[D-C Dave]

Instead of a pivot table, how about =COUNTIF?
E.g. A1:E11 =
Dist Sub Q1 Q2 Q3
a e 2 3 4
b f 3 4 5
c g 1 2 3
d h 2 3 4

1 1 0 0
2 2 1 0
3 1 2 1
4 0 1 2
5 0 0 1

The formula in C7: =COUNTIF(C$2:C$5,$A7)
Copy to C7:E11.

D-C Dave

 

[Piloulondon]

Hi Dave;
This is what I have at the moment and it works fine. However, I will have to
filter the data in different ways to see what the trends are (ie. % of people
who answered for all districts, per district, per district and subdistricts,
etc...) and I thought that the pivot table was the solution.
Each line of the table correspond to a questionnaire. Therefore, I cannot
use the solution that Jim suggested.
Thanks for you answer.
Philippe

 

[Piloulondon]

Hi Jim;
I cannot use what you suggest because each line represent a questionnaire
and the fields "District" and "Subdistrict" are the 1st 2 fields of the row.
Any other idea that could help me?
Thanks again!

 

[Jim Thomlinson]

If it is a matter of too much work to coerce your source data into the proper
format that can be done via code. If you do not coerce your source data then
a pivot table will be an up hill battle. Ideally pivot table data has as few
columns as possible.