Forum Discussion
Solved
Help with pivot table needing to count specific cells in a column
Hiya so i am doing practice interview questions for a job, and the question i need help on is
- On the ‘Workings’ sheet use formula and/or pivots to analyse this dataset to identify the percentage of students still retained and graduated at the end of the third year (by course) and the proportion of graduates that received a good honours degree (by course). The columns you require are as follows: ‘Course Code’, ‘Course Title’, ‘Retained’, ‘Graduated’, ‘Good Honours’
now on my spreadsheet, i believe i have the pivot table layed out correctly but it keeps counting all students instead of only ones with a Y in each column, if someone could help me out i would really appreciate it!
to give an example of part of the table i have created it from here it is:
| Course Code | Course Title | Retained | Graduated | Good Honours |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | N |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | Y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | Y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | N | |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | N | N | |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | Y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | N | |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | N |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | N | N | |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | N | N | |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | Y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | N | |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | N |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | Y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | N | N | |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | Y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | N | |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | Y | Y | Y |
| CARCFBAH | Bachelor of Arts (Honours) Architecture | N | N |
and this is what i get back as a pivot which is wrong
Row LabelsCount of RetainedCount of GraduatedGrand Total12771277
| Bachelor of Arts (Honours) Advertising & Brand Communication | 30 | 30 |
| Bachelor of Arts (Honours) Animation | 36 | 36 |
| Bachelor of Arts (Honours) Architecture | 44 | 44 |
| Bachelor of Arts (Honours) Arts and Media | 9 | 9 |
| Bachelor of Arts (Honours) CG Arts and Animation | 17 | 17 |
| Bachelor of Arts (Honours) Computer Games Arts | 9 | 9 |
| Bachelor of Arts (Honours) Contemporary Jewellery | 11 | 11 |
| Bachelor of Arts (Honours) Creative Arts for Theatre and Film | 23 | 23 |
| Bachelor of Arts (Honours) Digital Film and Screen Arts | 13 | 13 |
| Bachelor of Arts (Honours) Fashion | 81 | 81 |
| Bachelor of Arts (Honours) Fashion Atelier | 26 | 26 |
| Bachelor of Arts (Honours) Fashion Design | 51 | 51 |
| Bachelor of Arts (Honours) Fashion Journalism | 62 | 62 |
| Bachelor of Arts (Honours) Fashion Management and Marketing | 78 | 78 |
| Bachelor of Arts (Honours) Fashion Promotion | 55 | 55 |
| Bachelor of Arts (Honours) Fashion Promotion and Imaging | 106 | 106 |
| Bachelor of Arts (Honours) Fashion Textiles: Print | 29 | 29 |
| Bachelor of Arts (Honours) Film Production | 80 | 80 |
| Bachelor of Arts (Honours) Fine Art | 88 | 88 |
| Bachelor of Arts (Honours) Graphic Communication | 29 | 29 |
| Bachelor of Arts (Honours) Graphic Design | 69 | 69 |
| Bachelor of Arts (Honours) Graphic Design: New Media | 18 | 18 |
| Bachelor of Arts (Honours) Graphic Design: Visual Communications | 4 | 4 |
| Bachelor of Arts (Honours) Illustration | 39 | 39 |
| Bachelor of Arts (Honours) Interior Architecture and Design | 52 | 52 |
| Bachelor of Arts (Honours) Journalism | 22 | 22 |
| Bachelor of Arts (Honours) Music Journalism | 28 | 28 |
| Bachelor of Arts (Honours) Photography | 60 | 60 |
| Bachelor of Arts (Honours) Photography (Contemporary Practice) | 30 | 30 |
| Bachelor of Arts (Honours) Product Design | 10 | 10 |
| Bachelor of Arts (Honours) Product Design and Interaction | 7 | 7 |
| Bachelor of Arts (Honours) Silversmithing, Goldsmithing and Jewellery | 7 | 7 |
| Bachelor of Arts (Honours) Sports Journalism | 15 | 15 |
| Bachelor of Arts (Honours) Textiles for Fashion and Interiors | 30 | 30 |
| Bachelor of Arts (Honours) Three Dimensional Design | 9 | 9 |
Oscar
