How to count the number of years in a set of data

[Geekersw]

Hello all,

I have a set of payment dates (month and year), and want to be able to count how many payment were made during a specific year. I tried Countif function but cannot make it to work.

Which formula can I best use to count the number of payments in a certain year, for example 2026 (months in a year are not relevant)?
Thank you very much

 

[Legacy 143009]

Try countif like this:

=COUNTIF(YEAR(A:A), 2016)

 

[Geekersw]

Hi, this formula gives an error

 

[Legacy 143009]

OK, I have tested this one:

=SUMPRODUCT(1*(YEAR(A:A)=2026))

 

[Geekersw]

Great, this works! What if I want to be more granular, for example only aim for Q1 2026 payments, how can I insert that in this formula?

Many thanks!

 

[Legacy 143009]

This will check for Q4 2016

=SUMPRODUCT((YEAR(A:A)=2026)*(ROUNDUP(MONTH(A:A)/3,0)=4))

Since SUMPRODUCT is an array function in its nature, I would not recommend using A:A reference completely. It will be a better practice to limit it like A1:A10000

 

[Geekersw]

Wow that looks complex. Would you mind briefly explaining how this formula would check for A4 2026, so that I can reproduce it for other quarters? Thanks a lot!

 

[Legacy 143009]

I didn't get your question sorry.
(YEAR(A:A)=2026) This part checks if the range equals to year
(ROUNDUP(MONTH(A:A)/3,0)=4) This part checks if the date belongs to 4th quarter.

Then it multiplies TRUE results. Multiplication converts the TRUE values into 1 and False values into 0. Then SUMPRODUCT function sums them.

 

[bebo021999]

Why not compare with specific date:

Book2
	A	B	C	D	E	F
1	01-Jan-2023			2023	7	=SUMPRODUCT(--(YEAR($A$1:$A$8)=D1))
2	02-Oct-2023			Q1-2023	4	=COUNTIFS($A$1:$A$8,">="&DATE(2023,1,1),$A$1:$A$8,"<"&DATE(2023,4,1))
3	06-Dec-2024			Q4-2023	1	=COUNTIFS($A$1:$A$8,">="&DATE(2023,10,1),$A$1:$A$8,"<"&DATE(2024,1,1))
4	07-Mar-2023
5	05-Feb-2023
6	05-Apr-2023
7	05-Jun-2023
8	05-Feb-2023
9
Sheet1

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(YEAR($A$1:$A$8)=D1))
E2	E2	=COUNTIFS($A$1:$A$8,">="&DATE(2023,1,1),$A$1:$A$8,"<"&DATE(2023,4,1))
E3	E3	=COUNTIFS($A$1:$A$8,">="&DATE(2023,10,1),$A$1:$A$8,"<"&DATE(2024,1,1))

 

[Legacy 143009]

"DOĞRU" means TRUE in Turkish.

This will result 2