count how many times a word occurs per year

[chrisbland1]

Hi. I have series of dates in column A and associated data in column B. I need to know how many times the word 'fail' appears per month. I have attached a simple demo file. It is the date aspect that has stumped me

Thank you in advance for any help.

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2
3
4
5
6				01/04/2021	01/05/2021
7	20/04/2021	pass		3	1
8	21/04/2021	fail
9	22/04/2021	pass
10	23/04/2021	pass
11	24/04/2021	pass
12	25/04/2021	fail
13	26/04/2021	pass
14	27/04/2021	pass
15	28/04/2021	pass
16	29/04/2021	pass
17	30/04/2021	fail
18	01/05/2021	pass
19	02/05/2021	pass
20	03/05/2021	pass
21	04/05/2021	fail
22	05/05/2021	pass
23	06/05/2021	pass
24	07/05/2021	pass
25	08/05/2021	pass
26
Data

Cell Formulas
Range	Formula
D7:E7	D7	=COUNTIFS($B:$B,"fail",$A:$A,">="&D6,$A:$A,"<="&EOMONTH(D6,0))

 

[chrisbland1]

Thank you very much!

 

[Peter_SSs]

Another possibility

21 04 10.xlsm
	A	B	C	D	E
1
2
3
4
5
6				1/04/2021	1/05/2021
7	20/04/2021	pass		3	1
8	21/04/2021	fail
9	22/04/2021	pass
10	23/04/2021	pass
11	24/04/2021	pass
12	25/04/2021	fail
13	26/04/2021	pass
14	27/04/2021	pass
15	28/04/2021	pass
16	29/04/2021	pass
17	30/04/2021	fail
18	1/05/2021	pass
19	2/05/2021	pass
20	3/05/2021	pass
21	4/05/2021	fail
22	5/05/2021	pass
23	6/05/2021	pass
24	7/05/2021	pass
25	8/05/2021	pass
26
Count

Cell Formulas
Range	Formula
D7:E7	D7	=SUM(($A$7:$A$25-DAY($A$7:$A$25)+1=D6)*($B$7:$B$25="Fail"))

 

[chrisbland1]

Another possibility

21 04 10.xlsm
	A	B	C	D	E
1
2
3
4
5
6				1/04/2021	1/05/2021
7	20/04/2021	pass		3	1
8	21/04/2021	fail
9	22/04/2021	pass
10	23/04/2021	pass
11	24/04/2021	pass
12	25/04/2021	fail
13	26/04/2021	pass
14	27/04/2021	pass
15	28/04/2021	pass
16	29/04/2021	pass
17	30/04/2021	fail
18	1/05/2021	pass
19	2/05/2021	pass
20	3/05/2021	pass
21	4/05/2021	fail
22	5/05/2021	pass
23	6/05/2021	pass
24	7/05/2021	pass
25	8/05/2021	pass
26
Count

Cell Formulas
Range	Formula
D7:E7	D7	=SUM(($A$7:$A$25-DAY($A$7:$A$25)+1=D6)*($B$7:$B$25="Fail"))

Thankyou so much! If I also have to show how many by the year, (as in 2021, 2022 etc) how would I adapt this?

 

[Fluff]

Two options

+Fluff 1.xlsm
	A	B	C	D
1
2
3
4
5			Year
6			01/01/2021	2021
7	20/04/2021	pass	4	4
8	21/04/2021	fail
9	22/04/2021	pass
10	23/04/2021	pass
11	24/04/2021	pass
12	25/04/2021	fail
13	26/04/2021	pass
14	27/04/2021	pass
15	28/04/2021	pass
16	29/04/2021	pass
17	30/04/2021	fail
18	01/05/2021	pass
19	02/05/2021	pass
20	03/05/2021	pass
21	04/05/2021	fail
22	05/05/2021	pass
23	06/05/2021	pass
24	07/05/2021	pass
25	08/05/2021	pass
Data

Cell Formulas
Range	Formula
C7	C7	=COUNTIFS($B:$B,"fail",$A:$A,">="&C6,$A:$A,"<="&EOMONTH(C6,12))
D7	D7	=SUM((YEAR(A7:A25)=D6)*(B7:B25="Fail"))

 

[Peter_SSs]

Yet another option (for both your questions) is to use a Pivot Table with the dates
.. grouped by month as shown here

.. or grouped by year