Calculations for HR KPI's, specifically around headcount (historical and current).

[maxim642]

Below is a sample of my dataset. This is on the first tab.

HR Dashboard - Anonymous.xlsx
	A	B	C	D	E	F	G	H	I
1	Employee #	Gender	Country	Hire Date	Termination Date	Employment Status	Location	Directorate	Level
2	1283	Female	Viet Nam	6/1/17	9/30/20	Terminated	Ho Chi Minh	CCO
3	1366	Male	Nigeria	8/4/10		Full-Time on Permanent Contract	Lagos	CCO	D
4	1328	Male	Armenia	4/4/16		Full-Time on Permanent Contract	Yerevan	CAO	E
5	1289	Male	Chile	6/12/17	8/14/20	Terminated	Santiago	CCO
6	1321	Female	Armenia	8/1/16		Full-Time on Permanent Contract	Yerevan	CFO	D
7	529	Male	Nigeria	8/6/20		Full-Time on Permanent Contract	Lagos	CAO	E
8	1367	Male	Nigeria	1/3/11		Full-Time on Permanent Contract	Lagos	CTO	E
9	479	Male	Kenya	6/1/20		Full-Time on Permanent Contract	Nairobi	CCO	E
10	788	Male	Armenia	4/22/21		Full-Time on Permanent Contract	Yerevan	COO	F
11	663	Male	Armenia	2/1/21		Full-Time on Permanent Contract	Yerevan	COO	G
12	1322	Female	Armenia	7/15/19	6/16/20	Terminated	Yerevan	CCRO
13	1332	Male	Armenia	7/17/18		Full-Time on Permanent Contract	Yerevan	CTO	F
14	1357	Female	Armenia	8/8/17		Full-Time on Permanent Contract	Yerevan	CTO	G
15	181	Female	Armenia	2/10/20		Full-Time on Permanent Contract	Yerevan	CEO	F
16	1327	Male	Armenia	12/20/17		Full-Time on Permanent Contract	Yerevan	CCRO	E
17	1338	Male	Armenia	9/18/18		Full-Time on Permanent Contract	Yerevan	CTO	G
18	761	Female	Armenia	3/29/21		Full-Time on Permanent Contract	Yerevan	COO	G
19	1410	Male	Nigeria	7/15/16		Full-Time on Permanent Contract	Abuja	CCO	F
20	1384	Female	Nigeria	5/1/18		Full-Time on Permanent Contract	Lagos	CFO	F
21	1364	Male	Armenia	9/3/18		Full-Time on Permanent Contract	Yerevan	CCRO	E
22	1316	Female	Armenia	4/22/19		Full-Time on Permanent Contract	Yerevan	CCRO	E
23	1345	Male	Armenia	2/1/19		Full-Time on Permanent Contract	Yerevan	CCRO	F
24	1352	Female	Armenia	5/21/18		Full-Time on Permanent Contract	Yerevan	CCRO	F
25	321	Male	Russian Federation	11/1/06		Full-Time on Permanent Contract	Moscow	CISO	A
26	1329	Male	Armenia	10/10/19		Full-time on Fixed-term Contract	Yerevan	CCRO	G
27	539	Female	Armenia	9/2/20		Full-Time on Permanent Contract	Yerevan	CCO	E
28	1358	Female	Armenia	7/3/17		Full-Time on Permanent Contract	Yerevan	CD	E
29	1290	Male	Cyprus	9/5/18	9/30/20	Terminated	Santiago	CCO
30	1324	Female	Armenia	5/27/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
31	1331	Female	Armenia	7/11/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
32	1360	Female	Armenia	9/12/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
33	1334	Male	Armenia	8/16/18		Full-time on Fixed-term Contract	Yerevan	CCRO	F
34	1351	Female	Armenia	10/2/17		Full-time on Fixed-term Contract	Yerevan	CCRO	F
35	1344	Female	Armenia	5/10/17		Full-Time on Permanent Contract	Yerevan	CD	G
36	1404	Male	Nigeria	5/12/14		Full-Time on Permanent Contract	Abuja	CTO	F
37	1339	Female	Armenia	6/13/16		Full-Time on Permanent Contract	Yerevan	CCRO	F
38	1355	Female	Armenia	8/1/16		Full-Time on Permanent Contract	Yerevan	COO	G
39	526	Female	Armenia	8/4/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
40	674	Male	Armenia	2/15/21		Full-time on Fixed-term Contract	Yerevan	CCRO	G
41	473	Female	Armenia	6/1/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
42	525	Female	Armenia	8/3/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
43	656	Female	Armenia	1/25/21		Full-Time on Permanent Contract	Yerevan	CCRO	G
44	1478	Male	Armenia	3/2/20		Full-time on Fixed-term Contract	Yerevan	CCRO	G
45	530	Male	Armenia	8/12/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
46	1464	Male	Armenia	1/13/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
47	683	Female	Armenia	3/1/21		Full-Time on Permanent Contract	Yerevan	CCRO	G
48	675	Male	Armenia	2/15/21		Full-time on Fixed-term Contract	Yerevan	CCRO	G
49	531	Female	Armenia	8/12/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
50	511	Female	Armenia	7/23/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
51	595	Male	Lebanon	11/16/20		Full-time on Fixed-term Contract	Yerevan	CCRO	G
52	1452	Female	Armenia	12/18/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
53	503	Female	Armenia	7/15/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
54	532	Male	Armenia	8/12/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
55	1343	Female	Armenia	11/4/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
56	1340	Female	Armenia	8/19/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
57	1333	Male	Armenia	10/1/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
58	566	Male	Armenia	9/22/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
59	198	Male	Armenia	4/10/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
60	1335	Male	Armenia	9/5/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
61	431	Female	Armenia	4/16/20		Full-time on Fixed-term Contract	Yerevan	CCRO	G
62	505	Female	Armenia	7/15/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
63	426	Female	Armenia	4/10/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
64	1314	Female	Armenia	5/27/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
65	1350	Female	Armenia	5/17/17	11/30/20	Terminated	Yerevan	CD
66	1375	Male	Nigeria	10/2/12		Full-Time on Permanent Contract	Lagos	CCRO	E
67	1393	Male	Nigeria	9/10/18		Full-Time on Permanent Contract	Lagos	CTO	F
68	1432	Male	Armenia	12/2/19		Full-time on Fixed-term Contract	Yerevan	CCRO	G
69	1346	Female	Armenia	10/3/17		Full-Time on Permanent Contract	Yerevan	CCRO	F
70	1330	Male	Armenia	1/17/18	7/17/20	Terminated	Yerevan	CD
71	1368	Female	Nigeria	9/5/09		Full-Time on Permanent Contract	Lagos	CCRO	E
72	1319	Male	Armenia	1/14/19		Full-time on Fixed-term Contract	Yerevan	CCRO	G
73	1354	Male	Armenia	8/5/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
74	514	Male	Armenia	7/1/20		Full-time on Fixed-term Contract	Yerevan	CCRO	G
75	1349	Male	Armenia	4/3/17		Full-Time on Permanent Contract	Yerevan	CCRO	G
76	653	Male	Armenia	1/18/21	1/29/21	Terminated	Yerevan	CCRO
77	712	Male	Armenia	3/15/21		Full-Time on Permanent Contract	Yerevan	CCRO	G
78	1313	Female	Armenia	9/13/18		Full-Time on Permanent Contract	Yerevan	CCRO	G
79	1336	Female	Armenia	8/2/18		Full-Time on Permanent Contract	Yerevan	CCRO	G
80	1342	Male	Armenia	3/18/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
81	1470	Female	Armenia	2/5/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
82	1318	Male	Armenia	4/22/19	3/24/21	Terminated	Yerevan	CCRO	G
83	1362	Female	Armenia	7/15/19	1/21/21	Terminated	Yerevan	CCRO
84	1323	Male	Armenia	9/23/19		Full-time on Fixed-term Contract	Yerevan	CCRO	G
85	545	Female	Armenia	9/1/20		Full-Time on Permanent Contract	Yerevan	CD	Entry Level
86	1383	Female	Nigeria	2/2/18		Full-Time on Permanent Contract	Lagos	CCRO	E
87	1312	Female	Armenia	4/1/19		Full-Time on Permanent Contract	Yerevan	CD	G
88	703	Male	Russian Federation	12/21/15		Full-Time on Permanent Contract	Moscow	CISO	B
89	1377	Male	Nigeria	8/8/16		Full-Time on Permanent Contract	Lagos	CCO	G
90	592	Female	Nigeria	11/3/20		Full-Time on Permanent Contract	Lagos	CEO	F
91	1356	Female	Armenia	6/6/18		Full-Time on Permanent Contract	Yerevan	CFO	F
92	1326	Female	Armenia	10/1/19		Full-Time on Permanent Contract	Yerevan	CD	G
93	587	Female	Armenia	11/2/20		Full-Time on Permanent Contract	Yerevan	CEO	F
94	1359	Female	Armenia	6/7/19	7/31/20	Terminated	Yerevan	CD
95	1400	Female	Nigeria	3/11/19		Full-Time on Permanent Contract	Lagos	CFO	G
96	645	Male	Kenya	1/11/21		Full-Time on Permanent Contract	Nairobi	COO	F
97	1337	Female	Armenia	7/18/18		Full-Time on Permanent Contract	Yerevan	CCRO	G
98	1365	Male	Armenia	11/12/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
99	557	Male	Armenia	9/22/20	10/19/20	Terminated	Yerevan	CCRO
100	571	Female	Armenia	9/28/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
101	778	Female	Armenia	4/15/21		Full-Time on Permanent Contract	Yerevan	CCRO	F
102	481	Female	Armenia	6/8/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
103	455	Female	Armenia	5/11/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
104	1422	Male	Armenia	11/25/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
105	1325	Male	Armenia	6/17/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
106	1347	Female	Armenia	7/22/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
107	1341	Female	Armenia	9/1/17		Full-Time on Permanent Contract	Yerevan	CCRO	G
108	1471	Male	Armenia	2/5/20		Full-time on Fixed-term Contract	Yerevan	CCRO	G
109	1320	Female	Armenia	10/7/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
110	1423	Female	Armenia	11/25/19	12/30/20	Terminated	Yerevan	CCRO
111	1363	Female	Armenia	8/26/19	8/14/20	Terminated	Yerevan	CD
112	546	Female	Armenia	9/1/20		Full-Time on Permanent Contract	Yerevan	CD	G
113	614	Male	Armenia	12/1/20		Provision of Services Agreement	Yerevan	CCRO	F
114	614	Male	Armenia	12/1/20		Provision of Services Agreement	Yerevan	CCRO	F
115	496	Female	Armenia	7/6/20	1/8/21	Terminated	Yerevan	COO
116	1376	Male	Nigeria	4/25/16	2/5/21	Terminated	Abuja	CCO	F
117	794	Male	Russian Federation	4/26/21		Professional Service Provider Agreement	Moscow	CTO	E
118	1369	Female	Nigeria	1/10/11		Full-Time on Permanent Contract	Lagos	CCRO	G
119	1483	Male	Armenia	3/11/20	7/13/20	Terminated	Yerevan	CD
120	1361	Female	Armenia	8/8/18		Full-Time on Permanent Contract	Yerevan	CCRO	G
121	1315	Female	Armenia	8/20/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
122	490	Female	Armenia	7/1/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
123	762	Female	Armenia	4/1/21		Full-Time on Permanent Contract	Yerevan	CCRO	G
124	446	Female	Armenia	5/8/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
125	1317	Male	Armenia	8/8/19		Full-Time on Permanent Contract	Yerevan	CCRO	G
126	1348	Female	Armenia	4/1/19	4/27/20	Terminated	Yerevan	CCRO
127	689	Female	Armenia	3/9/21		Full-Time on Permanent Contract	Yerevan	CCRO	G
128	633	Female	Armenia	12/15/20		Full-Time on Permanent Contract	Yerevan	CCRO	G
129	1391	Female	Nigeria	8/1/18		Full-Time on Permanent Contract	Abuja	CCRO	F
130	210	Male	Russian Federation	9/1/10		Full-Time on Permanent Contract	Moscow	CTO	C
131	410	Male	Russian Federation	4/12/10		Full-Time on Permanent Contract	Moscow	CTO	D
132	1419	Male	Nigeria	11/6/19		Full-Time on Permanent Contract	Lagos	CCRO	G
133	626	Male	Kenya	12/7/20		Full-Time on Permanent Contract	Nairobi	COO	E
134	647	Male	Armenia	1/18/21		Full-Time on Permanent Contract	Yerevan	CCRO	G
135	782	Female	Armenia	4/15/21		Full-Time on Permanent Contract	Yerevan	CCRO	G
136	775	Male	Armenia	4/12/21		Full-Time on Permanent Contract	Yerevan	CCRO	G
137	783	Male	Armenia	4/15/21		Full-Time on Permanent Contract	Yerevan	CCRO	F
138	1353	Male	Armenia	7/26/19	8/31/20	Terminated	Yerevan	CCRO
139	377	Male	Russian Federation	9/14/07		Full-Time on Permanent Contract	Moscow	CISO	D
140	692	Male	Armenia	3/4/21		Full-Time on Permanent Contract	Yerevan	CD	Entry Level
141	768	Female	Armenia	4/5/21		Full-Time on Permanent Contract	Yerevan	CD	Entry Level
142	550	Female	Armenia	9/7/20		Full-Time on Permanent Contract	Yerevan	CCRO	Entry Level
143	797	Female	Armenia	5/6/21		Full-Time on Permanent Contract	Yerevan	CCRO	Entry Level
144	551	Male	Armenia	9/7/20		Full-Time on Permanent Contract	Yerevan	CCRO	Entry Level
145	567	Female	Armenia	10/1/20		Full-Time on Permanent Contract	Yerevan	CCRO	Entry Level
146	774	Female	Armenia	4/12/21		Full-Time on Permanent Contract	Yerevan	CCRO	Entry Level
147	754	Female	Armenia	3/22/21		Full-Time on Permanent Contract	Yerevan	CCRO	Entry Level
148	738	Female	Armenia	3/17/21	5/20/21	Terminated	Yerevan	CCRO	Entry Level
149	1371	Male	Cyprus	11/16/09	5/4/20	Terminated	Lagos	CCO
150	1388	Male	Nigeria	7/19/18	10/13/20	Terminated	Port Harcourt	CCO
151	1372	Male	Nigeria	11/19/11		Full-Time on Permanent Contract	Lagos	CCO	E
152	1403	Male	Nigeria	10/23/19		Full-Time on Permanent Contract	Lagos	CCRO	F
153	1381	Female	Nigeria	11/13/17		Full-Time on Permanent Contract	Lagos	CCRO	F
154	1418	Female	Nigeria	11/6/19		Full-Time on Permanent Contract	Lagos	CCRO	F
155	1413	Female	Nigeria	6/7/17		Full-Time on Permanent Contract	Lagos	CCRO	F
156	1407	Male	Nigeria	10/14/15		Full-Time on Permanent Contract	Abuja	CCRO	F
157	1374	Female	Nigeria	2/3/14		Full-Time on Permanent Contract	Lagos	CCRO	F
158	1415	Male	Nigeria	5/3/18		Full-Time on Permanent Contract	Lagos	CCRO	F
159	1412	Female	Nigeria	5/25/17		Full-Time on Permanent Contract	Lagos	CCRO	F
160	1373	Female	Nigeria	8/18/14		Full-Time on Permanent Contract	Lagos	CCRO	F
161	1411	Female	Nigeria	1/19/17		Full-Time on Permanent Contract	Lagos	CCRO	F
162	1414	Male	Nigeria	10/8/18		Full-Time on Permanent Contract	Abuja	CCRO	F
163	1370	Male	Nigeria	10/31/11		Full-Time on Permanent Contract	Lagos	CCRO	G
164	1421	Male	Armenia	2/3/20		Full-Time on Permanent Contract	Yerevan	CFO	F
165	497	Female	Armenia	7/15/20	1/8/21	Terminated	Yerevan	COO
166	1408	Male	Nigeria	10/12/15		Full-Time on Permanent Contract	Lagos	CCO	F
167	444	Male	Nigeria	4/27/20		Full-Time on Permanent Contract	Lagos	CCRO	G
168	1417	Male	Nigeria	11/6/19		Full-Time on Permanent Contract	Lagos	CCRO	G
169	623	Female	Kenya	12/7/20	6/8/21	Terminated	Nairobi	CFO	F
170	699	Female	Armenia	3/15/21	4/7/21	Terminated	Yerevan	CCRO	F
171	544	Female	Armenia	9/1/20		Full-Time on Permanent Contract	Yerevan	CD	Entry Level
172	585	Female	Armenia	10/26/20		Full-Time on Permanent Contract	Yerevan	CD	Entry Level
173	773	Male	Armenia	4/12/21		Full-Time on Permanent Contract	Yerevan	CCRO	G
174	780	Male	Armenia	4/12/21		Provision of Services Agreement	Yerevan	CCRO	G
175	701	Male		12/5/16		Full-Time on Permanent Contract	Moscow	CISO	D
176	359	Male	Russian Federation	1/26/11		Full-Time on Permanent Contract	Moscow	CTO	D
Profile Report

Below is 2nd tab. This is where I would like my calculations to appear.

The boxes in rows 1:3 will impact the figures in both the "Headcount" & "Level" table. The blue box governs the dates. The purple box governs the directorate, gender, and location. By default, the headcount splits should show all the current heads and as you click the dropdowns (maybe to see the number of females in a specific location), the numbers in the Headcount & Level box will change accordingly.

Headcount calculation is determined as count the record if the "Hire Date" is LESS THAN OR EQUAL TO the date in the query cell ("Today's Date, Last Reporting Date, Selectable Date) AND the "Termination Date" is BLANK OR AFTER the date in the query cell. The assumption is if an employee has no Termination Date, they must still be with the company and should be included in the headcount.

I wasn't sure how to add a quick-select date for the selectable date, other than a dropdown, so I've left it blank. Maybe the free text is fine? Can this be done with COUNTIFS at all, as I can easily build those up should I need to? Any help is greatly appreciated.

HR Dashboard - Anonymous.xlsx
	A	B	C	D	E
1	Today's Date	6/10/21		Directorate	All
2	Last Reporting Date	5/31/21		Gender	All
3	Selectable Date			Location	All
4
5
6
7
8	Headcount
9
10	Employment Status	Today	Reporting Date	Selected Date
11	Terminated
12	Full-Time on Permanent Contract
13	Full-time on Fixed-term Contract
14	Provision of Services Agreement
15	Professional Service Provider Agreement
16	Part-Time on Permanent Contract
17
18
19
20
21	Level
22
23	Entry Level
24	A
25	B
26	C
27	D
28	E
29	F
30	G
31	N/A
32	Blank
Summary

Cell Formulas
Range	Formula
B1	B1	=TODAY()
B2	B2	=EOMONTH(B1,-1)

Cells with Data Validation
Cell	Allow	Criteria
E1	List	='Data Validation'!$C$2:$C$11
E2	List	='Data Validation'!$A$2:$A$4
E3	List	='Data Validation'!$E$2:$E$32

 

[JackDanIce]

You'd be better off using pivot tables, slicers and timelines to achieve this, which would remove the need for most of the cell formula and give more dynamics:
See if this video helps:

I watched above to to create one for an HR friend, definitely an hour well spent, don't be put off by the duration of the video or as a learning experience

 

[maxim642]

You'd be better off using pivot tables, slicers and timelines to achieve this, which would remove the need for most of the cell formula and give more dynamics:
See if this video helps:

I watched above to to create one for an HR friend, definitely an hour well spent, don't be put off by the duration of the video or as a learning experience

I've used pivot tables before, but thought as this calculation requires multiple criteria to count that perhaps a pivot couldn't do that, I'll check it out though, thanks.

 

[JackDanIce]

Pivot tables aggregate data, i.e. SUMIF. The timeline can control the date ranges for the resulting aggregations, and slicers for different gender options etc.

Your source data looks like it's in normalised form already so it's in a great state to pivot and then do the groupings you need