Google Sheets – How to Count Entries Based on Timestamp

google sheets

I'm trying to count how many entries with a particular timestamp or range are in my table and although it’s pretty simple, I cannot find the proper way to do that.

Example data: 

A
------------------
9/12/2011 11:08:40
4/23/2012 8:09:22
4/23/2012 8:11:55

What I want to calculate is how many entries were made in 2011 and how many in 2012.

I tried to make some string checks with countif, like countif(A:A, "*/2011*"), but it does’t work.

I also tried to use the YEAR() function this way: countif(A:A, year(A)="2012"), but it returns 0.

What am I doing wrong?

Best Answer

Try:

=COUNTIF(ARRAYFORMULA(YEAR(B:B));2011)

or

=COUNT(FILTER(B:B;YEAR(B:B)=2012))

or

=INDEX(QUERY(B:B;"SELECT COUNT(B) WHERE YEAR(B)=2012");2;1)

If you use the QUERY formula without the INDEX, then a header will appear as well.

Related Topic