Count cells that are contain multiple criteria (dates)

[maxim642]

I would like to count the number of contracts being worked on at specific periods in time (based on the date input into G1, with these numbers split into Fixed and Perm, cells G2 & G3 respectively.

To count the number of contracts, the calculation should be summing the number of records that are EQUAL TO OR BELOW the date in G1 AND the contract finish date is EQUAL TO OR GREATER than the date in G1 OR the contract finish date is blank (so the contract is still ongoing, so they should be counted).

HR Dashboard - Anonymous v1.xlsx
	A	B	C	D	E	F	G
1	ID	Contract Start	Contract Finish	Type		Enter Date
2	48	10/20/19	12/19/19	Fixed		Total Fixed
3	10	4/25/21		Fixed		Total Perm
4	88	1/24/18	3/25/18	Fixed
5	48	7/2/21	8/31/21	Fixed
6	63	11/6/16	1/5/17	Fixed
7	71	2/16/19		Fixed
8	97	10/8/19	12/7/19	Fixed
9	29	1/31/16	3/31/16	Fixed
10	81	8/18/18		Fixed
11	95	12/30/20	2/28/21	Fixed
12	76	1/22/17	3/23/17	Fixed
13	13	5/21/16	7/20/16	Fixed
14	52	2/9/20		Fixed
15	8	1/28/18	3/29/18	Perm
16	12	5/15/21		Perm
17	20	4/6/16	6/5/16	Perm
18	43	9/23/18	11/22/18	Perm
19	71	5/4/18	7/3/18	Perm
20	75	1/3/16	3/3/16	Perm
21	89	5/19/20		Perm
22	83	6/7/18	8/6/18	Perm
23	49	2/16/21		Perm
24	27	5/9/19	7/8/19	Perm
25	34	4/29/17	6/28/17	Perm
Sheet3

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	ID	Contract Start	Contract Finish	Type		Enter Date	01/01/2021
2	48	20/10/2019	19/12/2019	Fixed	Total	Fixed	4
3	10	25/04/2021		Fixed	Total	Perm	1
4	88	24/01/2018	25/03/2018	Fixed
5	48	02/07/2021	31/08/2021	Fixed
6	63	06/11/2016	05/01/2017	Fixed
7	71	16/02/2019		Fixed
8	97	08/10/2019	07/12/2019	Fixed
9	29	31/01/2016	31/03/2016	Fixed
10	81	18/08/2018		Fixed
11	95	30/12/2020	28/02/2021	Fixed
12	76	22/01/2017	23/03/2017	Fixed
13	13	21/05/2016	20/07/2016	Fixed
14	52	09/02/2020		Fixed
15	8	28/01/2018	29/03/2018	Perm
16	12	15/05/2021		Perm
17	20	06/04/2016	05/06/2016	Perm
18	43	23/09/2018	22/11/2018	Perm
19	71	04/05/2018	03/07/2018	Perm
20	75	03/01/2016	03/03/2016	Perm
21	89	19/05/2020		Perm
22	83	07/06/2018	06/08/2018	Perm
23	49	16/02/2021		Perm
24	27	09/05/2019	08/07/2019	Perm
25	34	29/04/2017	28/06/2017	Perm
26
Lists

Cell Formulas
Range	Formula
G2:G3	G2	=SUMPRODUCT(($D$2:$D$25=F2)*($B$2:$B$25<=$G$1)*(($C$2:$C$25>=$G$1)+($C$2:$C$25="")))

 

[maxim642]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	ID	Contract Start	Contract Finish	Type		Enter Date	01/01/2021
2	48	20/10/2019	19/12/2019	Fixed	Total	Fixed	4
3	10	25/04/2021		Fixed	Total	Perm	1
4	88	24/01/2018	25/03/2018	Fixed
5	48	02/07/2021	31/08/2021	Fixed
6	63	06/11/2016	05/01/2017	Fixed
7	71	16/02/2019		Fixed
8	97	08/10/2019	07/12/2019	Fixed
9	29	31/01/2016	31/03/2016	Fixed
10	81	18/08/2018		Fixed
11	95	30/12/2020	28/02/2021	Fixed
12	76	22/01/2017	23/03/2017	Fixed
13	13	21/05/2016	20/07/2016	Fixed
14	52	09/02/2020		Fixed
15	8	28/01/2018	29/03/2018	Perm
16	12	15/05/2021		Perm
17	20	06/04/2016	05/06/2016	Perm
18	43	23/09/2018	22/11/2018	Perm
19	71	04/05/2018	03/07/2018	Perm
20	75	03/01/2016	03/03/2016	Perm
21	89	19/05/2020		Perm
22	83	07/06/2018	06/08/2018	Perm
23	49	16/02/2021		Perm
24	27	09/05/2019	08/07/2019	Perm
25	34	29/04/2017	28/06/2017	Perm
26
Lists

Cell Formulas
Range	Formula
G2:G3	G2	=SUMPRODUCT(($D$2:$D$25=F2)*($B$2:$B$25<=$G$1)*(($C$2:$C$25>=$G$1)+($C$2:$C$25="")))

Thank you once again!!!

 

[Fluff]

You're welcome & thanks for the feedback.