Create Pivot Table with Data from a quiz with "Select All" as an option

[MedAnalytics]

I am using Excel 2013.

I need to create an outcomes report by analyzing the data from a test that students have taken. I am reporting only on the number of students who properly identified the correct answer. Some of the questions are “Select All That Apply," meaning that students may have selected none, one, two, or three of the correct answers. I would like to create one pivot table (if a pivot table is the improper tool for this task, please let me know) which highlights the number of students who chose the correct answer for each possible answer choice. Ideally, I would like my pivot table to look like so:

Answer Choice	Total Count of Respondents
Chemotherapeutic drugs	36
Investigational drugs	44
NIOSH-list drugs	28

<tbody>
</tbody>

Here is the set up of my data:
[TABLE="class: grid, width: 837, align: left"]
<tbody>[TR]
[TD]Q9_1
[/TD]
[TD]Q9_2
[/TD]
[TD]Q9_3
[/TD]
[TD]Q9_4
[/TD]
[TD]Q9_5
[/TD]
[/TR]
[TR]
[TD]Select All (Concentrated Electrolytes)
[/TD]
[TD]Select All (Chemotherapeutic drugs)
[/TD]
[TD]Select All (Investigational drugs)
[/TD]
[TD]Select All (NIOSH-list drugs)
[/TD]
[TD]Select All (High-cost drugs)
[/TD]
[/TR]
[TR]
[TD]Concentrated Electrolytes
[/TD]
[TD]Chemotherapeutic drugs
[/TD]
[TD]Investigational drugs
[/TD]
[TD]NIOSH-list drugs
[/TD]
[TD]High-cost drugs
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chemotherapeutic drugs
[/TD]
[TD]Investigational drugs
[/TD]
[TD]NIOSH-list drugs
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chemotherapeutic drugs
[/TD]
[TD]Investigational drugs
[/TD]
[TD]NIOSH-list drugs
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chemotherapeutic drugs
[/TD]
[TD]Investigational drugs
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chemotherapeutic drugs
[/TD]
[TD]Investigational drugs
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Concentrated Electrolytes
[/TD]
[TD]Chemotherapeutic drugs
[/TD]
[TD]Investigational drugs
[/TD]
[TD]NIOSH-list drugs
[/TD]
[TD]High-cost drugs
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chemotherapeutic drugs
[/TD]
[TD]Investigational drugs
[/TD]
[TD]NIOSH-list drugs
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chemotherapeutic drugs
[/TD]
[TD]Investigational drugs
[/TD]
[TD]NIOSH-list drugs
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Chemotherapeutic drugs
[/TD]
[TD]Investigational drugs
[/TD]
[TD]NIOSH-list drugs
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









































Here is what my one pivot table ends up looking like:

[TABLE="class: grid, width: 500, align: left"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Select ALL NIOSH-list drugs[/TD]
[TD]Select ALL Investigational drugs[/TD]
[TD]Select ALLChemotherapeutic drugs[/TD]
[TD]Count of ResponseID[/TD]
[TD]Count of ResponseID2[/TD]
[/TR]
[TR]
[TD]NIOSH-list drugs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]89.13%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Investigational drugs[/TD]
[TD][/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]84.78%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Chemotherapeutic drugs[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]84.78%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD](blank)[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4.35%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Chemotherapeutic drugs[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4.35%[/TD]
[/TR]
[TR]
[TD](blank)[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10.87%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Investigational drugs[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10.87%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Chemotherapeutic drugs[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10.87%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
</tbody>[/TABLE]





























I would be grateful for any help, advice, tips, or ideas! Thank you so much!