I'm trying to summarize a multicolumn table using a pivot but I don't see a solution to the problem. The data table can grow or shrink depending if the cases remain open or not. My data looks like this:
a = facility name
b = administrative order (active,completed or blank)
c = penalty order (active,completed or blank)
d = settlement (active,completed or blank)
e = referral (active,completed or blank)
f = judicial (active,completed or blank)
g = program

abcdefg
facility1,completed,active,active,blank,blank,program1
facility2,active,active,active,active,blank,program2
facility3,completed,completed,active,blank,blank,program3
facility4,completed,completed,completed,completed,blank,program2
facility5,completed,active,completed,blank,completed,program1
facility6,completed,active,active,active,blank,program3
facility7,completed,active,active,blank,blank,program1
facility8,active,active,active,active,blank,program2
facility9,completed,active,active,blank,blank,program3
facility10,active,active,active,active,blank,program1

I'm hoping to end up with a pivot table that looks like this:
pivot.jpg

I put the facility name and the program in the rows section of the pivot and I can add a total count column for AO, penalty, settlement, etc but I can't break those columns into active or completed so that I can count results for the facilities in each program. I hope that this is making sense. I'm using excel 2013. Maybe I need to reformat my data. Any suggestions would be helpful. Thanks.