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:

Q9_1	Q9_2	Q9_3	Q9_4	Q9_5
Select All (Concentrated Electrolytes)	Select All (Chemotherapeutic drugs)	Select All (Investigational drugs)	Select All (NIOSH-list drugs)	Select All (High-cost drugs)
Concentrated Electrolytes	Chemotherapeutic drugs	Investigational drugs	NIOSH-list drugs	High-cost drugs
	Chemotherapeutic drugs	Investigational drugs	NIOSH-list drugs
	Chemotherapeutic drugs	Investigational drugs	NIOSH-list drugs
	Chemotherapeutic drugs	Investigational drugs
	Chemotherapeutic drugs	Investigational drugs
Concentrated Electrolytes	Chemotherapeutic drugs	Investigational drugs	NIOSH-list drugs	High-cost drugs
	Chemotherapeutic drugs	Investigational drugs	NIOSH-list drugs
	Chemotherapeutic drugs	Investigational drugs	NIOSH-list drugs
	Chemotherapeutic drugs	Investigational drugs	NIOSH-list drugs

<tbody>
</tbody>

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

Select ALL NIOSH-list drugs	Select ALL Investigational drugs	Select ALLChemotherapeutic drugs	Count of ResponseID	Count of ResponseID2
NIOSH-list drugs			41	89.13%
	Investigational drugs		39	84.78%
		Chemotherapeutic drugs	39	84.78%
	(blank)		2	4.35%
		Chemotherapeutic drugs	2	4.35%
(blank)			5	10.87%
	Investigational drugs		5	10.87%
		Chemotherapeutic drugs	5	10.87%
Grand Total			46	100.00%

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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