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

[maxim642]

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 name	Status	Gender	Birth Date	Country	Hire Date	Termination Date		Today's Date	09/06/2021
1	Empl X1	Inactive	Female	03/12/1985	Viet Nam	01/06/2017	30/09/2020		Previous Reporting Day	31/05/2021
2	Empl X2	Active	Male	27/02/1981	Nigeria	04/08/2010
3	Empl X3	Active	Male	13/06/1983	Armenia	04/04/2016			Headcount - Today
4	Empl X4	Inactive	Male	31/05/1983	Chile	12/06/2017	14/08/2020		Headcount - P.R.D
5	Empl X5	Active	Female	11/04/1988	Armenia	01/08/2016	01/06/2019
6	Empl X6	Active	Male	24/12/1982	Nigeria	06/08/2020
7	Empl X7	Active	Male	30/06/1981	Nigeria	03/01/2011	06/07/2019
8	Empl X8	Active	Male	08/12/1980	Kenya	01/06/2020
9	Empl X9	Active	Male	28/01/1994	Armenia	22/04/2021
10	Empl X10	Active	Male	26/09/1989	Armenia	01/02/2021
11	Empl X11	Inactive	Female	14/04/1982	Armenia	15/07/2019	16/06/2020
12	Empl X12	Inactive	Male	15/04/1982	Kenya	14/05/2016	26/08/2020
13	Empl X13	Inactive	Male	16/04/1982	Kenya	31/01/2011
14	Empl X14	Active	Male	17/04/1982	Armenia	03/06/2016
15	Empl X15	Active	Female	18/04/1982	Armenia	07/08/2020	04/06/2021
16	Empl X16	Active	Female	19/04/1982	Chile	04/08/2020
17	Empl X17	Inactive	Male	20/04/1982	Chile	06/11/2021
18	Empl X18	Inactive	Female	21/04/1982	Armenia	07/09/2021
19	Empl X19	Active	Female	22/04/1982	Nigeria	05/06/2002	07/08/2018
20	Empl X20	Inactive	Female	23/04/1982	Nigeria	05/07/2002

 

[Fluff]

How about

=SUMPRODUCT((G3:G22<=K2)*((H3:H22>=K2)+(H3:H22="")))

 

[maxim642]

How about

=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.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	Employee #	First name Last name	Status	Gender	Birth Date	Country	Hire Date	Termination Date		Today's Date	09/06/2021
3	1	Empl X1	Inactive	Female	03/12/1985	Viet Nam	01/06/2017	30/09/2020		Previous Reporting Day	31/05/2021
4	2	Empl X2	Active	Male	27/02/1981	Nigeria	04/08/2010			Gender	male
5	3	Empl X3	Active	Male	13/06/1983	Armenia	04/04/2016
6	4	Empl X4	Inactive	Male	31/05/1983	Chile	12/06/2017	14/08/2020		Headcount - Today	8
7	5	Empl X5	Active	Female	11/04/1988	Armenia	01/08/2016	01/06/2019		Headcount - P.R.D
8	6	Empl X6	Active	Male	24/12/1982	Nigeria	06/08/2020
9	7	Empl X7	Active	Male	30/06/1981	Nigeria	03/01/2011	06/07/2019
10	8	Empl X8	Active	Male	08/12/1980	Kenya	01/06/2020
11	9	Empl X9	Active	Male	28/01/1994	Armenia	22/04/2021
12	10	Empl X10	Active	Male	26/09/1989	Armenia	01/02/2021
13	11	Empl X11	Inactive	Female	14/04/1982	Armenia	15/07/2019	16/06/2020
14	12	Empl X12	Inactive	Male	15/04/1982	Kenya	14/05/2016	26/08/2020
15	13	Empl X13	Inactive	Male	16/04/1982	Kenya	31/01/2011
16	14	Empl X14	Active	Male	17/04/1982	Armenia	03/06/2016
17	15	Empl X15	Active	Female	18/04/1982	Armenia	07/08/2020	04/06/2021
18	16	Empl X16	Active	Female	19/04/1982	Chile	04/08/2020
19	17	Empl X17	Inactive	Male	20/04/1982	Chile	06/11/2021
20	18	Empl X18	Inactive	Female	21/04/1982	Armenia	07/09/2021
21	19	Empl X19	Active	Female	22/04/1982	Nigeria	05/06/2002	07/08/2018
22	20	Empl X20	Inactive	Female	23/04/1982	Nigeria	05/07/2002
Lists

Cell Formulas
Range	Formula
K6	K6	=SUMPRODUCT((G3:G22<=K2)*((H3:H22>=K2)+(H3:H22=""))*(IF(K4="",TRUE,D3:D22=K4)))

 

[maxim642]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	Employee #	First name Last name	Status	Gender	Birth Date	Country	Hire Date	Termination Date		Today's Date	09/06/2021
3	1	Empl X1	Inactive	Female	03/12/1985	Viet Nam	01/06/2017	30/09/2020		Previous Reporting Day	31/05/2021
4	2	Empl X2	Active	Male	27/02/1981	Nigeria	04/08/2010			Gender	male
5	3	Empl X3	Active	Male	13/06/1983	Armenia	04/04/2016
6	4	Empl X4	Inactive	Male	31/05/1983	Chile	12/06/2017	14/08/2020		Headcount - Today	8
7	5	Empl X5	Active	Female	11/04/1988	Armenia	01/08/2016	01/06/2019		Headcount - P.R.D
8	6	Empl X6	Active	Male	24/12/1982	Nigeria	06/08/2020
9	7	Empl X7	Active	Male	30/06/1981	Nigeria	03/01/2011	06/07/2019
10	8	Empl X8	Active	Male	08/12/1980	Kenya	01/06/2020
11	9	Empl X9	Active	Male	28/01/1994	Armenia	22/04/2021
12	10	Empl X10	Active	Male	26/09/1989	Armenia	01/02/2021
13	11	Empl X11	Inactive	Female	14/04/1982	Armenia	15/07/2019	16/06/2020
14	12	Empl X12	Inactive	Male	15/04/1982	Kenya	14/05/2016	26/08/2020
15	13	Empl X13	Inactive	Male	16/04/1982	Kenya	31/01/2011
16	14	Empl X14	Active	Male	17/04/1982	Armenia	03/06/2016
17	15	Empl X15	Active	Female	18/04/1982	Armenia	07/08/2020	04/06/2021
18	16	Empl X16	Active	Female	19/04/1982	Chile	04/08/2020
19	17	Empl X17	Inactive	Male	20/04/1982	Chile	06/11/2021
20	18	Empl X18	Inactive	Female	21/04/1982	Armenia	07/09/2021
21	19	Empl X19	Active	Female	22/04/1982	Nigeria	05/06/2002	07/08/2018
22	20	Empl X20	Inactive	Female	23/04/1982	Nigeria	05/07/2002
Lists

Cell Formulas
Range	Formula
K6	K6	=SUMPRODUCT((G3:G22<=K2)*((H3:H22>=K2)+(H3:H22=""))*(IF(K4="",TRUE,D3:D22=K4)))

Thank you very much for your help!

 

[Fluff]

You're welcome & thanks for the feedback.