Counting Unique Values from a Set List Based on Another Cell's Conditions

[Justen]

I need help figuring out a formula that will allow me to count the number of unique occurrences for a list of values that show up within specific categories.

Basically, I have data like this:

	A	B
1	Booking Type	Room Number
2	Office Hours	123
3	Office Hours	125
4	Event	200
5	Class	101
6	Event	200
7	Office Hours	123
8	Office Hours	145
9	Class	145
10	Event	202
11	Class	101

And I have to figure out how many different rooms have been used for, say, Office Hour bookings. But, in addition, there are different types of rooms and I need to break it down further by room category.

For example:

Classrooms	101, 145, 147, 148
Study Rooms	123, 125, 134, 191
Event Spaces	200, 202

So I need data to fill in the following:

	A	B	C	D	E
1	Booking Type	Total Unique Classrooms Used	Total Unique Study Rooms Used	Total Unique Event Spaces Used	Total Unique Spaces Used
2	Office Hours	??	??	??
3	Event	??	??	??
4	Class	??	??	??

Total Unique Spaces Used is fairly straight-forward. I'm getting really stuck on trying to figure out how many different classrooms were used specifically for Office Hours booking, for example.

 

[Toadstool]

Hi Justen,

You could always add a worker column for the Room Type.

Book1
	B	C	D	E	F	G	H	I	J
1	Booking Type	Room Number	Room Type		Room Types	Room Numbers
2	Office Hours	123	Study Rooms		Classrooms	101, 145, 147, 148	2
3	Office Hours	125	Study Rooms		Study Rooms	123, 125, 134, 191
4	Event	200	Event Spaces		Event Spaces	200, 202
5	Class	101	Classrooms
6	Event	200	Event Spaces
7	Office Hours	123	Study Rooms			Total Unique Room Usage
8	Office Hours	145	Classrooms		Booking Type	Classrooms	Study Rooms	Event Spaces	Total Unique Spaces Used
9	Class	145	Classrooms		Office Hours	1	3	0	4
10	Event	202	Event Spaces		Event	0	0	3	3
11	Class	101	Classrooms		Class	3	0	0	3
12
13
14
Sheet1

Cell Formulas
Range	Formula
H2	H2	=MATCH("*"&C2&"*",G2:G4,0)
G9:I11	G9	=COUNTIFS($B$2:$B$11,$F9,$D$2:$D$11,G$8)
J9:J11	J9	=COUNTIFS($B$2:$B$11,$F9)
D2:D11	D2	=INDEX($F$2:$F$4,MATCH("*"&C2&"*",$G$2:$G$4,0),0)