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

maxim642

New Member
Joined
Feb 4, 2021
Messages
27
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFGHIJKLMNOPQR
1Full NameEmployee NumberJob TitleDivisionDepartmentEmployee TypeManager Full NameStart DateLeaving DateLocationGenderCost CentreArchitecture and Engineering1985Jan
2Worker11JT1Sales1Admin1EmployeeMan11/13/11 LondonMaleFinanceCentral Build Office1986Feb
3Worker22JT2Sales2Admin2EmployeeMan23/1/19 LondonMaleExecutive TeamCity Development1987Mar
4Worker33JT3Sales3Admin3EmployeeMan31/11/21 LondonMaleExecutive TeamCustomer Assurance and Delivery1988Apr
5Worker44JT4Sales4Admin4EmployeeMan41/1/127/15/20LondonMaleExecutive TeamExecutive Team1989May
6Worker55JT5Sales5Admin5EmployeeMan51/4/21 LondonFemaleLegalField Operations1990Jun
7Worker66JT6Sales6Admin6EmployeeMan69/30/19 Milton KeynesMaleExecutive TeamFinance1991Jul
8Worker77JT7Sales7Admin7EmployeeMan71/16/14 LondonMaleExecutive TeamLegal1992Aug
9Worker88JT8Sales8Admin8EmployeeMan83/3/869/30/20HomeMaleExecutive TeamNetwork Build - North West1993Sep
10Worker99JT9Sales9Admin9EmployeeMan99/1/209/25/20Home Based Network Build - North WestSales1994Oct
11Worker1010JT10Sales10Admin10EmployeeMan108/6/12 LondonMaleExecutive TeamSoftware Development1995Nov
12Worker1111JT11Sales11Admin11EmployeeMan119/9/192/29/16LondonMaleExecutive TeamStrategy1996Dec
13Worker1212JT12Sales12Admin12EmployeeMan125/21/18 LondonFemaleLegal1997
14Worker1313JT13Sales13Admin13EmployeeMan1312/3/18 LondonMaleExecutive Team1998
15Worker1414JT14Sales14Admin14EmployeeMan144/1/03 TelfordFemaleExecutive Team1999
16Worker1515JT15Sales15Admin15EmployeeMan155/1/127/1/19LondonMaleNetwork Build - North West2000
17Worker1616JT16Sales16Admin16EmployeeMan164/2/19 LondonMaleFinance2001
18Worker1717JT17Sales17Admin17EmployeeMan179/7/20 LondonMaleCustomer Assurance and Delivery2002
19Worker1818JT18Sales18Admin18EmployeeMan1812/1/13 LondonMaleExecutive Team2003
20Worker1919JT19Sales19Admin19EmployeeMan194/1/209/11/20LondonMaleSoftware Development2004
21Worker2020JT20Sales20Admin20EmployeeMan209/1/2010/30/20Field BasedMaleFinance2005
22Worker2121JT21Sales21Admin21EmployeeMan212/17/162/28/20Project BasedMaleCentral Build Office2006
23Worker2222JT22Sales22Admin22EmployeeMan228/11/20 HomeFemaleNetwork Build - North West2007
24Worker2323JT23Sales23Admin23EmployeeMan231/11/16 LondonMaleArchitecture and Engineering2008
25Worker2424JT24Sales24Admin24Employee Fixed TermMan242/10/206/30/20LondonFemaleCustomer Assurance and Delivery2009
26Worker2525JT25Sales25Admin25EmployeeMan255/2/17 LondonMaleSales2010
27Worker2626JT26Sales26Admin26EmployeeMan269/7/09 LondonMaleLegal2011
28Worker2727JT27Sales27Admin27EmployeeMan279/1/16 LondonMaleStrategy2012
29Worker2828JT28Sales28Admin28EmployeeMan2810/5/15 HomeMaleField Operations2013
30Worker2929JT29Sales29Admin29EmployeeMan298/4/17 HomeMaleCity Development2014
312015
322016
332017
342018
35Cost CentreYearMonth2019
36Architecture and Engineering2013Jan2020
372021
38Cost Centre Headcount?2022
39Company Headcount?
Sheet1
Cells with Data Validation
CellAllowCriteria
A36List=$N$1:$N$12
B36List=$P$1:$P$38
C36List=$R$1:$R$12
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQR
1Full NameEmployee NumberJob TitleDivisionDepartmentEmployee TypeManager Full NameStart DateLeaving DateLocationGenderCost CentreArchitecture and Engineering1985Jan
2Worker11JT1Sales1Admin1EmployeeMan113-Jan-11 LondonMaleFinanceCentral Build Office1986Feb
3Worker22JT2Sales2Admin2EmployeeMan201-Mar-19 LondonMaleExecutive TeamCity Development1987Mar
4Worker33JT3Sales3Admin3EmployeeMan311-Jan-21 LondonMaleExecutive TeamCustomer Assurance and Delivery1988Apr
5Worker44JT4Sales4Admin4EmployeeMan401-Jan-1215-Jul-20LondonMaleExecutive TeamExecutive Team1989May
6Worker55JT5Sales5Admin5EmployeeMan504-Jan-21 LondonFemaleLegalField Operations1990Jun
7Worker66JT6Sales6Admin6EmployeeMan630-Sep-19 Milton KeynesMaleExecutive TeamFinance1991Jul
8Worker77JT7Sales7Admin7EmployeeMan716-Jan-14 LondonMaleExecutive TeamLegal1992Aug
9Worker88JT8Sales8Admin8EmployeeMan803-Mar-8630-Sep-20HomeMaleExecutive TeamNetwork Build - North West1993Sep
10Worker99JT9Sales9Admin9EmployeeMan901-Sep-2025-Sep-20Home Based Network Build - North WestSales1994Oct
11Worker1010JT10Sales10Admin10EmployeeMan1006-Aug-12 LondonMaleExecutive TeamSoftware Development1995Nov
12Worker1111JT11Sales11Admin11EmployeeMan1109-Sep-1929-Feb-16LondonMaleExecutive TeamStrategy1996Dec
13Worker1212JT12Sales12Admin12EmployeeMan1221-May-18 LondonFemaleLegal1997
14Worker1313JT13Sales13Admin13EmployeeMan1303-Dec-18 LondonMaleExecutive Team1998
15Worker1414JT14Sales14Admin14EmployeeMan1401-Apr-03 TelfordFemaleExecutive Team1999
16Worker1515JT15Sales15Admin15EmployeeMan1501-May-1201-Jul-19LondonMaleNetwork Build - North West2000
17Worker1616JT16Sales16Admin16EmployeeMan1602-Apr-19 LondonMaleFinance2001
18Worker1717JT17Sales17Admin17EmployeeMan1707-Sep-20 LondonMaleCustomer Assurance and Delivery2002
19Worker1818JT18Sales18Admin18EmployeeMan1801-Dec-13 LondonMaleExecutive Team2003
20Worker1919JT19Sales19Admin19EmployeeMan1901-Apr-2011-Sep-20LondonMaleSoftware Development2004
21Worker2020JT20Sales20Admin20EmployeeMan2001-Sep-2030-Oct-20Field BasedMaleFinance2005
22Worker2121JT21Sales21Admin21EmployeeMan2117-Feb-1628-Feb-20Project BasedMaleCentral Build Office2006
23Worker2222JT22Sales22Admin22EmployeeMan2211-Aug-20 HomeFemaleNetwork Build - North West2007
24Worker2323JT23Sales23Admin23EmployeeMan2311-Jan-16 LondonMaleArchitecture and Engineering2008
25Worker2424JT24Sales24Admin24Employee Fixed TermMan2410-Feb-2030-Jun-20LondonFemaleCustomer Assurance and Delivery2009
26Worker2525JT25Sales25Admin25EmployeeMan2502-May-17 LondonMaleSales2010
27Worker2626JT26Sales26Admin26EmployeeMan2607-Sep-09 LondonMaleLegal2011
28Worker2727JT27Sales27Admin27EmployeeMan2701-Sep-16 LondonMaleStrategy2012
29Worker2828JT28Sales28Admin28EmployeeMan2805-Oct-15 HomeMaleField Operations2013
30Worker2929JT29Sales29Admin29EmployeeMan2904-Aug-17 HomeMaleCity Development2014
312015
322016
332017
342018
35Cost CentreYearMonth2019
36Executive Team2020Aug2020
372021
38Cost Centre Headcount82022
39Company Headcount18
Sheet1
Cell Formulas
RangeFormula
B38B38=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))
B39B39=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
CellAllowCriteria
A36List=$N$1:$N$12
B36List=$P$1:$P$38
C36List=$R$1:$R$12
 
Solution

maxim642

New Member
Joined
Feb 4, 2021
Messages
27
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFGHIJKLMNOPQR
1Full NameEmployee NumberJob TitleDivisionDepartmentEmployee TypeManager Full NameStart DateLeaving DateLocationGenderCost CentreArchitecture and Engineering1985Jan
2Worker11JT1Sales1Admin1EmployeeMan113-Jan-11 LondonMaleFinanceCentral Build Office1986Feb
3Worker22JT2Sales2Admin2EmployeeMan201-Mar-19 LondonMaleExecutive TeamCity Development1987Mar
4Worker33JT3Sales3Admin3EmployeeMan311-Jan-21 LondonMaleExecutive TeamCustomer Assurance and Delivery1988Apr
5Worker44JT4Sales4Admin4EmployeeMan401-Jan-1215-Jul-20LondonMaleExecutive TeamExecutive Team1989May
6Worker55JT5Sales5Admin5EmployeeMan504-Jan-21 LondonFemaleLegalField Operations1990Jun
7Worker66JT6Sales6Admin6EmployeeMan630-Sep-19 Milton KeynesMaleExecutive TeamFinance1991Jul
8Worker77JT7Sales7Admin7EmployeeMan716-Jan-14 LondonMaleExecutive TeamLegal1992Aug
9Worker88JT8Sales8Admin8EmployeeMan803-Mar-8630-Sep-20HomeMaleExecutive TeamNetwork Build - North West1993Sep
10Worker99JT9Sales9Admin9EmployeeMan901-Sep-2025-Sep-20Home Based Network Build - North WestSales1994Oct
11Worker1010JT10Sales10Admin10EmployeeMan1006-Aug-12 LondonMaleExecutive TeamSoftware Development1995Nov
12Worker1111JT11Sales11Admin11EmployeeMan1109-Sep-1929-Feb-16LondonMaleExecutive TeamStrategy1996Dec
13Worker1212JT12Sales12Admin12EmployeeMan1221-May-18 LondonFemaleLegal1997
14Worker1313JT13Sales13Admin13EmployeeMan1303-Dec-18 LondonMaleExecutive Team1998
15Worker1414JT14Sales14Admin14EmployeeMan1401-Apr-03 TelfordFemaleExecutive Team1999
16Worker1515JT15Sales15Admin15EmployeeMan1501-May-1201-Jul-19LondonMaleNetwork Build - North West2000
17Worker1616JT16Sales16Admin16EmployeeMan1602-Apr-19 LondonMaleFinance2001
18Worker1717JT17Sales17Admin17EmployeeMan1707-Sep-20 LondonMaleCustomer Assurance and Delivery2002
19Worker1818JT18Sales18Admin18EmployeeMan1801-Dec-13 LondonMaleExecutive Team2003
20Worker1919JT19Sales19Admin19EmployeeMan1901-Apr-2011-Sep-20LondonMaleSoftware Development2004
21Worker2020JT20Sales20Admin20EmployeeMan2001-Sep-2030-Oct-20Field BasedMaleFinance2005
22Worker2121JT21Sales21Admin21EmployeeMan2117-Feb-1628-Feb-20Project BasedMaleCentral Build Office2006
23Worker2222JT22Sales22Admin22EmployeeMan2211-Aug-20 HomeFemaleNetwork Build - North West2007
24Worker2323JT23Sales23Admin23EmployeeMan2311-Jan-16 LondonMaleArchitecture and Engineering2008
25Worker2424JT24Sales24Admin24Employee Fixed TermMan2410-Feb-2030-Jun-20LondonFemaleCustomer Assurance and Delivery2009
26Worker2525JT25Sales25Admin25EmployeeMan2502-May-17 LondonMaleSales2010
27Worker2626JT26Sales26Admin26EmployeeMan2607-Sep-09 LondonMaleLegal2011
28Worker2727JT27Sales27Admin27EmployeeMan2701-Sep-16 LondonMaleStrategy2012
29Worker2828JT28Sales28Admin28EmployeeMan2805-Oct-15 HomeMaleField Operations2013
30Worker2929JT29Sales29Admin29EmployeeMan2904-Aug-17 HomeMaleCity Development2014
312015
322016
332017
342018
35Cost CentreYearMonth2019
36Executive Team2020Aug2020
372021
38Cost Centre Headcount82022
39Company Headcount18
Sheet1
Cell Formulas
RangeFormula
B38B38=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))
B39B39=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
CellAllowCriteria
A36List=$N$1:$N$12
B36List=$P$1:$P$38
C36List=$R$1:$R$12
Great job! This has worked perfectly. Thanks for your help, and good tip about the blanks!
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
You're welcome and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top