Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am having another go at using pivot tables to summarize data from a survey. In the main data range, columns contain answers to questions, such as Yes No Maybe ... On a separate worksheet I want a count of how many answered Yes to Q1, and the same for all answers to each question. There are thirty questions each with about five possible choices. I want to summarise onto one page how many answers there were to each question. so an example of the results would look like this:

    Yes No Maybe
    Question one: 12 6 0
    Question two: 9 85 16

    and so on for all thirty questions. We may have many hundreds of rows, each being one person's responses to the survey. We will want to filter on some fields, but mostly just summarise the totals.

    I've tried several ways to get this to work, including countifs and other functions, and don't know pivot tables well enough to get the result we need so any help is gratefully accepted. Thanks Frank

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Depends on the layout but a pivot table should be simple. Just drag the question field to the row area, the answer field to the column area, then drag the answer field to the data area as well and set it to Count.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    See sample attached.
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, I'll give that a try. Frank

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've attached a file with the layout we have to work with. Each respondent answers each question and we need a report of how many said Yes to Q1 etc.
    hope this is clear. Frank
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Frank - How does this look? It is not a Pivot Table.

    Tim
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Tim, countifs() were always our first choice, and they do the job well.The reason for thinking about using a pivot table is to see if it gives options for sorting / filtering the data on other fields. The respondents are in teams and we might like to look at a subset of answers from a given team, then compare these with the answers from another team. It may be that a Pivot table is not the best tool for that job, but we want to find out. All help gratefully accepted. Frank

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello Frank - The sample data makes no mention of "teams". Are there are other data fields in your actual work such as teams, dates, locality, etc.?

    Tim

  9. #9
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks to all who helped with this, it was easier in the end to use countif() so we did not pursue pivot tables. Frank

Posting Permissions

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