Calculate the headcount at the end of each month based on the start/leave dates, split by employee type and country.

[maxim642]

Dummy data is provided below. In the tables to the right of the dummy data (South Africa, Canada & Brazil), I'd like to calculate the headcount at the end of each month for the country, split by employee type. The headcount determiners will be the START DATE & LEAVE DATE columns.

If someone joined on the last/first day of the month, they should be counted for that reporting month (depending on the year they joined.

If the LEAVE DATE is blank or contains a future date (relative to the reporting month), the person is still employed and should be counted provided they started before the end of the reporting month.

If the START DATE is after the reporting month, the person should not be counted.

An example would be, Joe Bloggs started on 3rd March 2022 and left on 19th June 2022. They would not be included in the headcount for Jan, Feb, but would be in Mar, Apr, May & Jun.

Master Headcount - May - Dashboard.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Full Name	Contract Type	Sex	Start Date	Leave Date	Department	Reporting Country		South Africa
2	Joe Bloggs1	Contractor	Male	1/10/22		Technical	South Africa		Contract Type	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
3	Joe Bloggs2	Perm, local	Male	1/17/22	3/18/22	CEO’s Office	South Africa		Contractor
4	Joe Bloggs3	Perm, local	Female	2/14/22	4/15/22	CEO’s Office	South Africa		Perm, local
5	Joe Bloggs4	FTC	Male	2/28/22	4/29/22	Legal	South Africa		FTC
6	Joe Bloggs5	FTC	Female	3/1/22	4/30/22	Legal	South Africa		Expat
7	Joe Bloggs6	FTC	Female	3/7/22	5/6/22	Legal	South Africa
8	Joe Bloggs7	FTC	Male	3/11/22	5/10/22	Legal	South Africa
9	Joe Bloggs8	FTC	Female	3/15/22	5/14/22	Legal	South Africa
10	Joe Bloggs9	FTC	Female	3/22/22		Legal	South Africa		Canada
11	Joe Bloggs10	Perm, local	Male	4/25/22		Technical	South Africa		Contract Type	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
12	Joe Bloggs11	Perm, local	Male	5/1/22		Technical	South Africa		Contractor
13	Joe Bloggs12	Perm, local	Female	5/1/22		HR	South Africa		Perm, local
14	Joe Bloggs13	Perm, local	Male	5/1/22	5/26/22	Security	South Africa		FTC
15	Joe Bloggs14	Perm, local	Male	5/9/22	6/3/22	Technical	South Africa		Expat
16	Joe Bloggs15	Perm, local	Female	5/16/22	6/10/22	Technical	South Africa
17	Joe Bloggs16	Perm, local	Male	5/23/22	6/17/22	Technical	South Africa
18	Joe Bloggs17	Perm, local	Male	5/23/22	6/17/22	Technical	South Africa
19	Joe Bloggs18	Perm, local	Male	5/25/22	6/19/22	Technical	South Africa		Brazil
20	Joe Bloggs19	Perm, local	Male	5/20/22	6/14/22	Security	South Africa		Contract Type	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
21	Joe Bloggs20	Perm, local	Male	5/24/22	6/18/22	Technical	South Africa		Contractor
22	Joe Bloggs21	Perm, local	Male	6/1/22		Technical	South Africa		Perm, local
23	Joe Bloggs22	Perm, local	Male	6/1/22		Technical	South Africa		FTC
24	Joe Bloggs23	Perm, local	Male	6/1/22		Technical	South Africa		Expat
25	Joe Bloggs24	Perm, local	Male	6/1/22		Technical	South Africa
26	Joe Bloggs25	Perm, local	Male	6/1/22		Technical	South Africa
27	Joe Bloggs26	Perm, local	Male	6/1/22		Technical	South Africa
28	Joe Bloggs27	Perm, local	Male	6/1/22		Procurement	South Africa
29	Joe Bloggs28	Perm, local	Female	6/6/22		IT	South Africa
30	Joe Bloggs29	Perm, local	Male	6/6/22		Technical	South Africa
31	Joe Bloggs30	Perm, local	Male	6/10/22		Technical	South Africa
32	Joe Bloggs31	Expat	Male	10/15/16		COO's Office	Canada
33	Joe Bloggs32	Perm, local	Male	2/1/20		COO's Office	Canada
34	Joe Bloggs33	Perm, local	Female	10/2/17	2/14/18	Finance	Canada
35	Joe Bloggs34	Perm, local	Female	2/8/16	6/22/16	Communications	Canada
36	Joe Bloggs35	Perm, local	Female	2/14/22	6/29/22	CEO’s Office	Canada
37	Joe Bloggs36	Perm, local	Male	2/6/18	6/21/18	Finance	Group Netherlands
38	Joe Bloggs37	Perm, local	Male	1/1/01	5/16/01	CEO’s Office	Group US
39	Joe Bloggs38	Perm, local	Male	12/1/15	4/14/16	IT	Group US
40	Joe Bloggs39	Perm, local	Female	7/7/15	11/19/15	CEO’s Office	Group US
41	Joe Bloggs40	Perm, local	Male	9/1/20	1/14/21		Group US
42	Joe Bloggs41	Perm, local	Female	7/19/21	12/1/21	CEO’s Office	Group US
43	Joe Bloggs42	Perm, local	Male	1/4/22	5/19/22	CEO’s Office	Group US
44	Joe Bloggs43	Perm, local	Male	3/1/22	7/14/22	CEO’s Office	Group US
45	Joe Bloggs44	Perm, local	Female	5/10/22	9/22/22		Group US
46	Joe Bloggs45	Perm, local	Male	5/9/22	9/21/22	Operations	Brazil
47	Joe Bloggs46	Perm, local	Female	5/9/22	9/21/22	Finance	Brazil
48	Joe Bloggs47	Perm, local	Female	5/9/22	9/21/22	Supply Chain	Brazil
49	Joe Bloggs48	Perm, local	Female	5/2/22	9/14/22	HR	Brazil
50	Joe Bloggs49	Perm, local	Female	5/2/22	9/14/22	Real Estate & Property Management	Brazil
51	Joe Bloggs50	Perm, local	Male	5/2/22	9/14/22	Technical	Brazil
52	Joe Bloggs51	Perm, local	Male	4/18/22		Operations	Brazil
53	Joe Bloggs52	Perm, local	Male	4/18/22		Implementation / Engineering	Brazil
54	Joe Bloggs53	Perm, local	Male	4/18/22		Implementation / Engineering	Brazil
55	Joe Bloggs54	Perm, local	Male	4/4/22	6/16/22	Operations	Brazil
56	Joe Bloggs55	Perm, local	Male	4/4/22	6/16/22	Operations	Brazil
57	Joe Bloggs56	Perm, local	Male	4/4/22	6/16/22	Technical	Brazil
58	Joe Bloggs57	Perm, local	Male	4/4/22	6/16/22	Purchasing	Brazil
59	Joe Bloggs58	Perm, local	Male	3/21/22		Technical	Brazil
60	Joe Bloggs59	Perm, local	Male	3/21/22		Supply Chain	Brazil
61	Joe Bloggs60	Perm, local	Male	3/21/22		IT	Brazil
62	Joe Bloggs61	Perm, local	Female	3/21/22		Operations	Brazil
63	Joe Bloggs62	Perm, local	Male	3/21/22		Finance	Brazil
64	Joe Bloggs63	Perm, local	Male	3/14/22		Operations	Brazil
65	Joe Bloggs64	Perm, local	Male	3/14/22		Technical	Brazil
66	Joe Bloggs65	Perm, local	Male	3/14/22		Technical	Brazil
67	Joe Bloggs66	Perm, local	Male	3/7/22		Implementation / Engineering	Brazil
68	Joe Bloggs67	Perm, local	Female	3/7/22		Finance	Brazil
69	Joe Bloggs68	Perm, local	Male	3/7/22		IT	Brazil
70	Joe Bloggs69	Perm, local	Male	3/7/22		IT	Brazil
71	Joe Bloggs70	Perm, local	Male	3/7/22		IT	Brazil
72	Joe Bloggs71	Perm, local	Female	2/21/22		Finance	Brazil
73	Joe Bloggs72	Perm, local	Female	2/21/22		Supply Chain	Brazil
74	Joe Bloggs73	Perm, local	Female	2/7/22		Technical	Brazil
75	Joe Bloggs74	Perm, local	Male	2/7/22		Technical	Brazil
76	Joe Bloggs75	Perm, local	Male	2/7/22		Technical	Brazil
77	Joe Bloggs76	Perm, local	Male	2/7/22		Operations	Brazil
78	Joe Bloggs77	Perm, local	Male	2/7/22		Technical	Brazil
79	Joe Bloggs78	Perm, local	Male	2/7/22		Technical	Brazil
80	Joe Bloggs79	Perm, local	Female	2/7/22		IT	Brazil
81	Joe Bloggs80	Perm, local	Male	2/7/22		Technical	Brazil
82	Joe Bloggs81	Perm, local	Female	2/1/22		Legal	Brazil
83	Joe Bloggs82	Perm, local	Male	2/1/22		Operations	Brazil
84	Joe Bloggs83	Perm, local	Male	2/1/22		Technical	Brazil
85	Joe Bloggs84	Perm, local	Male	1/17/22	2/1/22	Finance	Brazil
86	Joe Bloggs85	Perm, local	Male	1/17/22		Technical	Brazil
87	Joe Bloggs86	Perm, local	Male	1/17/22		Purchasing	Brazil
88	Joe Bloggs87	Perm, local	Female	1/17/22		Technical	Brazil
89	Joe Bloggs88	Perm, local	Female	1/17/22		HR & Admin	Brazil
90	Joe Bloggs89	Perm, local	Male	1/10/22		Operations	Brazil
91	Joe Bloggs90	Perm, local	Male	1/10/22		Implementation / Engineering	Brazil
92	Joe Bloggs91	Perm, local	Female	1/10/22		Operations	Brazil
93	Joe Bloggs92	Perm, local	Male	1/10/22		Technical	Brazil
94	Joe Bloggs93	Perm, local	Male	1/10/22		Technical	Brazil
95	Joe Bloggs94	Perm, local	Female	1/10/22		Technical	Brazil
96	Joe Bloggs95	Perm, local	Male	1/10/22		Technical	Brazil
97	Joe Bloggs96	Perm, local	Male	1/10/22		Technical	Brazil
98	Joe Bloggs97	Perm, local	Female	12/6/21		HSE	Brazil
99	Joe Bloggs98	Perm, local	Male	12/1/21		Legal	Brazil
100	Joe Bloggs99	Perm, local	Male	12/1/21		HR	Brazil
101	Joe Bloggs100	Perm, local	Female	12/1/21		HR	Brazil
102	Joe Bloggs101	Perm, local	Male	12/1/21		PMO	Brazil
103	Joe Bloggs102	Perm, local	Female	12/1/21		Purchasing	Brazil
104	Joe Bloggs103	Perm, local	Male	12/1/21		CEO’s Office	Brazil
105	Joe Bloggs104	Perm, local	Female	11/17/21		Finance	Brazil
106	Joe Bloggs105	Perm, local	Male	11/17/21		CTO's Office	Brazil
107	Joe Bloggs106	Perm, local	Male	11/17/21		Finance	Brazil
108	Joe Bloggs107	Perm, local	Female	11/1/21		Operations	Brazil
109	Joe Bloggs108	Perm, local	Male	11/1/21		Operations	Brazil
110	Joe Bloggs109	Perm, local	Male	11/1/21		Operations	Brazil
111	Joe Bloggs110	Perm, local	Male	11/1/21		Technical	Brazil
Sample

Cell Formulas
Range	Formula
E3:E9	E3	=D3+60
E14:E21	E14	=D14+25
E34:E51	E34	=D34+135
E55:E58	E55	=D55+73

 

[kevin9999]

Does this give you what you want?

maxim642.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Full Name	Contract Type	Sex	Start Date	Leave Date	Department	Reporting Country		South Africa
2	Joe Bloggs1	Contractor	Male	1/10/22		Technical	South Africa		Contract Type	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
3	Joe Bloggs2	Perm, local	Male	1/17/22	3/18/22	CEO’s Office	South Africa		Contractor	1	1	1	1	1	1	1	1	1	1	1	1
4	Joe Bloggs3	Perm, local	Female	2/14/22	4/15/22	CEO’s Office	South Africa		Perm, local	1	2	2	2	11	20	13	13	13	13	13	13
5	Joe Bloggs4	FTC	Male	2/28/22	4/29/22	Legal	South Africa		FTC	0	1	6	6	4	1	1	1	1	1	1	1
6	Joe Bloggs5	FTC	Female	3/1/22	4/30/22	Legal	South Africa		Expat	0	0	0	0	0	0	0	0	0	0	0	0
7	Joe Bloggs6	FTC	Female	3/7/22	5/6/22	Legal	South Africa
8	Joe Bloggs7	FTC	Male	3/11/22	5/10/22	Legal	South Africa
9	Joe Bloggs8	FTC	Female	3/15/22	5/14/22	Legal	South Africa
10	Joe Bloggs9	FTC	Female	3/22/22		Legal	South Africa		Canada
11	Joe Bloggs10	Perm, local	Male	4/25/22		Technical	South Africa		Contract Type	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
12	Joe Bloggs11	Perm, local	Male	5/1/22		Technical	South Africa		Contractor	0	0	0	0	0	0	0	0	0	0	0	0
13	Joe Bloggs12	Perm, local	Female	5/1/22		HR	South Africa		Perm, local	1	2	2	2	2	2	1	1	1	1	1	1
14	Joe Bloggs13	Perm, local	Male	5/1/22	5/26/22	Security	South Africa		FTC	0	0	0	0	0	0	0	0	0	0	0	0
15	Joe Bloggs14	Perm, local	Male	5/9/22	6/3/22	Technical	South Africa		Expat	1	1	1	1	1	1	1	1	1	1	1	1
16	Joe Bloggs15	Perm, local	Female	5/16/22	6/10/22	Technical	South Africa
17	Joe Bloggs16	Perm, local	Male	5/23/22	6/17/22	Technical	South Africa
18	Joe Bloggs17	Perm, local	Male	5/23/22	6/17/22	Technical	South Africa
19	Joe Bloggs18	Perm, local	Male	5/25/22	6/19/22	Technical	South Africa		Brazil
20	Joe Bloggs19	Perm, local	Male	5/20/22	6/14/22	Security	South Africa		Contract Type	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
21	Joe Bloggs20	Perm, local	Male	5/24/22	6/18/22	Technical	South Africa		Contractor	0	0	0	0	0	0	0	0	0	0	0	0
22	Joe Bloggs21	Perm, local	Male	6/1/22		Technical	South Africa		Perm, local	27	40	52	59	65	65	61	61	61	55	55	55
23	Joe Bloggs22	Perm, local	Male	6/1/22		Technical	South Africa		FTC	0	0	0	0	0	0	0	0	0	0	0	0
24	Joe Bloggs23	Perm, local	Male	6/1/22		Technical	South Africa		Expat	0	0	0	0	0	0	0	0	0	0	0	0
25	Joe Bloggs24	Perm, local	Male	6/1/22		Technical	South Africa
26	Joe Bloggs25	Perm, local	Male	6/1/22		Technical	South Africa
Sheet1

Cell Formulas
Range	Formula
J3:J6	J3	=SUMPRODUCT(($G$2:$G$111=$I$1)*($B$2:$B$111=$I3)*($D$2:$D$111<=J$2)*(($E$2:$E$111>(J$2-DAY(J$2)))+($E$2:$E$111="")))
K3:U6	K3	=SUMPRODUCT(($G$2:$G$111=$I$1)*($B$2:$B$111=$I3)*($D$2:$D$111<=K$2)*(($E$2:$E$111>J$2)+($E$2:$E$111="")))
E3:E9	E3	=D3+60
J12:J15	J12	=SUMPRODUCT(($G$2:$G$111=$I$10)*($B$2:$B$111=$I12)*($D$2:$D$111<=J$2)*(($E$2:$E$111>(J$2-DAY(J$2)))+($E$2:$E$111="")))
K12:U15	K12	=SUMPRODUCT(($G$2:$G$111=$I$10)*($B$2:$B$111=$I12)*($D$2:$D$111<=K$2)*(($E$2:$E$111>J$2)+($E$2:$E$111="")))
E14:E21	E14	=D14+25
J21:J24	J21	=SUMPRODUCT(($G$2:$G$111=$I$19)*($B$2:$B$111=$I21)*($D$2:$D$111<=J$2)*(($E$2:$E$111>(J$2-DAY(J$2)))+($E$2:$E$111="")))
K21:U24	K21	=SUMPRODUCT(($G$2:$G$111=$I$19)*($B$2:$B$111=$I21)*($D$2:$D$111<=K$2)*(($E$2:$E$111>J$2)+($E$2:$E$111="")))

 

[Fluff]

Another option

=COUNT(FILTER(ROW($G$2:$G$100),($G$2:$G$100=$I$1)*($B$2:$B$100=$I3)*($D$2:$D$100<=J$2)*(($E$2:$E$100>EOMONTH(J$2,-1))+($E$2:$E$100="")),""))

 

[maxim642]

Another option

=COUNT(FILTER(ROW($G$2:$G$100),($G$2:$G$100=$I$1)*($B$2:$B$100=$I3)*($D$2:$D$100<=J$2)*(($E$2:$E$100>EOMONTH(J$2,-1))+($E$2:$E$100="")),""))

Thank you!

 

[maxim642]

Does this give you what you want?

maxim642.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Full Name	Contract Type	Sex	Start Date	Leave Date	Department	Reporting Country		South Africa
2	Joe Bloggs1	Contractor	Male	1/10/22		Technical	South Africa		Contract Type	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
3	Joe Bloggs2	Perm, local	Male	1/17/22	3/18/22	CEO’s Office	South Africa		Contractor	1	1	1	1	1	1	1	1	1	1	1	1
4	Joe Bloggs3	Perm, local	Female	2/14/22	4/15/22	CEO’s Office	South Africa		Perm, local	1	2	2	2	11	20	13	13	13	13	13	13
5	Joe Bloggs4	FTC	Male	2/28/22	4/29/22	Legal	South Africa		FTC	0	1	6	6	4	1	1	1	1	1	1	1
6	Joe Bloggs5	FTC	Female	3/1/22	4/30/22	Legal	South Africa		Expat	0	0	0	0	0	0	0	0	0	0	0	0
7	Joe Bloggs6	FTC	Female	3/7/22	5/6/22	Legal	South Africa
8	Joe Bloggs7	FTC	Male	3/11/22	5/10/22	Legal	South Africa
9	Joe Bloggs8	FTC	Female	3/15/22	5/14/22	Legal	South Africa
10	Joe Bloggs9	FTC	Female	3/22/22		Legal	South Africa		Canada
11	Joe Bloggs10	Perm, local	Male	4/25/22		Technical	South Africa		Contract Type	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
12	Joe Bloggs11	Perm, local	Male	5/1/22		Technical	South Africa		Contractor	0	0	0	0	0	0	0	0	0	0	0	0
13	Joe Bloggs12	Perm, local	Female	5/1/22		HR	South Africa		Perm, local	1	2	2	2	2	2	1	1	1	1	1	1
14	Joe Bloggs13	Perm, local	Male	5/1/22	5/26/22	Security	South Africa		FTC	0	0	0	0	0	0	0	0	0	0	0	0
15	Joe Bloggs14	Perm, local	Male	5/9/22	6/3/22	Technical	South Africa		Expat	1	1	1	1	1	1	1	1	1	1	1	1
16	Joe Bloggs15	Perm, local	Female	5/16/22	6/10/22	Technical	South Africa
17	Joe Bloggs16	Perm, local	Male	5/23/22	6/17/22	Technical	South Africa
18	Joe Bloggs17	Perm, local	Male	5/23/22	6/17/22	Technical	South Africa
19	Joe Bloggs18	Perm, local	Male	5/25/22	6/19/22	Technical	South Africa		Brazil
20	Joe Bloggs19	Perm, local	Male	5/20/22	6/14/22	Security	South Africa		Contract Type	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
21	Joe Bloggs20	Perm, local	Male	5/24/22	6/18/22	Technical	South Africa		Contractor	0	0	0	0	0	0	0	0	0	0	0	0
22	Joe Bloggs21	Perm, local	Male	6/1/22		Technical	South Africa		Perm, local	27	40	52	59	65	65	61	61	61	55	55	55
23	Joe Bloggs22	Perm, local	Male	6/1/22		Technical	South Africa		FTC	0	0	0	0	0	0	0	0	0	0	0	0
24	Joe Bloggs23	Perm, local	Male	6/1/22		Technical	South Africa		Expat	0	0	0	0	0	0	0	0	0	0	0	0
25	Joe Bloggs24	Perm, local	Male	6/1/22		Technical	South Africa
26	Joe Bloggs25	Perm, local	Male	6/1/22		Technical	South Africa
Sheet1

Cell Formulas
Range	Formula
J3:J6	J3	=SUMPRODUCT(($G$2:$G$111=$I$1)*($B$2:$B$111=$I3)*($D$2:$D$111<=J$2)*(($E$2:$E$111>(J$2-DAY(J$2)))+($E$2:$E$111="")))
K3:U6	K3	=SUMPRODUCT(($G$2:$G$111=$I$1)*($B$2:$B$111=$I3)*($D$2:$D$111<=K$2)*(($E$2:$E$111>J$2)+($E$2:$E$111="")))
E3:E9	E3	=D3+60
J12:J15	J12	=SUMPRODUCT(($G$2:$G$111=$I$10)*($B$2:$B$111=$I12)*($D$2:$D$111<=J$2)*(($E$2:$E$111>(J$2-DAY(J$2)))+($E$2:$E$111="")))
K12:U15	K12	=SUMPRODUCT(($G$2:$G$111=$I$10)*($B$2:$B$111=$I12)*($D$2:$D$111<=K$2)*(($E$2:$E$111>J$2)+($E$2:$E$111="")))
E14:E21	E14	=D14+25
J21:J24	J21	=SUMPRODUCT(($G$2:$G$111=$I$19)*($B$2:$B$111=$I21)*($D$2:$D$111<=J$2)*(($E$2:$E$111>(J$2-DAY(J$2)))+($E$2:$E$111="")))
K21:U24	K21	=SUMPRODUCT(($G$2:$G$111=$I$19)*($B$2:$B$111=$I21)*($D$2:$D$111<=K$2)*(($E$2:$E$111>J$2)+($E$2:$E$111="")))

Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.