SOLVED

Help with pivot table needing to count specific cells in a column

New Contributor

Hiya so i am doing practice interview questions for a job, and the question i need help on is 

 

 

  1. 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 CodeCourse TitleRetainedGraduatedGood Honours
CARCFBAHBachelor of Arts (Honours) ArchitectureYYN
CARCFBAHBachelor of Arts (Honours) ArchitectureYYY
CARCFBAHBachelor of Arts (Honours) ArchitectureYYY
CARCFBAHBachelor of Arts (Honours) ArchitectureYN 
CARCFBAHBachelor of Arts (Honours) ArchitectureNN 
CARCFBAHBachelor of Arts (Honours) ArchitectureYYY
CARCFBAHBachelor of Arts (Honours) ArchitectureYN 
CARCFBAHBachelor of Arts (Honours) ArchitectureYYy
CARCFBAHBachelor of Arts (Honours) ArchitectureYYy
CARCFBAHBachelor of Arts (Honours) ArchitectureYYN
CARCFBAHBachelor of Arts (Honours) ArchitectureNN 
CARCFBAHBachelor of Arts (Honours) ArchitectureNN 
CARCFBAHBachelor of Arts (Honours) ArchitectureYYy
CARCFBAHBachelor of Arts (Honours) ArchitectureYYY
CARCFBAHBachelor of Arts (Honours) ArchitectureYYy
CARCFBAHBachelor of Arts (Honours) ArchitectureYN 
CARCFBAHBachelor of Arts (Honours) ArchitectureYYy
CARCFBAHBachelor of Arts (Honours) ArchitectureYYy
CARCFBAHBachelor of Arts (Honours) ArchitectureYYy
CARCFBAHBachelor of Arts (Honours) ArchitectureYYN
CARCFBAHBachelor of Arts (Honours) ArchitectureYYY
CARCFBAHBachelor of Arts (Honours) ArchitectureNN 
CARCFBAHBachelor of Arts (Honours) ArchitectureYYy
CARCFBAHBachelor of Arts (Honours) ArchitectureYYy
CARCFBAHBachelor of Arts (Honours) ArchitectureYYY
CARCFBAHBachelor of Arts (Honours) ArchitectureYN 
CARCFBAHBachelor of Arts (Honours) ArchitectureYYY
CARCFBAHBachelor of Arts (Honours) ArchitectureNN 

 

 

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 Communication3030
Bachelor of Arts (Honours) Animation3636
Bachelor of Arts (Honours) Architecture4444
Bachelor of Arts (Honours) Arts and Media99
Bachelor of Arts (Honours) CG Arts and Animation1717
Bachelor of Arts (Honours) Computer Games Arts99
Bachelor of Arts (Honours) Contemporary Jewellery1111
Bachelor of Arts (Honours) Creative Arts for Theatre and Film2323
Bachelor of Arts (Honours) Digital Film and Screen Arts1313
Bachelor of Arts (Honours) Fashion8181
Bachelor of Arts (Honours) Fashion Atelier2626
Bachelor of Arts (Honours) Fashion Design5151
Bachelor of Arts (Honours) Fashion Journalism6262
Bachelor of Arts (Honours) Fashion Management and Marketing7878
Bachelor of Arts (Honours) Fashion Promotion5555
Bachelor of Arts (Honours) Fashion Promotion and Imaging106106
Bachelor of Arts (Honours) Fashion Textiles: Print2929
Bachelor of Arts (Honours) Film Production8080
Bachelor of Arts (Honours) Fine Art8888
Bachelor of Arts (Honours) Graphic Communication2929
Bachelor of Arts (Honours) Graphic Design6969
Bachelor of Arts (Honours) Graphic Design: New Media1818
Bachelor of Arts (Honours) Graphic Design: Visual Communications44
Bachelor of Arts (Honours) Illustration3939
Bachelor of Arts (Honours) Interior Architecture and Design5252
Bachelor of Arts (Honours) Journalism2222
Bachelor of Arts (Honours) Music Journalism2828
Bachelor of Arts (Honours) Photography6060
Bachelor of Arts (Honours) Photography (Contemporary Practice)3030
Bachelor of Arts (Honours) Product Design1010
Bachelor of Arts (Honours) Product Design and Interaction77
Bachelor of Arts (Honours) Silversmithing, Goldsmithing and Jewellery77
Bachelor of Arts (Honours) Sports Journalism1515
Bachelor of Arts (Honours) Textiles for Fashion and Interiors3030
Bachelor of Arts (Honours) Three Dimensional Design99

 

 

Oscar

2 Replies
best response confirmed by OrionOne (New Contributor)
Solution

@OrionOne 

I'd go the formula route. Please see attached:

 

Thank you very much, appreciate it!