Countif date contains year

[Fegal]

Hello,
I have a qty of data containing dates ranging from 2010 to 2023. eg, 1-1-2010 upto 1-12-2023
How can I count the number of entries in each year... ?

thanks

 

[Peter_SSs]

Is this the sort of thing you mean?

23 12 03.xlsm
	A	B	C	D
1	Date		Year	Count
2	29/01/2015		2010	3
3	22/01/2015		2011	2
4	18/06/2011		2012	0
5	20/09/2017		2013	2
6	15/06/2010		2014	1
7	22/12/2018		2015	3
8	7/05/2014		2016	2
9	20/01/2020		2017	1
10	9/09/2013		2018	3
11	6/12/2018		2019	1
12	10/03/2016		2020	1
13	19/09/2015		2021	0
14	14/03/2018		2022	1
15	26/05/2022		2023	0
16	22/07/2011
17	21/08/2019
18	7/01/2010
19	20/12/2010
20	10/08/2016
21	13/03/2013
Count Dates in Year

Cell Formulas
Range	Formula
D2:D15	D2	=SUMPRODUCT(--(YEAR(A$2:A$21)=C2))

Another alternative would be to use a pivot Table

23 12 03.xlsm
	A	B			E	F	G
1	Date					Row Labels	Count of Date
2	29/01/2015					2010	3
3	22/01/2015					2011	2
4	18/06/2011					2013	2
5	20/09/2017					2014	1
6	15/06/2010					2015	3
7	22/12/2018					2016	2
8	7/05/2014					2017	1
9	20/01/2020					2018	3
10	9/09/2013					2019	1
11	6/12/2018					2020	1
12	10/03/2016					2022	1
13	19/09/2015					Grand Total	20
14	14/03/2018
15	26/05/2022
16	22/07/2011
17	21/08/2019
18	7/01/2010
19	20/12/2010
20	10/08/2016
21	13/03/2013
22
Count Dates in Year

 

[Fegal]

Hey!
Sorry for the slow reply. Its a little more tricky for me.
I have other stuff in the range going on.

National Training record 1.xlsx
	J	K	L	M	N
24	14/09/2025		Achieved	Achieved	Achieved
25			1/03/2015	1/05/2015	1/07/2018
26	17/12/2024		Achieved	Achieved	Achieved
27			7/07/2012		10/09/2018
28	14/09/2025		Achieved	Achieved	Achieved
29			7/07/2012	19/9/2010	7/08/2018
National Training

Cell Formulas
Range	Formula
J24,J26,J28	J24	=IF($H25="","",H25+820)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I114:I116,J98:J112,J96,J82:J86,J142,J24:J36,J88:J92,J94,I118,J38:J80	Expression	=AND(I25<>"",I25-TODAY()>365)	text	NO
J102:J113,J82:J89,J142:J143,J92:J99,J24:J77	Expression	=J24-TODAY()<=0	text	NO
J102:J113,J82:J89,J142:J143,J92:J99,J24:J77	Expression	=AND(J24<>"",J24-TODAY()>0,J24-TODAY()<=90)	text	NO
J102:J113,J82:J89,J142:J143,J92:J99,J24:J77	Expression	=AND(J24<>"",J24-TODAY()>90,J24-TODAY()<=180)	text	NO
J102:J113,J82:J89,J142:J143,J92:J99,J24:J77	Expression	=AND(J24<>"",J24-TODAY()>90,J24-TODAY()<=180)	text	NO
J102:J113,J66:J77,J24:K25,J27:K27,J29:K29,J31:K31,J33:K33,J35:K35,J37:K37,J41:K41,J43:K43,J45:K45,J47:K47,J49:K49,J51:K51,J53:K53,J55:K55,J57:K57,J59:K59,J61:K61,J63:K63,J65:K65,I26:K26,I28:K28,I30:K30,I32:K32,I34:K34,I36:K36,I40:K40,I42:K42,I44:K44,I46:K46	Cell Value	=$D$5	text	NO

Cells with Data Validation
Cell	Allow	Criteria
L24:N24	List	=$D$1:$D$8
L28:N28	List	=$D$1:$D$8
L26:N26	List	=$D$1:$D$8

 

[Peter_SSs]

So, what are the expected results for that sample data and how would those results be laid out?
Also, please explain in words how you would get those results manually.

 

[Fegal]

I would like to have years listed in a column, 2010 onwards with a number next to it for however many times that year appears in the range.
So 2010 might appear 15 times.
Manually i would go through the cells G24:AT113 and count the number of times the year appears.
I can do this with the sumproduct, but because they range contains a mixture of text, dates and merged cells i get errors.

 

[Peter_SSs]

Like this then?

23 12 12.xlsm
	A	B	C				G	H	I	J	K	L	M	N
24	2010	1								14/09/2025		Achieved	Achieved	Achieved
25	2011	0										1/03/2015	1/05/2015	1/07/2018
26	2012	2								17/12/2024		Achieved	Achieved	Achieved
27	2013	0										7/07/2012		10/09/2018
28	2014	0								14/09/2025		Achieved	Achieved	Achieved
29	2015	2										7/07/2012	19/09/2010	7/08/2018
30	2016	0
31	2017	0
32	2018	3
33	2019	0
34	2020	0
35	2021	0
36	2022	0
37	2023	0
38	2024	1
39	2025	2
Count Years

Cell Formulas
Range	Formula
B24:B39	B24	=SUMPRODUCT(--(IFERROR(YEAR(G$24:AT$113),0)=A24))

 

[Fegal]

that works perfectly. thank you!

 

[Fegal]

Sorry to pest.
i changed it to ignore 2 columns which contain future dates which i dont want counted, =SUMPRODUCT(--(IFERROR(YEAR(H$24:I$113,l$24:AT113),0)=B148))

i broke it...

 

[Peter_SSs]

that works perfectly. thank you!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

i changed it to ignore 2 columns which contain future dates which i dont want counted, =SUMPRODUCT(--(IFERROR(YEAR(H$24:I$113,l$24:AT113),0)=B148))

i broke it...

=SUMPRODUCT(--(IFERROR(YEAR(H$24:I$113),0)=B148))+SUMPRODUCT(--(IFERROR(YEAR(L$24:AT$113),0)=B148))