Staffing Counts on Oddly Dated Schedule

[alm395]

Hi,

I am trying to help my area identify staffing counts on a schedule created before I came over. I have included a very consolidated version of it as an example as the full version is roughly 900 rows (06/06/20 - today). Basically, each section is a 5-week rotation, and each of the 5-week sections identifies the employees' position. Due to staffing changes and promotions, it is unlikely that the 5-week section will have the same number of employees or the number of positions. (NOTE: This schedule is atrocious and very confusing...I am working on enhancing it once I retrieve the historical data)

The report table shows the first line complete (by manual count). There are a variety of different "codes" on the schedule, but the only ones I want to count are "D", "N", and "R". I feel like because of the way it is laid out, I am overthinking this, but I cannot seem to get an accurate result.

5-week-sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1	STAFFING COUNTS								SCHEDULE
2	DATE	SUP	DO	TO	CSUP	CTO	TOTAL		EMP	06/21/20	06/22/20	06/23/20	06/24/20	06/25/20	06/26/20	06/27/20	06/28/20	06/29/20	06/30/20	07/01/20	07/02/20	07/03/20	07/04/20	07/05/20	07/06/20	07/07/20	07/08/20	07/09/20	07/10/20	07/11/20	07/12/20	07/13/20	07/14/20	07/15/20	07/16/20	07/17/20	07/18/20	07/19/20	07/20/20	07/21/20	07/22/20	07/23/20	07/24/20	07/25/20
3	06/21/20	2	4	2	0	0	8		SUP		N	N	N			D	D	D	D						T	T	T	T							N	N	N	SV			D	D	D
4	06/22/20								TO		N	N	N			D	D	D	D						T	T	T	T	D						N	N	N	N			D	D	D
5	06/23/20								DO		N	N	N			D	D	D	D						T	T	T	T	D						N	N	N	N			D	D	D
6	06/24/20								DO		N	N	N			D	D	D	D						T	T	T	T	D						N	N	N	N			D	D	D
7	06/25/20								SUP	N			D	D	D			N	N	N			D	D	D	D						T	T	T	T							N	N	N
8	06/26/20								TO	N			D	D	D			N	N	N			D	D	D	D						T	T	T	T							N	N	N
9	06/27/20								DO	N			D	D	D			N	N	N			D	D	D	D						T	T	T	T							N	N	N
10	06/28/20								DO	N			D	D	D			N	N	N			D	D	D	D						T	T	T	T							N	N	NSV
11	06/29/20								SUP					N	N	N	N			D	D	D			N	N	N			D	D	D	D						T	T	T	T		N
12	06/30/20								TO					NUS	N	N	N			D	D	D8H4V			N	N	N			D	D	D09U	D						T	T	T	T		N
13	07/01/20								TO					N	N	N	N			D	D	D			N	N	N			DSV	D	D	D						T	T	T	T
14	07/02/20								DO	D				N	N	N	N			D	D	D			N	N	N			D	D	D	D						T	T	T	T		N
15	07/03/20								SUP		T	T	T	T							N	N	N	N			D	D	D			N	N	N			DSV	D	D	D				D
16	07/04/20								TO		T	T	T	T		N					N	N	N	N			D	D	D			N	N	N			D	D	D	D				D
17	07/05/20								TO		TSV	T	T	T		N					N	N	N	N			D	D	D			N	N	N			D	D	D	D
18	07/06/20								TO		T	T	T	T							N	N	N	N			D	D	D			N	N	N			D	D	DSS	D
19	07/07/20								SUP	D	D	D						T	T	T	T							N	N	N	N			D	D	D			N	N	N			D
20	07/08/20								TO	DSV	D	D						T	T	T	T							N	N	N	N			D	D	D			N	N	N			D
21	07/09/20								TO	D	D	D						TSV	TSV	T	T							N	N	N	N			D	D	D			N	N	N			D
22	07/10/20								DO	D	D	D						T	T	T	T							N	N	N	N			D	D	D			N	N	N			D
23	07/11/20								SUP		R	R	R	R	R			R	R	R	R	R			RSV	R	R	R	R			R	R	R	R	R			R	R	R	R	RSV
24	07/12/20								DO			SV	SV	SV	SV			D	D	D	D					D	D	D	D			D	D	D	D				D10	DSS	D	D	D
25	07/13/20								DO			D	D	D	D			D	D
26	07/14/20								DO			D	D	D	D			D	D	D	D					D	D	D	D			DSV	DSV	DSV	DSV					D	D	D	D
27	07/15/20								DO		D	D	D	D				D10	D	D	D	D2V			DSV	DSV	DSV	DSV				D10	D	D	D	D			D	D	D	D
28	07/16/20								DO		D	D	D	D					D	D	D	D2V			D	D	D	D				D10	D	D	D	D			D	D	D	D
29	07/17/20								DO		D	D	D	D	D9				D	D	D	D2V			D	D	D	D				D10	D	D	D	D			DSV	D	D	D
30	07/18/20
31	07/19/20
32	07/20/20
33	07/21/20
34	07/22/20
35	07/23/20
36	07/24/20
37	07/25/20
5-Wk Schedule

Cell Formulas
Range	Formula
G3	G3	=SUM(B3:F3)

 

[Fluff]

How about in B3 dragged down & across

=SUM(COUNTIFS(INDEX($J$3:$AR$29,,MATCH($A3,$J$2:$AR$2,0)),{"D","N","R"},$I$3:$I$29,B$2))

 

[alm395]

How about in B3 dragged down & across

=SUM(COUNTIFS(INDEX($J$3:$AR$29,,MATCH($A3,$J$2:$AR$2,0)),{"D","N","R"},$I$3:$I$29,B$2))

That did it! I was doing various versions of that formula, but never quite got it there.

Thanks so much!

 

[Fluff]

Glad to help & thanks for the feedback.