Hi,

Suppose I ran a survey among 5 customers, and each of the 3 questions had one of the answers Yes, No or Maybe. The raw results would look like this:

Customer Q1 Q2 Q3
C1 Yes No Maybe
C2 Maybe Yes Yes
C3 No Yes Maybe
C4 No Maybe Yes
C5 No No Maybe


For each question I can easily see the distribution of the 3 answers in a Pivot table of its own. However, what I am after is a SINGLE PIVOT in which I will see the distribution of the answers to all 3 questions - something like this:

Yes No Maybe
Q1 1 3 1
Q2 2 2 1
Q3 2 0 3


Is there a way to produce such a pivot table in Excel ? If yes - how do I create it ?

Thanks !

Howdy. If I understand correctly, it can't be done with Pivot Table (however, I have been known to be wrong - ask my wife).

You could set up a series of formulas. Assume Q1 data is in column B, Q2 in column C, Q3 in column D. Then put Yes in F2, No in F3, and Maybe in F4

In cell G2 put this formula:

=COUNTIF(B$2:B$6,$F2)

Then copy into the entire range (G2:I4)