Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

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?

share|improve this question
up vote 3 down vote accepted







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

share|improve this answer
Thank you ! i eventualy found this solution : count(filter(A:A,YEAR(A:A)=2012)) which works prety well, i've even be able to build on top of it to make some sum on other cell. I realy need to dig into this ARRAYFORMULA thing. – d.zOid Apr 16 '13 at 8:36

This should work:

SELECT count(A) AS row_count,
    WHEN A LIKE '%/2009' THEN 2009
    WHEN A LIKE '%/2010' THEN 2010
END as year
FROM your_table
    WHEN A LIKE '%/2009' THEN 2009
    WHEN A LIKE '%/2010' THEN 2010
share|improve this answer
This is an SQL statement. That won't work in a spreadsheet. – Jacob Jan Tuinstra Apr 15 '13 at 20:38
this will not work in google spreadsheet but thank you anyway – d.zOid Apr 16 '13 at 8:38

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.