Counting number of date occurrences in a rolling 3 month

[Wyrdstone]

Hello,

I am fairly new to Excel and hoping someone can give me some advice on how to get a formula to work. I have been scratching my head for hours!

I am thinking a COUNTIFS formula would be the most appropriate.

I have column A which contains a fault code (e.g. KPT1.1, KPT1.2, KPT1.3 etc), column B which contains the date the fault occurred, and column C which contains a numerical value.

I need to create column D which counts how many times the fault code occurred in the last rolling 3 months with a numerical value higher than 0.

I am mainly struggle how to work out how to count the date occurrences in the COUNTIFS formula. something along the lines of:

=COUNTIFS(B:B,"<="&B6, B:B,">="&EDATE(B6,-3)) this obviously returns the wrong number (i feel like I'm halfway there!). I feel if i can get my head round this bit i should be able to sort the other parts of the formula.

Here is hoping someone can help.

Thank you very much.

 

[etaf]

do you want the same day 3months ago
=COUNTIFS($B$2:B2,"<="&B2,$B$2:B2,">="&DATE(YEAR(B2),MONTH(B2)-3,DAY(B2)))

or just to the end or begining of the month

so if its the 2/month - only go back to 2/month - see the highlighted cell and different

then add the other criteria
not sure what

with a numerical value higher than 0.

means

Various.xlsx
	B	C	D
1
2	9/1/21		1
3	10/1/21		2
4	11/1/21		3
5	12/2/21		3
6	1/1/22		4
7	2/1/22		4
8	3/1/22		4
9	4/1/22		4
10	5/1/22		4
11	6/1/22		4
12	7/1/22		4
13	8/1/22		4
14	9/1/22		4
15	10/1/22		4
16	11/1/22		4
17	12/1/22		4
18	1/1/23		4
19	2/1/23		4
20	3/1/23		4
Sheet5

Cell Formulas
Range	Formula
D2:D20	D2	=COUNTIFS($B$2:B2,"<="&B2,$B$2:B2,">="&DATE(YEAR(B2),MONTH(B2)-3,DAY(B2)))

 

[etaf]

Various.xlsx
	A	B	C	D
1
2	kpt1.1	9/1/21		0
3	kpt1.2	10/1/21		0
4	kpt1.3	11/1/21		0
5	kpt1.1	12/2/21	1	1
6	kpt1.2	1/1/22		0
7	kpt1.3	2/1/22		0
8	kpt1.1	3/1/22		1
9	kpt1.2	4/1/22		0
10	kpt1.3	5/1/22		0
11	kpt1.1	6/1/22		0
12	kpt1.2	7/1/22		0
13	kpt1.3	8/1/22	3	1
14	kpt1.1	9/1/22		0
15	kpt1.2	10/1/22		0
16	kpt1.3	11/1/22		1
17	kpt1.1	12/1/22		0
18	kpt1.2	1/1/23		0
19	kpt1.3	2/1/23		0
20	kpt1.1	3/1/23		0
Sheet5

Cell Formulas
Range	Formula
D2:D20	D2	=COUNTIFS($B$2:$B$20,"<="&B2,$B$2:$B$20,">="&DATE(YEAR(B2),MONTH(B2)-3,DAY(B2)),$A$2:$A$20,A2,$C$2:$C$20,">"&0)

 

[Wyrdstone]

Thank you so much! it worked like a dream

I can follow your formula and see where i went horribly wrong! Just to work the rest out now

Thanks again, you have saved my sanity.

 

[etaf]

You are welcome