year by year headcount snapshot with only start and end date

[ulooz]

client would like "year by year snapshot" of headcount within company, assume that even if the start and end date was 1/2/2019 - 1/2/2020 that the person would count for 2019 and 2020. In the below example, i would need to summarize this contractor as counting for each 2017,2018,2019,2020,2021,2022 and 2023.

the data looks like this:

assignment # | name | start date | end date
1 | Darkwing Duck | 1/4/2017 | 3/4/2018
2 | Darkwing Duck | 4/20/2018 | 5/20/2019
3 | Darkwing Duck | 6/10/2019 | 7/8/2021
4 | Darkwing Duck | 8/4/20222 | 8/11/2023

 

[awoohaw]

This is what I've come up with:

Mr excel questions 41.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1							2017	2018	2019	2020	2021	2022	2023
2	Assignment #	Name	Start_Date	End_Date	Min Start	Max End	2017	2018	2019	2020	2021	2022	2023
3	1	AM	2020-01-01	2020-08-31	2020-01-01	2021-05-31	0	0	0	1	0	0	0
4	2	AM	2020-01-01	2020-12-31	2020-01-01	2021-05-31	0	0	0	1	0	0	0
5	3	AM	2020-09-01	2021-05-31	2020-01-01	2021-05-31	0	0	0	1	1	0	0
6	1	AS	2020-06-01	2020-07-31	2019-03-06	2023-09-30	0	0	0	1	0	0	0
7	2	AS	2019-03-06	2020-11-30	2019-03-06	2023-09-30	0	0	1	1	0	0	0
8	3	AS	2020-03-16	2022-03-15	2019-03-06	2023-09-30	0	0	0	1	1	1	0
9	1	AJ	2018-12-03	2019-02-28	2018-12-03	2019-02-28	0	1	1	0	0	0	0
10	1	AR	2019-08-19	2020-02-16	2019-08-19	2023-12-01	0	0	1	1	0	0	0
11	2	AR	2020-02-17	2020-05-29	2019-08-19	2023-12-01	0	0	0	1	0	0	0
12	3	AR	2021-07-29	2023-12-01	2019-08-19	2023-12-01	0	0	0	0	1	1	1
13	1	AS	2021-10-01	2023-09-30	2019-03-06	2023-09-30	0	0	0	0	1	1	1
14	1	AK	2017-08-08	2018-12-16	2017-08-08	2020-02-23	1	1	0	0	0	0	0
15	2	AK	2018-12-17	2019-06-23	2017-08-08	2020-02-23	0	1	1	0	0	0	0
16	3	AK	2019-06-24	2020-02-23	2017-08-08	2020-02-23	0	0	1	1	0	0	0
17
18	Yearly Total
19	2017	1
20	2018	3
21	2019	5
22	2020	9
23	2021	4
24	2022	3
25	2023	2
26
Sheet4

Cell Formulas
Range	Formula
E3:E16	E3	=MINIFS(C:C,B:B,B3)
F3:F16	F3	=MAXIFS(D:D,B:B,B3)
G3:M16	G3	=(--(AND(G$1>=YEAR($C3),G$1<=YEAR($D3))))
B19:B25	B19	=SUM(INDEX($G$3:$M$16,0,XMATCH(A19,$G$2:$M$2)))

 

[awoohaw]

try this to filter by name:

Mr excel questions 41.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1							2017	2018	2019	2020	2021	2022	2023
2	Assignment #	Name	Start_Date	End_Date	Min Start	Max End	2017	2018	2019	2020	2021	2022	2023
3	1	AM	2020-01-01	2020-08-31	2020-01-01	2021-05-31	0	0	0	1	1	0	0
4	2	AM	2020-01-01	2020-12-31	2020-01-01	2021-05-31	0	0	0	1	1	0	0
5	3	AM	2020-09-01	2021-05-31	2020-01-01	2021-05-31	0	0	0	1	1	0	0
6	1	AS	2020-06-01	2020-07-31	2019-03-06	2023-09-30	0	0	1	1	1	1	1
7	2	AS	2019-03-06	2020-11-30	2019-03-06	2023-09-30	0	0	1	1	1	1	1
8	3	AS	2020-03-16	2022-03-15	2019-03-06	2023-09-30	0	0	1	1	1	1	1
9	1	AJ	2018-12-03	2019-02-28	2018-12-03	2019-02-28	0	1	1	0	0	0	0
10	1	AR	2019-08-19	2020-02-16	2019-08-19	2023-12-01	0	0	1	1	1	1	1
11	2	AR	2020-02-17	2020-05-29	2019-08-19	2023-12-01	0	0	1	1	1	1	1
12	3	AR	2021-07-29	2023-12-01	2019-08-19	2023-12-01	0	0	1	1	1	1	1
13	1	AS	2021-10-01	2023-09-30	2019-03-06	2023-09-30	0	0	1	1	1	1	1
14	1	AK	2017-08-08	2018-12-16	2017-08-08	2020-02-23	1	1	1	1	0	0	0
15	2	AK	2018-12-17	2019-06-23	2017-08-08	2020-02-23	1	1	1	1	0	0	0
16	3	AK	2019-06-24	2020-02-23	2017-08-08	2020-02-23	1	1	1	1	0	0	0
17
18	Name	AM
19	Yearly Total
20	2017	0
21	2018	0
22	2019	0
23	2020	3
24	2021	3
25	2022	0
26	2023	0
27
Sheet4

Cell Formulas
Range	Formula
E3:E16	E3	=MINIFS($C$3:$C$16,$B$3:$B$16,B3)
F3:F16	F3	=MAXIFS($D$3:$D$16,$B$3:$B$16,B3)
G3:M16	G3	=(--(AND(G$1>=YEAR($E3),G$1<=YEAR($F3))))
B20:B26	B20	=SUM(CHOOSECOLS(FILTER($A$3:$M$16,$B$3:$B$16=$B$18),MATCH(A20,$A$2:$M$2,0)))