Countifs or sumproduct - date range and multiple criteria

[Sirod]

Hello,

I have a list of employees who were assigned tasks for different projects. The list includes details such as task status, task start date, task end date, and what project the task is associated with. Please see the below table for a snapshot.

I now need to count the number of people who worked on a task in a given year, counted by date range, project, and status. The employee may have started the task in one year and not completed it until a later year. For example, if they started the task in 2018 and completed it in 2020, they would be counted in each year for 2018 (start), 2019, and 2020 (completed) - so three times. I’m not sure if I need a countifs, sumproduct, or a combination of both. Please see the below table. I did do a manual count to fill in a few of the easier cells to give an idea of what I need – hope it helps!

Many thanks.

 

[RasGhul]

Maybe try this sumproduct, although it is producing different results from your manual count;

Countifs or sumproduct - date range and multiple criteria - Sirod.xlsx
	A	B	C	D	E
1	name	project	task status	start	end
2	Employee 1	Project A	Completed	2017-05-21	2021-07-09
3	Employee 2	Project A	Completed	2017-05-13	2019-01-25
4	Employee 3	Project A	Completed	2017-05-13	2018-01-31
5	Employee 4	Project A	Completed	2018-07-15	2018-08-28
6	Employee 5	Project A	Completed	2018-07-08	2021-01-31
7	Employee 6	Project B	Incomplete	2020-11-01	2020-11-22
8	Employee 7	Project C	Completed	2019-01-03	2019-05-31
9	Employee 8	Project A	Completed	2019-07-14	2019-05-31
10	Employee 9	Project A	In Progress	2019-04-21
11	Employee 10	Project A	Completed	2020-09-18	2020-11-08
12	Employee 11	Project B	In Progress	2020-07-03
13	Employee 12	Project B	In Progress	2017-05-07
14	Employee 13	Project B	In Progress	2019-08-14
15	Employee 14	Project C	In Progress	2020-01-14
16	Employee 15	Project A	Completed	2019-01-13	2020-01-20
Sheet1

Countifs or sumproduct - date range and multiple criteria - Sirod.xlsx
	A	B	C	D	E
1	Year	Project	Completed	In Progress	Incomplete
2	2017-01-01	Project A		1
3	2017-01-01	Project B		3
4	2017-01-01	Project C		1
5	2018-01-01	Project A	1	1
6	2018-01-01	Project B		2
7	2018-01-01	Project C		1
8	2019-01-01	Project A	1	1
9	2019-01-01	Project B		2
10	2019-01-01	Project C	1	1
11	2020-01-01	Project A	1
12	2020-01-01	Project B		1	1
13	2020-01-01	Project C		1
14	2021-01-01	Project A
15	2021-01-01	Project B
16	2021-01-01	Project C
Sheet2

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT((Sheet1!$D$2:$D$16>=$A2)*(Sheet1!$E$2:$E$16<=EOMONTH($A2,11))*(Sheet1!$B$2:$B$16=$B2)*(Sheet1!$C$2:$C$16=C$1))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Since we cannot copy data from an image like you posted, you should also investigate this:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

There is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Regarding my first paragraph above, IF you have Microsoft 365, you could try this.

Sirod.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	name	project	task status	start	end		Year	Project	Completed	In Progress	Incomplete
2	Employee 1	Project A	Completed	21/05/2017	9/07/2021		2018	Project A	5	0	0
3	Employee 2	Project A	Completed	13/05/2017	25/01/2019		2018	Project B	0	1	0
4	Employee 3	Project A	Completed	13/05/2017	31/01/2018		2018	Project C	0	0	0
5	Employee 4	Project A	Completed	15/07/2018	28/08/2018		2019	Project A	5	1	0
6	Employee 5	Project A	Completed	8/07/2018	31/01/2021		2019	Project B	0	2	0
7	Employee 6	Project B	Incomplete	1/11/2020	22/11/2020		2019	Project C	1	0	0
8	Employee 7	Project C	Completed	3/01/2019	31/05/2019		2020	Project A	4	1	0
9	Employee 8	Project A	Completed	14/07/2019	31/05/2019		2020	Project B	0	3	1
10	Employee 9	Project A	In Progress	21/04/2019			2020	Project C	0	1	0
11	Employee 10	Project A	Completed	18/09/2020	8/11/2020
12	Employee 11	Project B	In Progress	3/07/2020
13	Employee 12	Project B	In Progress	7/05/2017
14	Employee 13	Project B	In Progress	14/08/2019
15	Employee 14	Project C	In Progress	14/01/2020
16	Employee 15	Project A	Completed	13/01/2019	20/01/2020
17
Sheet1