Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2007
    Posts
    1

    Unanswered: Advanced PivotTable question

    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 !

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •