count of date outside range

[mikenz1983]

Hi

i have a range of dates for a survey that was completed with a due date of completion
i am wanting to identify how many sit outside the close date range

for example if the close date was 22/07/2021, how many in the range of cells are between 1-4 days late 5 and 9 days late and then how many are later than that

 

[AhoyNC]

Maybe something like this:

Book1
	A	B	C	D	E	F
1	Completed Date		Due	7/22/2021
2	7/2/2021			Days Late
3	7/22/2021			1-4 days	5-9 days	10 or more
4	7/24/2021			2	1	2
5	7/25/2021
6	8/15/2021
7	7/31/2021
8	8/18/2021
Sheet2

Cell Formulas
Range	Formula
D4	D4	=SUMPRODUCT(($A$2:$A$8-$D$1>0)*($A$2:$A$8-$D$1<=4))
E4	E4	=SUMPRODUCT(($A$2:$A$8-$D$1>=5)*($A$2:$A$8-$D$1<=9))
F4	F4	=SUMPRODUCT(--($A$2:$A$8-$D$1>9))

 

[mikenz1983]

thanks for that really helpful, i have adjusted to my range and it is miss counting by 2 and i think it has something to do with the 5-9 range
below is what i have

Total completes = 45
withing timeframe = =COUNTIFS(Changed!C2:C45,"<15/05/2021") ---------------- 32
1-4 days late = =SUMPRODUCT((Changed!C2:C45-$B$2>0)*(Changed!C2:C45-$B$2<=4)) ---------------- 5
5-9 days late = =SUMPRODUCT((Changed!C2:C45-$B$2>=5)*(Changed!C2:C45-$B$2<=9)) --------------- 0
10+ days late = =SUMPRODUCT(--(Changed!C2:C45-$B$2>9)) ----------------------------------------------- 6

when i add the above up i am short 2
the below is what i have as raw data, i am counting manually that i have 6 dates that are between 1-4 days late , 1 that should sit in the 5-9 period and 5 beyond that

below is a dump of that data, unsure if i am doing something wrong here

34​	1​	17/05/2021​
35​	1​	17/05/2021​
36​	1​	18/05/2021​
37​	1​	18/05/2021​
38​	1​	18/05/2021​
40​	1​	19/05/2021​
41​	1​	24/05/2021​
42​	1​	1/06/2021​
43​	1​	2/06/2021​
45​	1​	23/06/2021​
46​	1​	30/06/2021​
47​	1​	21/07/2021​

Maybe something like this:

Book1
	A	B	C	D	E	F
1	Completed Date		Due	7/22/2021
2	7/2/2021			Days Late
3	7/22/2021			1-4 days	5-9 days	10 or more
4	7/24/2021			2	1	2
5	7/25/2021
6	8/15/2021
7	7/31/2021
8	8/18/2021
Sheet2

Cell Formulas
Range	Formula
D4	D4	=SUMPRODUCT(($A$2:$A$8-$D$1>0)*($A$2:$A$8-$D$1<=4))
E4	E4	=SUMPRODUCT(($A$2:$A$8-$D$1>=5)*($A$2:$A$8-$D$1<=9))
F4	F4	=SUMPRODUCT(--($A$2:$A$8-$D$1>9))

[

Cell Formulas
Range	Formula

 

[AhoyNC]

I don't see anything wrong with your formula. I assume all the dates in your C2:C45 range are all true Excel dates and none are text.

As you can see below the formula seems to work.

Book1
	A	B	C	D	E	F
1				Days		Due
2	34	1	5/17/2021	2		5/15/2021
3	35	1	5/17/2021	2
4	36	1	5/18/2021	3		5 - 9 days
5	37	1	5/18/2021	3		3
6	38	1	5/22/2021	7
7	40	1	5/19/2021	4
8	41	1	5/24/2021	9
9	42	1	5/23/2021	8
10	43	1	6/2/2021	18
11	45	1	6/23/2021	39
12	46	1	6/30/2021	46
13	47	1	7/21/2021	67
Sheet1

Cell Formulas
Range	Formula
F5	F5	=SUMPRODUCT(($C$2:$C$13-$F$2>=5)*($C$2:$C$13-$F$2<=9))
D2:D13	D2	=C2-$F$2