countifs and multiply

[jimtheeagle]

Hello kind Mr. Excel folks,

My main table looks like this:

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>

Site	Suite	Room #	Username	First Name	Last Name	Employee Name	Title	Room Type	Utilization	Assignment Status
EME	1100	11.01		Chardonnay	Conference	Chardonnay Conference		Conference Room
EME	1100	11.02						Std Cube	Vacant, but furnished
EME	1100	11.03						Std Cube	Vacant, but furnished
EME	1100	11.04	rsmith	Rae	Smith	Rae Smith	Credit Manager	Std Ext Office	5 days/ week	Secure
EME	1100	11.05	hchang	Hieu	Chang	Hieu Chang	Data Analyst	Std Cube	5 days/ week	Secure
EME	1100	11.06		Crissy Field	Print	Crissy Field Print		Print Station
EME	1100	11.07	tknoll	Tom	Knoll	Tom Knoll	Infrastructure Manager	Std Ext Office	5 days/ week	Secure
EME	1100	11.08	cwest	Chad	West	Chad West	Independent Contractor	Std Cube	1-2 days / week	Secure
EME	1100	11.09						Std Cube	Vacant, but furnished
EME	1100	11.10	mgarcia	Melinda	Garcia	Melinda Garcia	HR Manager	Std Ext Office	5 days/ week	Secure
EME	1100	11.11						Std Cube	Vacant, but furnished
EME	1100	11.12	tfreeman	Tiffany	Freeman	Tiffany Freeman	Accounting Assistant	Std Cube	5 days/ week	Secure
EME	1100	11.13	ajones	Angela	Jones	Angela Jones	Payroll Specialist	Std Ext Office	5 days/ week	Secure
EME	1100	11.14	pbrown	Priscilla	Brown	Priscilla Brown	Payroll Specialist	Std Cube	5 days/ week	Secure
EME	1100	11.15	vsmith	Valerie	Smith	Valerie Smith	Accounting Assistant	Std Cube	5 days/ week	Secure
EME	1100	11.16						Std Ext Office	Unfurnished

<tbody>
</tbody>

The table I am trying to populate is:

Site	Total Capacity		Unutilized Current		Hires (hired but not started)		6-Mth Hotlist estimates		Planned Remain.Cap.
	Sqft	# Ext Offices	# Int Offices	# Workstations	Seated Capacity	# Conf	Sqft/seat		# Ext Off	# Int Off	# Work	Open Seats	Open		# Ext Off	# Int Off	# Work		# Ext Off	# Int Off	# Work	Hotlist success		# Ext Off	# Int Off	# Work	Open
ALP	0	0	0	0	0	0									2	1	5		2	2	4	50%		-4	-3	-9	####
ATL	0	0	0	0	0	0
BOG	0	0	0	0	0	0
BOS	5,300	0	0	0	0	0
BTM	4,896	0	0	0	0	0
CAL	0	0	0	0	0	0
CCI	10,091	0	0	0	0	0
CHA	0	0	0	0	0	0
CHI	11,015	0	0	0	0	0
CST	6,118	0	0	0	0	0
DAL	3,354	0	0	0	0	0
DCN	57,483	0	0	0	0	0
EME	33,798	66	12	84	162	6	209
HOU	8,239	0	0	0	0	0

<tbody>
</tbody>

The cell I need help with is the "unutilized current"

What should happen is it should reference the site e.g. EME, then subtract from the total number of spaces, e.g. Ext Offices (in EME) the number of "secure" rooms with the following calculation: sum the following: "5 days / week * 100%" + "3-4 days/ week * 70% + 1-2 days /week * 30% + less than 1 day/ week * 10%

Hope that all makes sense and thank you in advance.