Creating count pivot tables of survy Qs with categorical variables across multiple columns

Hi,

I'm doing some simple survey analysis using pivot tables. I've come across an issue when trying to present a likert scale question (i.e. from strongly agree to strongly disagree) spilt over 3 columns (named: Q9a, Q9b, Q9c). in order to show the categorical range I inserted Q9a into the Row pivot field and then Q9a (again), Q9b and Q9c into the Value field. As you can imagine, with the pivot tables reading across, the column counts for Q9b and Q9c were distorted by Q9a.

I should also say that as the survey is rather long and has loads of these grid-style categorical questions, it would therefore be a bit of a pain to have separate out each column of the same question (that is, separate pivots for Q9a, Q9b and Q9c)

Any advice on how to present the correct counts using pivots would be greatly appreciated!!

Thanks in advance!

Simon

I have also attached a few pictures in the hope that they will aid your understanding:

pivot and countif.JPG

and

Pivot table fields.JPG

For most questions about pivot tables, it's impossible to suggest a solution without knowing the format of the source data. Could you please attach a sample workbook? It would help us a lot to suggest a solution. Thanks.