Dynamic SUM based off Multiple Criteria

[CLaManna]

I'm trying to figure out how to do some sort of dynamic sum count based on multiple criteria from one sheet to another.

• Sheet A contains Personnel's Data which includes name, work title, work location, work group number, and other various information. There are 3 work titles, 6 work locations and 10 work group numbers which I am trying to use to create a "dynamic work chart".
• Sheet B contains the Work Chart, which should sum the number of personnel that are assigned to a specific group based on their work title.
• Sheet C contains the Cycle Days, which is when a specific set of groups are working. This sheet will show how many people are working based on a range of Groups, based on their work title. I currently have a manual SUM formula to include the Cycle Range; not sure if there's a way to have this part automated.

If someone's information is updated on Sheet A, i.e. they move from one work location to another, or they transfer to a different work group, I would like the Sheet B and Sheet C to automatically update accordingly.

It may sounds more complex than it should, but I appreciate any help I can get.

 

[ExceLoki]

try this. i think it's close to what you're asking for
----------

Book1
	A	B	C	D	E
1	first name	last name	title	location	group #
2	First Name 1	Last Name 1	Title 1	Location 1	1
3	First Name 2	Last Name 2	Title 2	Location 2	2
4	First Name 3	Last Name 3	Title 3	Location 3	3
5	First Name 4	Last Name 4	Title 1	Location 4	4
6	First Name 5	Last Name 5	Title 2	Location 5	5
7	First Name 6	Last Name 6	Title 3	Location 6	6
8	First Name 7	Last Name 7	Title 1	Location 1	7
9	First Name 8	Last Name 8	Title 2	Location 2	8
10	First Name 9	Last Name 9	Title 3	Location 3	9
11	First Name 10	Last Name 10	Title 1	Location 4	10
12	First Name 11	Last Name 11	Title 2	Location 5	1
13	First Name 12	Last Name 12	Title 3	Location 6	2
14	First Name 13	Last Name 13	Title 1	Location 1	3
15	First Name 14	Last Name 14	Title 2	Location 2	4
16	First Name 15	Last Name 15	Title 3	Location 3	5
17	First Name 16	Last Name 16	Title 1	Location 4	6
18	First Name 17	Last Name 17	Title 2	Location 5	7
19	First Name 18	Last Name 18	Title 3	Location 6	8
20	First Name 19	Last Name 19	Title 1	Location 1	9
21	First Name 20	Last Name 20	Title 2	Location 2	10
22	First Name 21	Last Name 21	Title 3	Location 3	1
23	First Name 22	Last Name 22	Title 1	Location 4	2
24	First Name 23	Last Name 23	Title 2	Location 5	3
25	First Name 24	Last Name 24	Title 3	Location 6	4
26	First Name 25	Last Name 25	Title 1	Location 1	5
27	First Name 26	Last Name 26	Title 2	Location 2	6
28	First Name 27	Last Name 27	Title 3	Location 3	7
29	First Name 28	Last Name 28	Title 1	Location 4	8
30	First Name 29	Last Name 29	Title 2	Location 5	9
31	First Name 30	Last Name 30	Title 3	Location 6	10
32	First Name 31	Last Name 31	Title 1	Location 1	1
33	First Name 32	Last Name 32	Title 2	Location 2	2
34	First Name 33	Last Name 33	Title 3	Location 3	3
35	First Name 34	Last Name 34	Title 1	Location 4	4
36	First Name 35	Last Name 35	Title 2	Location 5	5
37	First Name 36	Last Name 36	Title 3	Location 6	6
Personnel

----------

Book1
	A	B	C	D	E	F	G	H
1	Groups	Location 1	Location 2	Location 3	Location 4	Location 5	Location 6	Sum per Group
2	1	2	0	1	0	1	0	4
3	2	0	2	0	1	0	1	4
4	3	1	0	2	0	1	0	4
5	4	0	1	0	2	0	1	4
6	5	1	0	1	0	2	0	4
7	6	0	1	0	1	0	2	4
8	7	1	0	1	0	1	0	3
9	8	0	1	0	1	0	1	3
10	9	1	0	1	0	1	0	3
11	10	0	1	0	1	0	1	3
12	11	0	0	0	0	0	0	0
13	12	0	0	0	0	0	0	0
14	13	0	0	0	0	0	0	0
15	14	0	0	0	0	0	0	0
16	15	0	0	0	0	0	0	0
17	16	0	0	0	0	0	0	0
18	17	0	0	0	0	0	0	0
19	18	0	0	0	0	0	0	0
20	19	0	0	0	0	0	0	0
21	20	0	0	0	0	0	0	0
22	21	0	0	0	0	0	0	0
23	22	0	0	0	0	0	0	0
24	23	0	0	0	0	0	0	0
25	24	0	0	0	0	0	0	0
26	25	0	0	0	0	0	0	0
Work Chart Title 1

Cell Formulas
Range	Formula
B2:G26	B2	=COUNTIFS(Table1[location],'Work Chart Title 1'!B$1,Table1[group '#],'Work Chart Title 1'!$A2)
H2:H26	H2	=SUM(B2:G2)

----------

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Location 1		Location 2		Location 3		Location 4		Location 5		Location 6		Totals
2	Cycle Day	Groups Working	# Working	# Deficient	# Working	# Deficient	# Working	# Deficient	# Working	# Deficient	# Working	# Deficient	# Working	# Deficient	Sum Per Group	Deficiency
3	1	22 to 2	2		0		1		0		1		0		4	0
4	2	25 to 5	0		2		0		1		0		1		4	0
5	3	3 to 8	1		0		2		0		1		0		4	0
6	4	6 to 11	0		1		0		2		0		1		4	0
7	5	9 to 14	1		0		1		0		2		0		4	0
8	6	12 to 17	0		1		0		1		0		2		4	0
9	7	15 to 20	1		0		1		0		1		0		3	0
10	8	18 to 23	0		1		0		1		0		1		3	0
11	9	21 to 1	1		0		1		0		1		0		3	0
12	10	24 to 4	0		1		0		1		0		1		3	0
13	11	2 to 7	0		0		0		0		0		0		0	0
14	12	5 to 10	0		0		0		0		0		0		0	0
15	13	8 to 13	0		0		0		0		0		0		0	0
16	14	11 to 16	0		0		0		0		0		0		0	0
17	15	14 to 19	0		0		0		0		0		0		0	0
18	16	17 to 22	0		0		0		0		0		0		0	0
19	17	20 to 25	0		0		0		0		0		0		0	0
20	18	23 to 3	0		0		0		0		0		0		0	0
21	19	1 to 6	0		0		0		0		0		0		0	0
22	20	4 to 9	0		0		0		0		0		0		0	0
23	21	7 to 12	0		0		0		0		0		0		0	0
24	22	10 to 15	0		0		0		0		0		0		0	0
25	23	13 to 18	0		0		0		0		0		0		0	0
26	24	16 to 21	0		0		0		0		0		0		0	0
27	25	19 to 24	0		0		0		0		0		0		0	0
Cycle Days Title 1

Cell Formulas
Range	Formula
O3:P27	O3	=SUM(C3,E3,G3,I3,K3,M3)
C3:C27,M3:M27,K3:K27,I3:I27,G3:G27,E3:E27	C3	=INDEX('Work Chart Title 1'!$B$2:$G$26,MATCH($A3,'Work Chart Title 1'!$A$2:$A$26,1),MATCH(C$1,'Work Chart Title 1'!$B$1:$G$1,1))

 

[CLaManna]

try this. i think it's close to what you're asking for
----------

Book1
	A	B	C	D	E
1	first name	last name	title	location	group #
2	First Name 1	Last Name 1	Title 1	Location 1	1
3	First Name 2	Last Name 2	Title 2	Location 2	2
4	First Name 3	Last Name 3	Title 3	Location 3	3
5	First Name 4	Last Name 4	Title 1	Location 4	4
6	First Name 5	Last Name 5	Title 2	Location 5	5
7	First Name 6	Last Name 6	Title 3	Location 6	6
8	First Name 7	Last Name 7	Title 1	Location 1	7
9	First Name 8	Last Name 8	Title 2	Location 2	8
10	First Name 9	Last Name 9	Title 3	Location 3	9
11	First Name 10	Last Name 10	Title 1	Location 4	10
12	First Name 11	Last Name 11	Title 2	Location 5	1
13	First Name 12	Last Name 12	Title 3	Location 6	2
14	First Name 13	Last Name 13	Title 1	Location 1	3
15	First Name 14	Last Name 14	Title 2	Location 2	4
16	First Name 15	Last Name 15	Title 3	Location 3	5
17	First Name 16	Last Name 16	Title 1	Location 4	6
18	First Name 17	Last Name 17	Title 2	Location 5	7
19	First Name 18	Last Name 18	Title 3	Location 6	8
20	First Name 19	Last Name 19	Title 1	Location 1	9
21	First Name 20	Last Name 20	Title 2	Location 2	10
22	First Name 21	Last Name 21	Title 3	Location 3	1
23	First Name 22	Last Name 22	Title 1	Location 4	2
24	First Name 23	Last Name 23	Title 2	Location 5	3
25	First Name 24	Last Name 24	Title 3	Location 6	4
26	First Name 25	Last Name 25	Title 1	Location 1	5
27	First Name 26	Last Name 26	Title 2	Location 2	6
28	First Name 27	Last Name 27	Title 3	Location 3	7
29	First Name 28	Last Name 28	Title 1	Location 4	8
30	First Name 29	Last Name 29	Title 2	Location 5	9
31	First Name 30	Last Name 30	Title 3	Location 6	10
32	First Name 31	Last Name 31	Title 1	Location 1	1
33	First Name 32	Last Name 32	Title 2	Location 2	2
34	First Name 33	Last Name 33	Title 3	Location 3	3
35	First Name 34	Last Name 34	Title 1	Location 4	4
36	First Name 35	Last Name 35	Title 2	Location 5	5
37	First Name 36	Last Name 36	Title 3	Location 6	6
Personnel

----------

Book1
	A	B	C	D	E	F	G	H
1	Groups	Location 1	Location 2	Location 3	Location 4	Location 5	Location 6	Sum per Group
2	1	2	0	1	0	1	0	4
3	2	0	2	0	1	0	1	4
4	3	1	0	2	0	1	0	4
5	4	0	1	0	2	0	1	4
6	5	1	0	1	0	2	0	4
7	6	0	1	0	1	0	2	4
8	7	1	0	1	0	1	0	3
9	8	0	1	0	1	0	1	3
10	9	1	0	1	0	1	0	3
11	10	0	1	0	1	0	1	3
12	11	0	0	0	0	0	0	0
13	12	0	0	0	0	0	0	0
14	13	0	0	0	0	0	0	0
15	14	0	0	0	0	0	0	0
16	15	0	0	0	0	0	0	0
17	16	0	0	0	0	0	0	0
18	17	0	0	0	0	0	0	0
19	18	0	0	0	0	0	0	0
20	19	0	0	0	0	0	0	0
21	20	0	0	0	0	0	0	0
22	21	0	0	0	0	0	0	0
23	22	0	0	0	0	0	0	0
24	23	0	0	0	0	0	0	0
25	24	0	0	0	0	0	0	0
26	25	0	0	0	0	0	0	0
Work Chart Title 1

Cell Formulas
Range	Formula
B2:G26	B2	=COUNTIFS(Table1[location],'Work Chart Title 1'!B$1,Table1[group '#],'Work Chart Title 1'!$A2)
H2:H26	H2	=SUM(B2:G2)

----------

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Location 1		Location 2		Location 3		Location 4		Location 5		Location 6		Totals
2	Cycle Day	Groups Working	# Working	# Deficient	# Working	# Deficient	# Working	# Deficient	# Working	# Deficient	# Working	# Deficient	# Working	# Deficient	Sum Per Group	Deficiency
3	1	22 to 2	2		0		1		0		1		0		4	0
4	2	25 to 5	0		2		0		1		0		1		4	0
5	3	3 to 8	1		0		2		0		1		0		4	0
6	4	6 to 11	0		1		0		2		0		1		4	0
7	5	9 to 14	1		0		1		0		2		0		4	0
8	6	12 to 17	0		1		0		1		0		2		4	0
9	7	15 to 20	1		0		1		0		1		0		3	0
10	8	18 to 23	0		1		0		1		0		1		3	0
11	9	21 to 1	1		0		1		0		1		0		3	0
12	10	24 to 4	0		1		0		1		0		1		3	0
13	11	2 to 7	0		0		0		0		0		0		0	0
14	12	5 to 10	0		0		0		0		0		0		0	0
15	13	8 to 13	0		0		0		0		0		0		0	0
16	14	11 to 16	0		0		0		0		0		0		0	0
17	15	14 to 19	0		0		0		0		0		0		0	0
18	16	17 to 22	0		0		0		0		0		0		0	0
19	17	20 to 25	0		0		0		0		0		0		0	0
20	18	23 to 3	0		0		0		0		0		0		0	0
21	19	1 to 6	0		0		0		0		0		0		0	0
22	20	4 to 9	0		0		0		0		0		0		0	0
23	21	7 to 12	0		0		0		0		0		0		0	0
24	22	10 to 15	0		0		0		0		0		0		0	0
25	23	13 to 18	0		0		0		0		0		0		0	0
26	24	16 to 21	0		0		0		0		0		0		0	0
27	25	19 to 24	0		0		0		0		0		0		0	0
Cycle Days Title 1

Cell Formulas
Range	Formula
O3:P27	O3	=SUM(C3,E3,G3,I3,K3,M3)
C3:C27,M3:M27,K3:K27,I3:I27,G3:G27,E3:E27	C3	=INDEX('Work Chart Title 1'!$B$2:$G$26,MATCH($A3,'Work Chart Title 1'!$A$2:$A$26,1),MATCH(C$1,'Work Chart Title 1'!$B$1:$G$1,1))

Thank you for the assistance, but I'm still having issues. I tried doing the code you provided and it is giving me a SPILL error. I just installed the XL2BB extension and will add the chart I have for your reference

 

[CLaManna]

Roster:

Disp Ops Personnel with Work Chart.xlsx
	B	C	D	E	F	G
1	First Name	Last Name	Title	Dispatcher #	Borough	Group #
5	James	Martin	CFAD	006	Staten Island	19
6	Jeffrey	Feinsilver	CFAD	007	Manhattan	11
7	Patricia	Wright	CFAD	008	Staten Island	01
8	Nicholas	Lamberti	CFAD	009	HQ	--
9	David	Hroncich	CFAD	011	HQ	--
10	William	Nolan	CFAD	012	Manhattan	04
11	Shaun	Mathis	CFAD	013	Bronx	17
12	Brian	Schriefer	CFAD	014	Brooklyn	07
13	Christopher	LaManna	CFAD	015	Queens	23
14	Faye	Smyth	SFAD	032	Staten Island	17
15	Nicholas	Maxwell	SFAD	037	Manhattan	23
16	Jacquelin	Jones-Munch	SFAD	039	Brooklyn	14
17	Anthony	Buccieri	SFAD	040	Bronx	19
18	Auriel	Serrano	SFAD	041	Staten Island	24
19	Aaron	Rosenzweig	SFAD	048	Staten Island	05
20	Robert	DeMarr	SFAD	050	Bronx	01
21	John	Veneziano	SFAD	053	JOC	13
22	Daniel	Rosenbloom	SFAD	054	Brooklyn	21
23	Mark	Ferrucci	SFAD	055	OEM	--
24	Kevin	Trudden	SFAD	056	Field Comm	--
25	Joseph	Shovlin	SFAD	058	Manhattan	16
26	James	Murray	SFAD	059	JOC	01
27	Allen	Blatt	SFAD	060	HQ	--
28	Kevin	Delano Jr.	SFAD	061	HQ	07
29	Jason	Smith	SFAD	064	Bronx	13
30	John	Burke	SFAD	065	JOC	07
31	James	Corey	SFAD	066	JOC	19
32	Daniel	Meyers	SFAD	067	HQ	--
33	Edward	Overton	SFAD	068	Queens	15
34	Michael	Giarrizzo	SFAD	069	Staten Island	11
35	Richard	Fasulo	SFAD	070	Manhattan	04
36	James	Keane	SFAD	071	HQ	--
37	Sean	Walsh	SFAD	072	Manhattan	10
38	Ryan	Lawton	SFAD	073	Queens	09
39	Michael	Multari	SFAD	074	Queens	03
40	Robert	Suhre	SFAD	075	Queens	22
41	Phil	Humphrey-Rivas	SFAD	076	Brooklyn	02
42	Louis	Gomez	SFAD	077	Bronx	07
43	Bill	Ladell	FAD	101	Field Comm	--
44	Glenn	Loew	FAD	151	Staten Island	18
45	Mariliz	Rodriguez	FAD	175	HQ	--
46	James	Lang	FAD	179	Field Comm	--
47	Connie	Barnett	FAD	181	JOC	14
48	Darrin	Lewis	FAD	186	Bronx	13
49	Chris	Kalisak	FAD	190	Staten Island	24
50	Melvin	Hill	FAD	191	Staten Island	20
51	Frank	Bassetti	FAD	192	Field Comm	--
52	Thomas	Feeny	FAD	197	Field Comm	--
53	Scott	Hanratty	FAD	199	HQ	--
54	Allen	Ruff	FAD	200	Bronx	03
55	Douglas	Share	FAD	201	Field Comm	--
56	James	Baric	FAD	202	HQ	14
57	Christopher	Orlando	FAD	207	Field Comm	07
58	Dawn	Lappe	FAD	210	Bronx	18
59	Lourene	Rue	FAD	212	JOC	20
60	Matthew	Vitucci	FAD	214	Brooklyn	25
61	Mary	Healy	FAD	216	HQ	15
62	Joseph	Berkey	FAD	218	HQ	--
63	Frank	Longo	FAD	219	Staten Island	07
64	Nury	Molinar	FAD	221	Staten Island	16
65	Gary	Sheehan	FAD	223	Queens	05
66	Stefan	Fisher	FAD	225	Queens	02
67	William	Wade	FAD	227	JOC	23
68	James	Morrison	FAD	233	Bronx	04
69	James	Galia	FAD	234	Queens	14
70	Jonathan	Hadden	FAD	238	Field Comm	--
71	Teresa	Mathiasen	FAD	239	Staten Island	03
72	John	Newsom	FAD	240	Queens	24
73	Pauline	Brown	FAD	246	HQ	17
74	Matthew	Scullin	FAD	247	Staten Island	21
75	Christof	Pasterak	FAD	249	Staten Island	14
76	Chloe	Dmytrow	FAD	250	HQ	--
77	Fred	Robinson	FAD	251	Bronx	10
78	Victor	Melendez	FAD	252	Brooklyn	05
79	Lasheen	Maxey	FAD	253	Bronx	05
80	Michael	Walsh	FAD	257	Field Comm	--
81	Peter	Kaercher	FAD	258	Manhattan	18
82	Yvette	Mansfield	FAD	259	Staten Island	11
83	Justin	Zydor	FAD	260	Brooklyn	22
84	William	Galloway	FAD	261	Field Comm	--
85	Natalia	Tobon	FAD	268	Brooklyn	14
86	Moises	Arias	FAD	270	Staten Island	06
87	Philip	LaCombe	FAD	271	Field Comm	--
88	Michael	Prestovino	FAD	276	OEM	--
89	Jennifer	Eldridge	FAD	277	Bronx	02
90	Jason	Gonzalez	FAD	278	Brooklyn	01
91	Cristell	White	FAD	280	JOC	08
92	Alisha	Smith	FAD	281	HQ	10
93	Lynn	Soto	FAD	286	HQ	--
94	John	Abouricheh	FAD	290	Brooklyn	18
95	Jason	Frankel	FAD	294	Queens	18
96	Chris	Kassas	FAD	297	Manhattan	22
97	Andrew	Wright	FAD	300	Field Comm	20
98	Andrew	Baron	FAD	303	Brooklyn	20
99	Frank	Swiencki	FAD	306	Bronx	11
100	Thresa	Romako	FAD	307	Brooklyn	04
101	Victoria	Pritchard	FAD	308	Queens	11
102	Kai	Harold	FAD	310	Staten Island	23
103	Brian	Grogan	FAD	312	Brooklyn	09
104	Brandon	Millner	FAD	315	Manhattan	12
105	Anthony	Lauricella	FAD	316	Manhattan	11
106	Kelly	McCormack	FAD	317	Manhattan	12
107	Sherri	Johnson-Campbell	FAD	318	Brooklyn	23
108	Michael	Marchiano	FAD	319	Staten Island	17
109	Andrew	Chacon	FAD	321	HQ	25
110	Johnathan	Santo	FAD	323	Manhattan	02
111	Eric	Altamore	FAD	325	Field Comm	16
112	Tiffany	Webster	FAD	328	Brooklyn	11
113	Daniel	Rosenthal	FAD	329	Brooklyn	07
114	Michael	Rozhanskiy	FAD	330	Bronx	22
115	Fatimah	Shepherd	FAD	334	Manhattan	14
116	Sadiyyah	Dunson	FAD	336	HQ	19
117	Marlind	Haxhialiu	FAD	337	Staten Island	09
118	Ariele	Duran	FAD	340	JOC	11
119	Kelly	Ritz	FAD	341	Staten Island	05
120	William	Sauberman	FAD	342	Staten Island	04
121	Samiyah	Mitchell	FAD	344	Bronx	21
122	Matthew	Condon	FAD	345	Manhattan	03
123	John	Curley	FAD	346	HQ	13
124	Eric	Kropp	FAD	356	Queens	16
125	Anthony	Scalisi	FAD	357	HQ	--
126	Richard	Froehlich	FAD	360	Bronx	09
127	Peter	Leone	FAD	364	Queens	13
128	Robert	Munoz	FAD	365	Manhattan	24
129	Tanya	Kinard	FAD	367	HQ	--
130	Thomas	Miele	FAD	370	Queens	10
131	Michael	Colucci	FAD	372	Manhattan	19
132	Ianthe	Wyatt	FAD	377	Staten Island	13
133	Steven	Keck	FAD	381	Bronx	01
134	Derrick	Gallimore	FAD	385	Manhattan	05
135	Joseph	Pereira	FAD	386	Manhattan	17
136	Ralph	Saint Eloi	FAD	388	Brooklyn	24
137	Ronald	Martin	FAD	389	HQ	22
138	Elizabeth	Laberdee	FAD	390	JOC	02
139	Bryan	Etkins	FAD	397	JOC	17
140	Anthony	Esposito	FAD	398	Manhattan	01
141	Kevin	O'Donnell	FAD	399	Manhattan	07
142	Christa	Burns	FAD	400	Brooklyn	03
143	Ricardo	Santana	FAD	401	Brooklyn	13
144	Ledarius	Drumgold	FAD	406	Manhattan	09
145	Temara	Billey	FAD	407	Brooklyn	08
146	Lauren	Endriss	FAD	408	Queens	07
147	Jerry	Saladin	FAD	410	Brooklyn	16
148	Brittany	Dewiit	FAD	411	Brooklyn	24
149	Kelleah	Greenwood	FAD	415	Bronx	14
150	Nicole	Mennella	FAD	418	Brooklyn	08
151	Alejandro	Rodriguez	FAD	420	Manhattan	21
152	Zyanya	Torres	FAD	422	Manhattan	25
153	Shanell	Jackson	FAD	423	Manhattan	07
154	Timothy	Young	FAD	425	Manhattan	06
155	Kamyra	McCallum	FAD	426	Bronx	06
156	Frank	Festa	FAD	430	Brooklyn	06
157	Ann	Frawley	FAD	431	Manhattan	15
158	Mallory	Frawley-McGuinness	FAD	432	Bronx	16
159	Joseph	Giacopelli	FAD	433	Queens	12
160	Jason	Greco	FAD	434	Brooklyn	17
161	Thomas	Pynn	FAD	436	Brooklyn	19
162	Steven	Harris	FAD	438	Brooklyn	12
163	Natalie	Delgado	FAD	440	Queens	06
164	Brian	Cetin	FAD	443	Brooklyn	10
165	Matthew	Ganz	FAD	444	Bronx	17
166	Ruben	Tabares	FAD	446	Queens	21
167	Marina	Timmins	FAD	448	Manhattan	13
168	Michael	Minucci	FAD	451	Queens	19
169	Josiyah	Green	FAD	452	Queens	23
170	Mark	McIntyre Jr	FAD	453	Brooklyn	14
171	Richard	McDonough	FAD	455	Queens	15
172	Loribett	Quinones	FAD	456	Bronx	19
173	Scott	Pizzo	FAD	457	Manhattan	10
174	Nickolas	Finocchio	FAD	459	Manhattan	23
175	Carini	Reynoso	FAD	460	Queens	25
176	Paul	McGovern	FAD	461	Queens	09
177	Gissel	Melendez	FAD	462	Bronx	7
178	Albin	Franson	FAD	463	Manhattan	21
179	Shannon	Burgos	FAD	464	Brooklyn	2
180	John	Crisano Jr	FAD	465	Manhattan	4
181	Nicholas	Montalvo	FAD	466	Queens	22
182	Gabriela	Johnson	FAD	467	Brooklyn	15
183	Anthony	Pallisco	FAD	469	Manhattan	20
184	Susana	Romani	FAD	470	Bronx	15
185	Andrew	Haridat	FAD	471	Queens	03
186	Ashley	Burke	FAD	472	Manhattan	16
187	Mario	Barbagallo	FAD	473	Bronx	08
188	Izel	Johnson	FAD	475	Brooklyn	02
189	Maxine	Lake	FAD	476	Manhattan	09
Current Roster

 

[CLaManna]

Numbers by Group Chart:

Disp Ops Personnel with Work Chart.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T		V
35	Groups:	MANHATTAN			THE BRONX			BROOKLYN			QUEENS			STATEN IS			JOC				CW
36	1	1			1			1			0			0			0				3
37	2	1			1			2			1			0			1				6
38	3	1			1			1			1			1			0				5
39	4	1			1			1			0			1			0				4
40	5	1			1			1			1			1			0				5
41	6	1			1			1			1			1			0				5
42	7	2			1			1			1			1			0				6
43	8	0			1			1			0			0			1				3
44	9	2			1			1			1			1			0				6
45	10	1			1			1			1			0			0				4
46	11	1			1			1			1			1			1				6
47	12	2			0			0			1			0			0				3
48	13	1			1			1			1			1			0				5
49	14	1			1			2			1			1			1				7
50	15	1			1			1			1			0			0				4
51	16	2			0			1			1			1			0				5
52	17	1			1			1			0			1			1				5
53	18	1			1			1			1			1			0				5
54	19	1			1			1			1			0			0				4
55	20	1			0			1			0			1			1				4
56	21	1			0			1			1			1			0				4
57	22	1			1			1			1			0			0				4
58	23	1			1			1			1			1			2				7
59	24	1			1			1			1			1			0				5
60	25	1			0			1			1			0			0				3
61
62	Target Number	29			24			24			24			20			9
63
64	Total per Boro	28			20			26			20			16			8	118	118
Cycle Day Group

Cell Formulas
Range	Formula
V36:V60	V36	=SUM(C36:S36)
C64,R64,O64,L64,I64,F64	C64	=SUM(C36:C60)
S64	S64	=SUM(V36:V60)
T64	T64	=SUM(C64:R64)

 

[CLaManna]

Cycle Day Chart:

Disp Ops Personnel with Work Chart.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T		V
4	Cycle Day	Groups:	MANHATTAN			THE BRONX			BROOKLYN			QUEENS			STATEN IS			JOC				CW +/-
5	1	22 to 2	6	-1		5	-1		7	+1		5	-1		2	-3		3	1			-4
6	2	25 to 5	6	-1		5	-1		7	+1		4	-2		3	-2		1	-1			-6
7	3	3 to 8	6	-1		6	0		6	0		4	-2		5	0		1	-1			-4
8	4	6 to 11	7	0		6	0		6	0		5	-1		4	-1		2	0			-2
9	5	9 to 14	8	+1		5	-1		6	0		6	0		4	-1		2	0			-1
10	6	12 to 17	8	+1		4	-2		6	0		5	-1		4	-1		2	0			-3
11	7	15 to 20	7	0		4	-2		6	0		4	-2		4	-1		2	0			-5
12	8	18 to 23	6	-1		4	-2		6	0		5	-1		4	-1		3	1			-4
13	9	21 to 1	6	-1		4	-2		6	0		5	-1		3	-2		2	0			-6
14	10	24 to 4	6	-1		5	-1		7	+1		4	-2		3	-2		1	-1			-6
15	11	2 to 7	7	0		6	0		7	+1		5	-1		5	0		1	-1			-1
16	12	5 to 10	7	0		6	0		6	0		5	-1		4	-1		1	-1			-3
17	13	8 to 13	7	0		5	-1		5	-1		5	-1		3	-2		2	0			-5
18	14	11 to 16	8	+1		4	-2		6	0		6	0		4	-1		2	0			-2
19	15	14 to 19	7	0		5	-1		7	+1		5	-1		4	-1		2	0			-2
20	16	17 to 22	6	-1		4	-2		6	0		4	-2		4	-1		2	0			-6
21	17	20 to 25	6	-1		3	-3		6	0		5	-1		4	-1		3	1			-5
22	18	23 to 3	6	-1		5	-1		7	+1		5	-1		3	-2		3	1			-3
23	19	1 to 6	6	-1		6	0		7	+1		4	-2		4	-1		1	-1			-4
24	20	4 to 9	7	0		6	0		6	0		4	-2		5	0		1	-1			-3
25	21	7 to 12	8	+1		5	-1		5	-1		5	-1		3	-2		2	0			-4
26	22	10 to 15	7	0		5	-1		6	0		6	0		3	-2		2	0			-3
27	23	13 to 18	7	0		5	-1		7	+1		5	-1		5	0		2	0			-1
28	24	16 to 21	7	0		3	-3		6	0		4	-2		5	0		2	0			-5
29	25	19 to 24	6	-1		4	-2		6	0		5	-1		4	-1		3	1			-4
Cycle Day Group

Cell Formulas
Range	Formula
C5,R5,O5,L5,F5	C5	=SUM(C57:C60,C36,C37)
D5:D29	D5	=SUM((C5)-7)
C6,R6,O6,L6,I6,F6	C6	=SUM(C60,C36:C40)
C7,R7,O7,L7,I7,F7	C7	=SUM(C38:C43)
C8,R8,O8,L8,I8,F8	C8	=SUM(C41:C46)
C9,R9,O9,L9,I9,F9	C9	=SUM(C44:C49)
C10,R10,O10,L10,I10,F10	C10	=SUM(C47:C52)
C11,R11,O11,L11,I11,F11	C11	=SUM(C50:C55)
C12,R12,O12,L12,I12,F12	C12	=SUM(C53:C58)
C13,R13,O13,L13,I13,F13	C13	=SUM(C56:C60,C36)
C14,R14,O14,L14,I14,F14	C14	=SUM(C59:C60,C36:C39)
C15,R15,O15,L15,I15,F15	C15	=SUM(C37:C42)
C16,R16,O16,L16,I16,F16	C16	=SUM(C40:C45)
C17,R17,O17,L17,I17,F17	C17	=SUM(C43:C48)
C18,R18,O18,L18,I18,F18	C18	=SUM(C46:C51)
C19,R19,O19,L19,I19,F19	C19	=SUM(C49:C54)
C20,R20,O20,L20,I20,F20	C20	=SUM(C52:C57)
C21,R21,O21,L21,I21,F21	C21	=SUM(C55:C60)
C22,R22,O22,L22,I22,F22	C22	=SUM(C58:C60,C36:C38)
C23,R23,O23,L23,I23,F23	C23	=SUM(C36:C41)
C24,R24,O24,L24,I24,F24	C24	=SUM(C39:C44)
C25,R25,O25,L25,I25,F25	C25	=SUM(C42:C47)
C26,R26,O26,L26,I26,F26	C26	=SUM(C45:C50)
C27,R27,O27,L27,I27,F27	C27	=SUM(C48:C53)
C28,R28,O28,L28,I28,F28	C28	=SUM(C51:C56)
C29,R29,O29,L29,I29,F29	C29	=SUM(C54:C59)
M5:M29,J5:J29,G5:G29	G5	=SUM((F5)-6)
I5	I5	=SUM(I57:I60,I36:I37)
P5:P29	P5	=SUM((O5)-5)
S5:S29	S5	=SUM((R5)-2)
V5:V29	V5	=SUM(D5,G5,J5,M5,P5,S5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R5:R29	Cell Value	>2	text	NO
R5:R29	Cell Value	<2	text	NO
O5:O29	Cell Value	>5	text	NO
O5:O29	Cell Value	<5	text	NO
I5:I29	Cell Value	>6	text	NO
I5:I29	Cell Value	<6	text	NO
F5:F29,L5:L29	Cell Value	>6	text	NO
F5:F29,L5:L29	Cell Value	<6	text	NO
C5:C29	Cell Value	>7	text	NO
C5:C29	Cell Value	<7	text	NO
V5:V29	Cell Value	<0	text	NO

 

[CLaManna]

I had to break each sheet into a different post due to character limit.
Sorry if this is not allowed, or frowned upon, but I figured it was the easiest way to show you exactly what I am working with and referring too.