Sub Dividing pivot data by a set range

Hi All :)

I have been given a piece of work to do that involves me summarising a big bunch of data in a way that makes it easy to identify possible trends. However, I am finding this is easier said than done. So I have signed up to the forum today to see if any one can help me.

I have a matrix of survey results, with question numbers in columns, respondents (with a date of response) in rows and at the intercepts the response, e.g. A, B or C.

I would like to summarise this data in one pivot table, but appear to have almost too many variables.

I have a calculation on each row to group the responses into quarter they were received and would want the pivot table to do this also.

In my pivot table so far I have set question numbers as Columns, quarters in Rows and a count of the responses themselves in Values.

The bit I cannot work out is how to split the count of responses for each quarter by their value, e.g. 4As, 2Bs, 2Cs.

The closest I have come is to use the responses to question 1, dropped into Rows, to divide each quarter, but doing so doesn’t correctly count the responses to the other questions, as it is a count of intercepts as opposed to a count of all As,Bs,Cs for that question.

I have attached a small example sheet, as I’ve found this a difficult concept to define! It shows the layout of my source data (orange table), my ideal pivot table layout (green table) and my best effort so far!

Any help would be greatly appreciated! :)

Thank you in advance.

Morning Everyone,

I'm the horrible boss that gave Charlotte the above! I found this forum a huge help and really friendly when I was picking up Excel some time ago so pointed her here. This one has me stumped as well. We've struggled to explain what we're trying to achieve and aren't sure we're using the right terminology.

We feel we're nearly there but can't get past how to count A,B,C responses for each quarter without defining that range from one of the sets of question responses. If you do so it looks right, but reality doesn't count correctly - if we use A-C from question 1, the tally for other questions is only counted in relation to that, rather than in it's own right.

Many thanks in advance if anyone can help (or tell us it can't be done without multiple pivot tables, which I'm starting to suspect).

Simon.