COUNTIFS and Dates

[robbarba]

Here is the problem: I have a required complete date of 5/31/2023 (LINE 15) but I was not able to complete that until 6/6/2023 I would like a count of how many projects missed the deadline.

2023 Onboards.xlsx
	A	B	C	D
1	Submit	Complete By	Act. Complete Date	DAYS OPEN
2	5/31/23	6/5/23		8
3	5/31/23	6/5/23		8
4	5/31/23	5/30/23		8
5	5/31/23	5/31/23		8
6	5/31/23	6/5/23		8
7	5/31/23	6/5/23	6/5/23
8	5/31/23	6/5/23		8
9	5/31/23	6/5/23		8
10	5/31/23	5/30/23	6/6/23
11	5/31/23	6/5/23		8
12	5/31/23	6/5/23		8
13	5/31/23	6/5/23		8
14	6/1/23	6/5/23		7
15	6/1/23	5/31/23	6/6/23
16	6/1/23	6/5/23		7
17	6/1/23	6/5/23		7
18	6/1/23	6/1/23		7
19	6/1/23	6/5/23		7
20	6/2/23	5/22/23		6
21	6/2/23	6/5/23		6
22	6/2/23	6/2/23		6
23	6/2/23	6/5/23		6
24	6/2/23	6/5/23	6/6/23
25	6/5/23	6/12/23		3
26	6/5/23	6/1/23		3
27	6/5/23	6/1/23		3
28	6/6/23	6/5/23		2
29	6/6/23	6/6/23		2
Sheet11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B29	Cell	contains a blank value	text	NO

I have tried using the following
=COUNTIFS(Projects!$A:$A,ProjectStart!$n:$n,">="&ProjectComplete!$M:$M,ProjectComplete!$M:$M,">="&$C$1,ProjectComplete!$M:$M,"<="&$E$1)

where column A is the name of the project
where column N is the project start date
where column M is the project complete date
where C1 is the start of report date
where E1 is the start of the report date

 

[kevin9999]

On the same sheet:

Book1
	A	B	C	D	E	F
1	Submit	Complete By	Act. Complete Date	DAYS OPEN
2	5/31/23	6/5/23		8	Missed:	3
3	5/31/23	6/5/23		8
4	5/31/23	5/30/23		8
5	5/31/23	5/31/23		8
6	5/31/23	6/5/23		8
7	5/31/23	6/5/23	6/5/23
8	5/31/23	6/5/23		8
9	5/31/23	6/5/23		8
10	5/31/23	5/30/23	6/6/23
11	5/31/23	6/5/23		8
12	5/31/23	6/5/23		8
13	5/31/23	6/5/23		8
14	6/1/23	6/5/23		7
15	6/1/23	5/31/23	6/6/23
16	6/1/23	6/5/23		7
17	6/1/23	6/5/23		7
18	6/1/23	6/1/23		7
19	6/1/23	6/5/23		7
20	6/2/23	5/22/23		6
21	6/2/23	6/5/23		6
22	6/2/23	6/2/23		6
23	6/2/23	6/5/23		6
24	6/2/23	6/5/23	6/6/23
25	6/5/23	6/12/23		3
26	6/5/23	6/1/23		3
27	6/5/23	6/1/23		3
28	6/6/23	6/5/23		2
29	6/6/23	6/6/23		2
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT((C2:C29<>"")*(C2:C29>B2:B29))

 

[robbarba]

great this give the formula for the entire year, I want to run it with a date range, and I added
=SUMPRODUCT((!c2:c29<>"")*(c2:c29>b2:b29)*$c:$c,">="&$g$1,$c:$c,"<="&$h$1)
and it didn't work suggestions ?

 

[kevin9999]

I can't see what you've got in G1 & H1, but here's a suggestion....

Book1
	A	B	C	D	E	F	G	H
1	Submit	Complete By	Act. Complete Date	DAYS OPEN			5/1/23	6/30/23
2	5/31/23	6/5/23		8	Missed:	3
3	5/31/23	6/5/23		8
4	5/31/23	5/30/23		8
5	5/31/23	5/31/23		8
6	5/31/23	6/5/23		8
7	5/31/23	6/5/23	6/5/23
8	5/31/23	6/5/23		8
9	5/31/23	6/5/23		8
10	5/31/23	5/30/23	6/6/23
11	5/31/23	6/5/23		8
12	5/31/23	6/5/23		8
13	5/31/23	6/5/23		8
14	6/1/23	6/5/23		7
15	6/1/23	5/31/23	6/6/23
16	6/1/23	6/5/23		7
17	6/1/23	6/5/23		7
18	6/1/23	6/1/23		7
19	6/1/23	6/5/23		7
20	6/2/23	5/22/23		6
21	6/2/23	6/5/23		6
22	6/2/23	6/2/23		6
23	6/2/23	6/5/23		6
24	6/2/23	6/5/23	6/6/23
25	6/5/23	6/12/23		3
26	6/5/23	6/1/23		3
27	6/5/23	6/1/23		3
28	6/6/23	6/5/23		2
29	6/6/23	6/6/23		2
30	6/16/23	6/16/23
31	6/26/23	6/26/23
32	7/6/23	7/6/23	7/7/23
33	7/16/23	7/16/23
34	7/26/23	7/26/23
35	8/5/23	8/5/23
36	8/15/23	8/15/23
37	8/25/23	8/25/23
Sheet2

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT((C2:C37<>"")*(C2:C37>B2:B37)*(C2:C37>=G1)*(C2:C37<=H1))
A30:B37	A30	=A29+10

 

[robbarba]

That is exactly right! The ask was for project task of expected end date and the actual end date How many were missed during the time frame asked for! Works beautifully, much appreciated the work!
I was so wrapped around the date, I didn't think of reversing it!

 

[kevin9999]

That is exactly right! The ask was for project task of expected end date and the actual end date How many were missed during the time frame asked for! Works beautifully, much appreciated the work!
I was so wrapped around the date, I didn't think of reversing it!

Happy to help, and thanks for the feedback