Pivot Table to count values
 

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

Pivot Table to count values
 

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.
--
HTH...

Jim Thomlinson


"Piloulondon" wrote:

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

Pivot Table to count values
 

Thanks Jim;

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

"Jim Thomlinson" wrote:

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.
--
HTH...

Jim Thomlinson


"Piloulondon" wrote:

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

Pivot Table to count values
 

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" wrote:
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

Pivot Table to count values
 

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

"D-C Dave" wrote:

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" wrote:
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

Pivot Table to count values
 

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" wrote:

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.
--
HTH...

Jim Thomlinson


"Piloulondon" wrote:

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

Pivot Table to count values
 

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.
--
HTH...

Jim Thomlinson


"Piloulondon" wrote:

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" wrote:

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.
--
HTH...

Jim Thomlinson


"Piloulondon" wrote:

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