Pivot table for a survey with likert scale answers (need to filter down to sub-population)

There is just one thing between me and a few days of rest - and I am a bit stuck and would like some input from you kind people.
------------------------------

Aim: To show survey results (table or graph) based on filter (sub-set of the population surveyed)
Input: A huge table full of survey results - please find a sample below in the attachment
Book1.xlsx


How do I build a pivot table which can let me find out number of "Agree", "Disagree" etc. for people from the UK, France, US, US who own Mac, people who own Mac etc.



Can anyone please help with this? I would really appreciate it if you could give some pointers!

You want a result that looks like this?

Row\Col	N	O	P	Q	R
3	Country	(All)
4	OS	(All)
5	Gender	(All)
6
7	Sum of Highly Disagree	Sum of Disagree	Sum of Neutral	Sum of Agree	Sum of Highly Agree
8	3	8	0	9	7

Hi shg,

Thanks for your reply.

I need a count/sum of highly disagree, disagree, neutral, agree and highly agree by each question for the filter selected.

The problem I have now is that "Agree, Disagree etc." when used as label only relate to that particular question and isn't viewed as global list applicable to all questions.

Please could you provide some insight? I am in a bit of a mess with this at the moment. Please suggest if there is a better way to organise data.

Thanks,
G


Edit: I should clarify that the final output I am looking for is average score for each question e.g. 4.25 for Do you like your OS? In case there is a different approach to this

See the workbook at https://app.box.com/s/hve66ycm5oqdkw4aoel7

I should add that I am no pivot table wiz, and someone may be able to suggest a better way to do this than by deconstructing the source data.

Thanks shg for your effort and help. My "real data" contains around 27 questions with around 1000 expected participants, who fall into 21/4/16 buckets (like countries, gender) and 20 questions, so would require me to make c.27,000 rows for Cartesian product idea. Is this the elegant solution for this problem? Is there a way to automatically populate that static list?

Thanks

Oops -- I reckon that is highly redundant. Hang on ...

Input compressed a bit:

Row\Col	A	B	C	D	E	F
1	Country	OS type	Gender	Like OS?	Like programming?	OS problems?
2	France	Mac	Male	HD	A	A
3	France	Mac	Male	HA	HD	D
4	France	Windows	Female	A	HA	HD
5	Germany	Windows	Female	A	HA	HA
6	UK	Mac	Male	A	D	D
7	UK	Mac	Male	HA	A	HA
8	UK	Windows	Female	HA	A	D
9	UK	Windows	Female	D	D	A
10	USA	Mac	Male	D	A	D

Then:

Row\Col	H	I	J	K	L	M	N	O
1	Country	UK
2	OS	Mac
3	Gender	Male
4
5		HD	D	N	A	HA
6	Like OS?	0	0	0	1	1		I6: =SUMPRODUCT((tblCtry = $I$1) * (tblOS = $I$2) * (tblGen = $I$3) * (INDEX(tblAs, 0, MATCH($H6, tblQs, 0)) = I$5))
7	Like programming?	0	1	0	1	0
8	OS problems?	0	1	0	0	1

You're welcome. Uploaded at same link.

If you change the formula to use CountIfs instead of SumProduct, you can do a wildcard match:

Row\Col	A	B	C	D	E	F	G	H
1	Country	*
2	OS	Mac
3	Gender	*
4
5		HD	D	N	A	HA
6	Like OS?	1	1	0	1	2		B6: =COUNTIFS(tblCtry, $B$1, tblOS, $B$2, tblGen, $B$3, INDEX(tblAs, 0, MATCH($A6, tblQs, 0)), B$5)
7	Like programming?	1	1	0	3	0
8	OS problems?	0	3	0	1	1