Calculate headcount number based on "Hire Date" & "Termination Date".

maxim642

Board Regular
Joined
Feb 4, 2021
Messages
91
Office Version
  1. 365
Platform
  1. MacOS
I have included some sample data to use as a reference point.

Based on the dates in K1 & K2, I would like to have a formula in K4 to show me the headcount as of "today", and in K5 it will show the headcount as of the end of the previous month.

To headcount will be determined by columns "G" & "H".

For the headcount today calculation = count those employees with both a hire date EQUAL to or EARLIER than K2 AND a termination date GREATER than or EQUAL to K2.
For the headcount P.R.D. calculation = count those employees with both a hire date EQUAL to or EARLIER than K3 AND a termination date GREATER than or EQUAL to K3.

Where the termination date is blank, the employee is counted as still working with the company and should be included in the company.

Disregard all the other fields in the dataset, only "Hire Date" & "Termination Date" are being used for this calculation.

My data set begins in A1. Sample below.

Thank you.

Untitled Spreadsheet
Employee #First name Last nameStatusGenderBirth DateCountryHire DateTermination DateToday's Date09/06/2021
1Empl X1InactiveFemale03/12/1985Viet Nam01/06/201730/09/2020Previous Reporting Day31/05/2021
2Empl X2ActiveMale27/02/1981Nigeria04/08/2010
3Empl X3ActiveMale13/06/1983Armenia04/04/2016Headcount - Today
4Empl X4InactiveMale31/05/1983Chile12/06/201714/08/2020Headcount - P.R.D
5Empl X5ActiveFemale11/04/1988Armenia01/08/201601/06/2019
6Empl X6ActiveMale24/12/1982Nigeria06/08/2020
7Empl X7ActiveMale30/06/1981Nigeria03/01/201106/07/2019
8Empl X8ActiveMale08/12/1980Kenya01/06/2020
9Empl X9ActiveMale28/01/1994Armenia22/04/2021
10Empl X10ActiveMale26/09/1989Armenia01/02/2021
11Empl X11InactiveFemale14/04/1982Armenia15/07/201916/06/2020
12Empl X12InactiveMale15/04/1982Kenya14/05/201626/08/2020
13Empl X13InactiveMale16/04/1982Kenya31/01/2011
14Empl X14ActiveMale17/04/1982Armenia03/06/2016
15Empl X15ActiveFemale18/04/1982Armenia07/08/202004/06/2021
16Empl X16ActiveFemale19/04/1982Chile04/08/2020
17Empl X17InactiveMale20/04/1982Chile06/11/2021
18Empl X18InactiveFemale21/04/1982Armenia07/09/2021
19Empl X19ActiveFemale22/04/1982Nigeria05/06/200207/08/2018
20Empl X20InactiveFemale23/04/1982Nigeria05/07/2002
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
How about
Excel Formula:
=SUMPRODUCT((G3:G22<=K2)*((H3:H22>=K2)+(H3:H22="")))
 
Upvote 0
How about
Excel Formula:
=SUMPRODUCT((G3:G22<=K2)*((H3:H22>=K2)+(H3:H22="")))
This is great, and it works.

How difficult would it be to include a filter in this formula, or can you even use COUNTIFS with SUMPRODUCT? I should have mentioned from the outset that I'd like to be able to easily build up the formula with additional criteria (like a COUNTIFS). For example, the formula you have provided is perfect for giving the total headcount, but what if I wanted to split that by GENDER or COUNTRY?

Thanks again for your help, much appreciated.
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEFGHIJK
1
2Employee #First name Last nameStatusGenderBirth DateCountryHire DateTermination DateToday's Date09/06/2021
31Empl X1InactiveFemale03/12/1985Viet Nam01/06/201730/09/2020Previous Reporting Day31/05/2021
42Empl X2ActiveMale27/02/1981Nigeria04/08/2010Gendermale
53Empl X3ActiveMale13/06/1983Armenia04/04/2016
64Empl X4InactiveMale31/05/1983Chile12/06/201714/08/2020Headcount - Today8
75Empl X5ActiveFemale11/04/1988Armenia01/08/201601/06/2019Headcount - P.R.D
86Empl X6ActiveMale24/12/1982Nigeria06/08/2020
97Empl X7ActiveMale30/06/1981Nigeria03/01/201106/07/2019
108Empl X8ActiveMale08/12/1980Kenya01/06/2020
119Empl X9ActiveMale28/01/1994Armenia22/04/2021
1210Empl X10ActiveMale26/09/1989Armenia01/02/2021
1311Empl X11InactiveFemale14/04/1982Armenia15/07/201916/06/2020
1412Empl X12InactiveMale15/04/1982Kenya14/05/201626/08/2020
1513Empl X13InactiveMale16/04/1982Kenya31/01/2011
1614Empl X14ActiveMale17/04/1982Armenia03/06/2016
1715Empl X15ActiveFemale18/04/1982Armenia07/08/202004/06/2021
1816Empl X16ActiveFemale19/04/1982Chile04/08/2020
1917Empl X17InactiveMale20/04/1982Chile06/11/2021
2018Empl X18InactiveFemale21/04/1982Armenia07/09/2021
2119Empl X19ActiveFemale22/04/1982Nigeria05/06/200207/08/2018
2220Empl X20InactiveFemale23/04/1982Nigeria05/07/2002
Lists
Cell Formulas
RangeFormula
K6K6=SUMPRODUCT((G3:G22<=K2)*((H3:H22>=K2)+(H3:H22=""))*(IF(K4="",TRUE,D3:D22=K4)))
 
Upvote 0
Solution
How about
+Fluff 1.xlsm
ABCDEFGHIJK
1
2Employee #First name Last nameStatusGenderBirth DateCountryHire DateTermination DateToday's Date09/06/2021
31Empl X1InactiveFemale03/12/1985Viet Nam01/06/201730/09/2020Previous Reporting Day31/05/2021
42Empl X2ActiveMale27/02/1981Nigeria04/08/2010Gendermale
53Empl X3ActiveMale13/06/1983Armenia04/04/2016
64Empl X4InactiveMale31/05/1983Chile12/06/201714/08/2020Headcount - Today8
75Empl X5ActiveFemale11/04/1988Armenia01/08/201601/06/2019Headcount - P.R.D
86Empl X6ActiveMale24/12/1982Nigeria06/08/2020
97Empl X7ActiveMale30/06/1981Nigeria03/01/201106/07/2019
108Empl X8ActiveMale08/12/1980Kenya01/06/2020
119Empl X9ActiveMale28/01/1994Armenia22/04/2021
1210Empl X10ActiveMale26/09/1989Armenia01/02/2021
1311Empl X11InactiveFemale14/04/1982Armenia15/07/201916/06/2020
1412Empl X12InactiveMale15/04/1982Kenya14/05/201626/08/2020
1513Empl X13InactiveMale16/04/1982Kenya31/01/2011
1614Empl X14ActiveMale17/04/1982Armenia03/06/2016
1715Empl X15ActiveFemale18/04/1982Armenia07/08/202004/06/2021
1816Empl X16ActiveFemale19/04/1982Chile04/08/2020
1917Empl X17InactiveMale20/04/1982Chile06/11/2021
2018Empl X18InactiveFemale21/04/1982Armenia07/09/2021
2119Empl X19ActiveFemale22/04/1982Nigeria05/06/200207/08/2018
2220Empl X20InactiveFemale23/04/1982Nigeria05/07/2002
Lists
Cell Formulas
RangeFormula
K6K6=SUMPRODUCT((G3:G22<=K2)*((H3:H22>=K2)+(H3:H22=""))*(IF(K4="",TRUE,D3:D22=K4)))
Thank you very much for your help!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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
Back
Top