SUMPRODUCT formula needed to count headcount based on date input in cell A1, split by department.

[maxim642]

I want to summarise headcount volumes per department based on the date input in cell A1.

Headcount calculation is based on a combination of the "Hire Date" & the "Current Employment: End Date". An employee should only be counted if their hire date is equal to OR less than the date in cell A1 AND their end date is either blank OR equal to OR greater than the date in A1.

I want to be able to break the headcounts by the department.

Below is an example of what my dataset looks like.

I would like the formula to go into cells H2:H4

I tried using SUMPRODUCT but I couldn't get it to work. Perhaps, there's a simpler way? Thanks in advance!!

Master_Template_Exinity.xlsx
	A	B	C	D	E	F	G	H
1	9/30/21		Hire Date	Current Employment: End Date	Current Employment Department			Headcount per Date (A1)
2			1/22/19		Dep1		Dep1
3			7/4/15	5/12/18	Dep1		Dep2
4			9/4/16		Dep1		Dep3
5			2/1/20		Dep1
6			11/23/20		Dep1
7			5/25/18	5/30/21	Dep1
8			2/11/21		Dep1
9			9/9/20		Dep2
10			9/17/18	12/3/19	Dep2
11			12/31/18		Dep2
12			8/27/21		Dep2
13			3/12/19	10/8/19	Dep2
14			12/10/18		Dep2
15			6/3/16	7/25/17	Dep2
16			2/6/21		Dep3
17			9/26/19		Dep3
18			6/5/15	8/8/18	Dep3
19			7/31/15	12/16/15	Dep3
20			8/26/21		Dep3
21			1/20/15	11/8/20	Dep3
22			1/1/20	6/12/21	Dep3
23			4/27/16		Dep3
24			10/25/18		Dep3
25			7/19/16	5/13/17	Dep2
26			8/11/20		Dep2
27			3/27/20		Dep2
28			2/15/21		Dep2
29			12/23/16	2/8/17	Dep2
30			5/16/16		Dep2
31			5/30/19		Dep1
32			10/9/20		Dep1
33			8/11/15	5/7/18	Dep1
34			10/28/15	3/14/19	Dep1
35			8/20/19		Dep1
36			3/6/21		Dep1
37			6/23/16		Dep1
38			7/5/21		Dep1
39			7/19/21		Dep1
40			12/19/15	7/21/17	Dep3
41			5/4/17	12/12/17	Dep3
42			6/30/19		Dep3
43			1/2/17		Dep3
44			7/25/19		Dep3
45			1/27/19		Dep3
46			6/17/20	6/21/21	Dep3
47			9/28/19		Dep3
48			4/11/18	9/30/18	Dep3
Sheet1

 

[Fluff]

How about

=SUMPRODUCT(($C$2:$C$50<=$A$1)*(($D$2:$D$50="")+($D$2:$D$50>=$A$1))*($E$2:$E$50=G2))

 

[Dave Patton]

try

T202110a.xlsm
	A	B	C	D	E	F	G	H
1	30-Sep-21		Hire Date	Employ End Date	Dept			Headcount per Date (A1)
2			22-Jan-19		Dep1		Dep1	12
3			4-Jul-15	12-May-18	Dep1		Dep2	8
4			4-Sep-16		Dep1		Dep3	10
1c

Cell Formulas
Range	Formula
H2:H4	H2	=SUMPRODUCT(--($E$2:$E$48=G2),--($C$2:$C$48<=$A$1),--(($D$2:$D$48="")+($D$2:$D$48>$A$1)))

 

[maxim642]

How about

=SUMPRODUCT(($C$2:$C$50<=$A$1)*(($D$2:$D$50="")+($D$2:$D$50>=$A$1))*($E$2:$E$50=G2))

Thanks. So, this worked in the sample dataset worksheet I used but when I used it in the "real" worksheet, it returned 0 values. I'm wondering if it has anything to do with the fact the data source is formatted as a table?

 

[Fluff]

Being in a table will not make any difference. Check that all your dates are real dates & not text.

 

[maxim642]

Being in a table will not make any difference. Check that all your dates are real dates & not text.

I thought that might have been the issue but it doesn't appear to be.

Do you know whether a COUNTIFS could work?

 

[Fluff]

If your change the cell format for all the dates to General, what do you see?

 

[maxim642]

If your change the cell format for all the dates to General, what do you see?

This is driving me demented!!

So, this is currently the formula that I'm using

=SUMPRODUCT(('ORC Template'!SH:SH<=Calcs!$B$2)(('ORC Template'!$I: $I=''') +('ORC Template'!$I:$I>=Calcs!$B$2)) ('ORC Template '!SM:SM=Calcs!B10))

The ORC Template is worksheet 1 (main data source) and I'm pulling that information in the Calcs tab (worksheet 2).

Have I messed up the syntax as they are on separate worksheets?

 

[Fluff]

You are missing a couple of * in there, also you should not use the sheet name of the sheet the formula is in and using whole column references is a bad idea as it can slow your workbook down considerably.

=SUMPRODUCT(('ORC Template'!SH:SH<=$B$2)*(('ORC Template'!$I:$I="")+('ORC Template'!$I:$I>=$B$2))*('ORC Template'!SM:SM=B10))

 

[maxim642]