Count Yes/No in Pivot Table

ConfusedMum

New Member
Joined
Apr 22, 2013
Messages
1
Hi all,

I am hoping someone can advise me on how to set up my pivot table please?

This isn't the best way of setting up feedback analysis I am very sure, but I need to work with what I've got! I have survey results from students who have done a variety of modules for their courses and submit a feedback survey at the end of each module - the tricky bit to analysis is the free text question and that's what I am stuck on here. I have been going through reading each entry and if it mentions something about one of the key categories I put Yes in the column for that category and if the entry doesn't then I type No. Here is a shortened example (mine is obviously much bigger than this and has nearly 1,800 survey responses and 10 different categories):
CourseModuleCommentSentimentNavigation/DesignActivities/ContentLive Sessions/VideosGroupworkReading List
Cats101abc1Blah blahPositiveNoYesNoNoNo
Cats102def1Blah blahMixedNoNoYesYesYes
Cats103ghi1Blah blahPositiveYesNoYesYesNo
Cats102def1Blah blahMixedNoYesYesYesYes
Cats101abc1Blah blahMixedNoNoNoNoNo
Cats102def1Blah blahNegativeNoNoYesNoYes
Cats101abc4Blah blahPositiveYesYesYesNoYes
Cats101abc7Blah blahMixedNoYesYesYesNo

I then have a pivot table connected to it. As you can from the attached images I can't get the combinations right on the pivot table!

What I am TRYING (and failing so far!) to show is to count the number of Yes's and No's for each category for each module/course. So, ideally, I would have Course and Module in the Rows, then the categories (Navigation/Design, Activities/Content, etc...) along the top and any of the following:
  • each category column broken down into two - Yes and No, with a count of each ) (my preferred option)
  • each category column showing just the Yes count (if above option isn't possible!)
Any ideas you wonderful people - PLEASE!!!!

Thanks SO much,
Cathy 😀
 

Attachments

  • Screenshot 2021-11-23 185108.jpg
    Screenshot 2021-11-23 185108.jpg
    229.8 KB · Views: 2
  • Screenshot 2021-11-23 185340.jpg
    Screenshot 2021-11-23 185340.jpg
    215.8 KB · Views: 2

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,641
Office Version
  1. 365
Platform
  1. Windows
You might want to update your profile to say what version of Excel you are using.
If you have Power Query and are interested in using that, then it is only a couple of steps to transform what you have into a format that works for pivot tables.

eg.
These steps
1637732685396.png


Produce this M Code
Power Query:
[CODE=pq]
let
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Course", "Module", "Comment", "Sentiment"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Course", type text}, {"Module", type text}, {"Comment", type text}, {"Sentiment", type text}, {"Attribute", type text}, {"Value", type text}})
in
    #"Changed Type"
[/CODE]

And this table:

20211124 PQ to Pivot Yes No.xlsm
LMNOPQ
1CourseModuleCommentSentimentAttributeValue
2Cats101abc1Blah blahPositiveNavigation/DesignNo
3Cats101abc1Blah blahPositiveActivities/ContentYes
4Cats101abc1Blah blahPositiveLive Sessions/VideosNo
5Cats101abc1Blah blahPositiveGroupworkNo
6Cats101abc1Blah blahPositiveReading ListNo
7Cats102def1Blah blahMixedNavigation/DesignNo
8Cats102def1Blah blahMixedActivities/ContentNo
9Cats102def1Blah blahMixedLive Sessions/VideosYes
10Cats102def1Blah blahMixedGroupworkYes
11Cats102def1Blah blahMixedReading ListYes
12Cats103ghi1Blah blahPositiveNavigation/DesignYes
13Cats103ghi1Blah blahPositiveActivities/ContentNo
14Cats103ghi1Blah blahPositiveLive Sessions/VideosYes
15Cats103ghi1Blah blahPositiveGroupworkYes
16Cats103ghi1Blah blahPositiveReading ListNo
17Cats102def1Blah blahMixedNavigation/DesignNo
18Cats102def1Blah blahMixedActivities/ContentYes
19Cats102def1Blah blahMixedLive Sessions/VideosYes
20Cats102def1Blah blahMixedGroupworkYes
21Cats102def1Blah blahMixedReading ListYes
22Cats101abc1Blah blahMixedNavigation/DesignNo
23Cats101abc1Blah blahMixedActivities/ContentNo
24Cats101abc1Blah blahMixedLive Sessions/VideosNo
25Cats101abc1Blah blahMixedGroupworkNo
26Cats101abc1Blah blahMixedReading ListNo
27Cats102def1Blah blahNegativeNavigation/DesignNo
28Cats102def1Blah blahNegativeActivities/ContentNo
29Cats102def1Blah blahNegativeLive Sessions/VideosYes
30Cats102def1Blah blahNegativeGroupworkNo
31Cats102def1Blah blahNegativeReading ListYes
32Cats101abc4Blah blahPositiveNavigation/DesignYes
33Cats101abc4Blah blahPositiveActivities/ContentYes
34Cats101abc4Blah blahPositiveLive Sessions/VideosYes
35Cats101abc4Blah blahPositiveGroupworkNo
36Cats101abc4Blah blahPositiveReading ListYes
37Cats101abc7Blah blahMixedNavigation/DesignNo
38Cats101abc7Blah blahMixedActivities/ContentYes
39Cats101abc7Blah blahMixedLive Sessions/VideosYes
40Cats101abc7Blah blahMixedGroupworkYes
41Cats101abc7Blah blahMixedReading ListNo
Sheet1
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,824
Messages
5,766,657
Members
425,367
Latest member
Boboka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top