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

 

[maxim642]

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))

Thanks, though I'm still not returning any values. I've uploaded screenshots of the 2 worksheets I'm using. I'm sure it must be something to do with the dates but I can't see the issue.

 

[Fluff]

Can you answer my question from post#7?

 

[maxim642]

Can you answer my question from post#7?

It didn't change anything: still returning "0".

 

[maxim642]

It's rather bizarre: the dates do appear to be recognised as dates: I did a quick formula in an adjacent column to count the number of days between the dates using NETWORKDAYS and it gave the expected results.

Are COUNTIFS not generally recommended for counting between dates?

 

[Dave Patton]

The syntax that follows also works.
Edit for location of criteria, the locations of columns of interest, and the appropriate range for the data.
The formula in Column D also works; it was edited.
N.B. You can post an extract of your data with the forum's tool XL2BB.

T202110a.xlsm
	A	B	C	D
1
2		30-Sep-21
3
4
5
6
7
8
9			Headcount per Date
10		Dep1	12	12
11		Dep2	8	8
12		Dep3	10	10
13
1d

Cell Formulas
Range	Formula
C10:C12	C10	=SUMPRODUCT(--('ORC Template'!$M$2:$M$500=B10),--('ORC Template'!$H$2:$H$500<=$B$2),--(('ORC Template'!$I$2:$I$500="")+('ORC Template'!$I$2:$I$500>$B$2)))
D10:D12	D10	=SUMPRODUCT(('ORC Template'!$H$2:$H$500<=$B$2)*(('ORC Template'!$I$2:$I$500="")+('ORC Template'!$I$2:$I$500>=$B$2))*('ORC Template'!$M$2:$M$500=B10))

 

[Fluff]

It didn't change anything: still returning "0".

But what do you see in the cells when you change the format of the dates to general?