Formula to calculate headcount based on start/leave dates, governed by selectable dropdowns.

[maxim642]

The goal is to be able to view headcounts by cost centre and company-wide.

Columns H and I contain the start/leave data. If an employee's leave date is blank, that means they are still with the company and should be counted as current.

The user will input a time period and cost centre using the dropdowns in row 36.

Row 38 will show the headcount for the respective cost centre for the period selected above.

Row 39 will show the headcount for the company as a whole (so it will be all the cost centres).

If a cost centre and date period are chosen that have no data, please return "No records found.".

Below is an example of my dataset (thanks in advance):

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Full Name	Employee Number	Job Title	Division	Department	Employee Type	Manager Full Name	Start Date	Leaving Date	Location	Gender	Cost Centre		Architecture and Engineering		1985		Jan
2	Worker1	1	JT1	Sales1	Admin1	Employee	Man1	1/13/11		London	Male	Finance		Central Build Office		1986		Feb
3	Worker2	2	JT2	Sales2	Admin2	Employee	Man2	3/1/19		London	Male	Executive Team		City Development		1987		Mar
4	Worker3	3	JT3	Sales3	Admin3	Employee	Man3	1/11/21		London	Male	Executive Team		Customer Assurance and Delivery		1988		Apr
5	Worker4	4	JT4	Sales4	Admin4	Employee	Man4	1/1/12	7/15/20	London	Male	Executive Team		Executive Team		1989		May
6	Worker5	5	JT5	Sales5	Admin5	Employee	Man5	1/4/21		London	Female	Legal		Field Operations		1990		Jun
7	Worker6	6	JT6	Sales6	Admin6	Employee	Man6	9/30/19		Milton Keynes	Male	Executive Team		Finance		1991		Jul
8	Worker7	7	JT7	Sales7	Admin7	Employee	Man7	1/16/14		London	Male	Executive Team		Legal		1992		Aug
9	Worker8	8	JT8	Sales8	Admin8	Employee	Man8	3/3/86	9/30/20	Home	Male	Executive Team		Network Build - North West		1993		Sep
10	Worker9	9	JT9	Sales9	Admin9	Employee	Man9	9/1/20	9/25/20	Home Based		Network Build - North West		Sales		1994		Oct
11	Worker10	10	JT10	Sales10	Admin10	Employee	Man10	8/6/12		London	Male	Executive Team		Software Development		1995		Nov
12	Worker11	11	JT11	Sales11	Admin11	Employee	Man11	9/9/19	2/29/16	London	Male	Executive Team		Strategy		1996		Dec
13	Worker12	12	JT12	Sales12	Admin12	Employee	Man12	5/21/18		London	Female	Legal				1997
14	Worker13	13	JT13	Sales13	Admin13	Employee	Man13	12/3/18		London	Male	Executive Team				1998
15	Worker14	14	JT14	Sales14	Admin14	Employee	Man14	4/1/03		Telford	Female	Executive Team				1999
16	Worker15	15	JT15	Sales15	Admin15	Employee	Man15	5/1/12	7/1/19	London	Male	Network Build - North West				2000
17	Worker16	16	JT16	Sales16	Admin16	Employee	Man16	4/2/19		London	Male	Finance				2001
18	Worker17	17	JT17	Sales17	Admin17	Employee	Man17	9/7/20		London	Male	Customer Assurance and Delivery				2002
19	Worker18	18	JT18	Sales18	Admin18	Employee	Man18	12/1/13		London	Male	Executive Team				2003
20	Worker19	19	JT19	Sales19	Admin19	Employee	Man19	4/1/20	9/11/20	London	Male	Software Development				2004
21	Worker20	20	JT20	Sales20	Admin20	Employee	Man20	9/1/20	10/30/20	Field Based	Male	Finance				2005
22	Worker21	21	JT21	Sales21	Admin21	Employee	Man21	2/17/16	2/28/20	Project Based	Male	Central Build Office				2006
23	Worker22	22	JT22	Sales22	Admin22	Employee	Man22	8/11/20		Home	Female	Network Build - North West				2007
24	Worker23	23	JT23	Sales23	Admin23	Employee	Man23	1/11/16		London	Male	Architecture and Engineering				2008
25	Worker24	24	JT24	Sales24	Admin24	Employee Fixed Term	Man24	2/10/20	6/30/20	London	Female	Customer Assurance and Delivery				2009
26	Worker25	25	JT25	Sales25	Admin25	Employee	Man25	5/2/17		London	Male	Sales				2010
27	Worker26	26	JT26	Sales26	Admin26	Employee	Man26	9/7/09		London	Male	Legal				2011
28	Worker27	27	JT27	Sales27	Admin27	Employee	Man27	9/1/16		London	Male	Strategy				2012
29	Worker28	28	JT28	Sales28	Admin28	Employee	Man28	10/5/15		Home	Male	Field Operations				2013
30	Worker29	29	JT29	Sales29	Admin29	Employee	Man29	8/4/17		Home	Male	City Development				2014
31																2015
32																2016
33																2017
34																2018
35	Cost Centre	Year	Month													2019
36	Architecture and Engineering	2013	Jan													2020
37																2021
38	Cost Centre Headcount	?														2022
39	Company Headcount	?
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A36	List	=$N$1:$N$12
B36	List	=$P$1:$P$38
C36	List	=$R$1:$R$12

 

[Toadstool]

Hi Maxim642,

I have assumed you want headcount as of the 1st of the month. If you want end of month then you'll need to wrap an EOMONTH around the DATE.

The end dates are indeed a single blank. Be careful you don't press delete on the cell as it will look the same but will not be counted.

Maxim642.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Full Name	Employee Number	Job Title	Division	Department	Employee Type	Manager Full Name	Start Date	Leaving Date	Location	Gender	Cost Centre		Architecture and Engineering		1985		Jan
2	Worker1	1	JT1	Sales1	Admin1	Employee	Man1	13-Jan-11		London	Male	Finance		Central Build Office		1986		Feb
3	Worker2	2	JT2	Sales2	Admin2	Employee	Man2	01-Mar-19		London	Male	Executive Team		City Development		1987		Mar
4	Worker3	3	JT3	Sales3	Admin3	Employee	Man3	11-Jan-21		London	Male	Executive Team		Customer Assurance and Delivery		1988		Apr
5	Worker4	4	JT4	Sales4	Admin4	Employee	Man4	01-Jan-12	15-Jul-20	London	Male	Executive Team		Executive Team		1989		May
6	Worker5	5	JT5	Sales5	Admin5	Employee	Man5	04-Jan-21		London	Female	Legal		Field Operations		1990		Jun
7	Worker6	6	JT6	Sales6	Admin6	Employee	Man6	30-Sep-19		Milton Keynes	Male	Executive Team		Finance		1991		Jul
8	Worker7	7	JT7	Sales7	Admin7	Employee	Man7	16-Jan-14		London	Male	Executive Team		Legal		1992		Aug
9	Worker8	8	JT8	Sales8	Admin8	Employee	Man8	03-Mar-86	30-Sep-20	Home	Male	Executive Team		Network Build - North West		1993		Sep
10	Worker9	9	JT9	Sales9	Admin9	Employee	Man9	01-Sep-20	25-Sep-20	Home Based		Network Build - North West		Sales		1994		Oct
11	Worker10	10	JT10	Sales10	Admin10	Employee	Man10	06-Aug-12		London	Male	Executive Team		Software Development		1995		Nov
12	Worker11	11	JT11	Sales11	Admin11	Employee	Man11	09-Sep-19	29-Feb-16	London	Male	Executive Team		Strategy		1996		Dec
13	Worker12	12	JT12	Sales12	Admin12	Employee	Man12	21-May-18		London	Female	Legal				1997
14	Worker13	13	JT13	Sales13	Admin13	Employee	Man13	03-Dec-18		London	Male	Executive Team				1998
15	Worker14	14	JT14	Sales14	Admin14	Employee	Man14	01-Apr-03		Telford	Female	Executive Team				1999
16	Worker15	15	JT15	Sales15	Admin15	Employee	Man15	01-May-12	01-Jul-19	London	Male	Network Build - North West				2000
17	Worker16	16	JT16	Sales16	Admin16	Employee	Man16	02-Apr-19		London	Male	Finance				2001
18	Worker17	17	JT17	Sales17	Admin17	Employee	Man17	07-Sep-20		London	Male	Customer Assurance and Delivery				2002
19	Worker18	18	JT18	Sales18	Admin18	Employee	Man18	01-Dec-13		London	Male	Executive Team				2003
20	Worker19	19	JT19	Sales19	Admin19	Employee	Man19	01-Apr-20	11-Sep-20	London	Male	Software Development				2004
21	Worker20	20	JT20	Sales20	Admin20	Employee	Man20	01-Sep-20	30-Oct-20	Field Based	Male	Finance				2005
22	Worker21	21	JT21	Sales21	Admin21	Employee	Man21	17-Feb-16	28-Feb-20	Project Based	Male	Central Build Office				2006
23	Worker22	22	JT22	Sales22	Admin22	Employee	Man22	11-Aug-20		Home	Female	Network Build - North West				2007
24	Worker23	23	JT23	Sales23	Admin23	Employee	Man23	11-Jan-16		London	Male	Architecture and Engineering				2008
25	Worker24	24	JT24	Sales24	Admin24	Employee Fixed Term	Man24	10-Feb-20	30-Jun-20	London	Female	Customer Assurance and Delivery				2009
26	Worker25	25	JT25	Sales25	Admin25	Employee	Man25	02-May-17		London	Male	Sales				2010
27	Worker26	26	JT26	Sales26	Admin26	Employee	Man26	07-Sep-09		London	Male	Legal				2011
28	Worker27	27	JT27	Sales27	Admin27	Employee	Man27	01-Sep-16		London	Male	Strategy				2012
29	Worker28	28	JT28	Sales28	Admin28	Employee	Man28	05-Oct-15		Home	Male	Field Operations				2013
30	Worker29	29	JT29	Sales29	Admin29	Employee	Man29	04-Aug-17		Home	Male	City Development				2014
31																2015
32																2016
33																2017
34																2018
35	Cost Centre	Year	Month													2019
36	Executive Team	2020	Aug													2020
37																2021
38	Cost Centre Headcount	8														2022
39	Company Headcount	18
Sheet1

Cell Formulas
Range	Formula
B38	B38	=COUNTIFS($L$2:$L$30,$A$36,$H$2:$H$30,"<="&DATE($B$36,MATCH($C$36,$R$1:$R$12,0),1),$I$2:$I$30," ")+COUNTIFS($L$2:$L$30,$A$36,$H$2:$H$30,"<="&DATE($B$36,MATCH($C$36,$R$1:$R$12,0),1),$I$2:$I$30,">="&DATE($B$36,MATCH($C$36,$R$1:$R$12,0),1))
B39	B39	=COUNTIFS($H$2:$H$30,"<="&DATE($B$36,MATCH($C$36,$R$1:$R$12,0),1),$I$2:$I$30," ")+COUNTIFS($H$2:$H$30,"<="&DATE($B$36,MATCH($C$36,$R$1:$R$12,0),1),$I$2:$I$30,">="&DATE($B$36,MATCH($C$36,$R$1:$R$12,0),1))

Cells with Data Validation
Cell	Allow	Criteria
A36	List	=$N$1:$N$12
B36	List	=$P$1:$P$38
C36	List	=$R$1:$R$12

 

[maxim642]

Hi Maxim642,

I have assumed you want headcount as of the 1st of the month. If you want end of month then you'll need to wrap an EOMONTH around the DATE.

The end dates are indeed a single blank. Be careful you don't press delete on the cell as it will look the same but will not be counted.

Maxim642.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Full Name	Employee Number	Job Title	Division	Department	Employee Type	Manager Full Name	Start Date	Leaving Date	Location	Gender	Cost Centre		Architecture and Engineering		1985		Jan
2	Worker1	1	JT1	Sales1	Admin1	Employee	Man1	13-Jan-11		London	Male	Finance		Central Build Office		1986		Feb
3	Worker2	2	JT2	Sales2	Admin2	Employee	Man2	01-Mar-19		London	Male	Executive Team		City Development		1987		Mar
4	Worker3	3	JT3	Sales3	Admin3	Employee	Man3	11-Jan-21		London	Male	Executive Team		Customer Assurance and Delivery		1988		Apr
5	Worker4	4	JT4	Sales4	Admin4	Employee	Man4	01-Jan-12	15-Jul-20	London	Male	Executive Team		Executive Team		1989		May
6	Worker5	5	JT5	Sales5	Admin5	Employee	Man5	04-Jan-21		London	Female	Legal		Field Operations		1990		Jun
7	Worker6	6	JT6	Sales6	Admin6	Employee	Man6	30-Sep-19		Milton Keynes	Male	Executive Team		Finance		1991		Jul
8	Worker7	7	JT7	Sales7	Admin7	Employee	Man7	16-Jan-14		London	Male	Executive Team		Legal		1992		Aug
9	Worker8	8	JT8	Sales8	Admin8	Employee	Man8	03-Mar-86	30-Sep-20	Home	Male	Executive Team		Network Build - North West		1993		Sep
10	Worker9	9	JT9	Sales9	Admin9	Employee	Man9	01-Sep-20	25-Sep-20	Home Based		Network Build - North West		Sales		1994		Oct
11	Worker10	10	JT10	Sales10	Admin10	Employee	Man10	06-Aug-12		London	Male	Executive Team		Software Development		1995		Nov
12	Worker11	11	JT11	Sales11	Admin11	Employee	Man11	09-Sep-19	29-Feb-16	London	Male	Executive Team		Strategy		1996		Dec
13	Worker12	12	JT12	Sales12	Admin12	Employee	Man12	21-May-18		London	Female	Legal				1997
14	Worker13	13	JT13	Sales13	Admin13	Employee	Man13	03-Dec-18		London	Male	Executive Team				1998
15	Worker14	14	JT14	Sales14	Admin14	Employee	Man14	01-Apr-03		Telford	Female	Executive Team				1999
16	Worker15	15	JT15	Sales15	Admin15	Employee	Man15	01-May-12	01-Jul-19	London	Male	Network Build - North West				2000
17	Worker16	16	JT16	Sales16	Admin16	Employee	Man16	02-Apr-19		London	Male	Finance				2001
18	Worker17	17	JT17	Sales17	Admin17	Employee	Man17	07-Sep-20		London	Male	Customer Assurance and Delivery				2002
19	Worker18	18	JT18	Sales18	Admin18	Employee	Man18	01-Dec-13		London	Male	Executive Team				2003
20	Worker19	19	JT19	Sales19	Admin19	Employee	Man19	01-Apr-20	11-Sep-20	London	Male	Software Development				2004
21	Worker20	20	JT20	Sales20	Admin20	Employee	Man20	01-Sep-20	30-Oct-20	Field Based	Male	Finance				2005
22	Worker21	21	JT21	Sales21	Admin21	Employee	Man21	17-Feb-16	28-Feb-20	Project Based	Male	Central Build Office				2006
23	Worker22	22	JT22	Sales22	Admin22	Employee	Man22	11-Aug-20		Home	Female	Network Build - North West				2007
24	Worker23	23	JT23	Sales23	Admin23	Employee	Man23	11-Jan-16		London	Male	Architecture and Engineering				2008
25	Worker24	24	JT24	Sales24	Admin24	Employee Fixed Term	Man24	10-Feb-20	30-Jun-20	London	Female	Customer Assurance and Delivery				2009
26	Worker25	25	JT25	Sales25	Admin25	Employee	Man25	02-May-17		London	Male	Sales				2010
27	Worker26	26	JT26	Sales26	Admin26	Employee	Man26	07-Sep-09		London	Male	Legal				2011
28	Worker27	27	JT27	Sales27	Admin27	Employee	Man27	01-Sep-16		London	Male	Strategy				2012
29	Worker28	28	JT28	Sales28	Admin28	Employee	Man28	05-Oct-15		Home	Male	Field Operations				2013
30	Worker29	29	JT29	Sales29	Admin29	Employee	Man29	04-Aug-17		Home	Male	City Development				2014
31																2015
32																2016
33																2017
34																2018
35	Cost Centre	Year	Month													2019
36	Executive Team	2020	Aug													2020
37																2021
38	Cost Centre Headcount	8														2022
39	Company Headcount	18
Sheet1

Cell Formulas
Range	Formula
B38	B38	=COUNTIFS($L$2:$L$30,$A$36,$H$2:$H$30,"<="&DATE($B$36,MATCH($C$36,$R$1:$R$12,0),1),$I$2:$I$30," ")+COUNTIFS($L$2:$L$30,$A$36,$H$2:$H$30,"<="&DATE($B$36,MATCH($C$36,$R$1:$R$12,0),1),$I$2:$I$30,">="&DATE($B$36,MATCH($C$36,$R$1:$R$12,0),1))
B39	B39	=COUNTIFS($H$2:$H$30,"<="&DATE($B$36,MATCH($C$36,$R$1:$R$12,0),1),$I$2:$I$30," ")+COUNTIFS($H$2:$H$30,"<="&DATE($B$36,MATCH($C$36,$R$1:$R$12,0),1),$I$2:$I$30,">="&DATE($B$36,MATCH($C$36,$R$1:$R$12,0),1))

Cells with Data Validation
Cell	Allow	Criteria
A36	List	=$N$1:$N$12
B36	List	=$P$1:$P$38
C36	List	=$R$1:$R$12

Great job! This has worked perfectly. Thanks for your help, and good tip about the blanks!

 

[Toadstool]

You're welcome and thanks for the feedback.