Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

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:

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?

share|improve this question
up vote 3 down vote accepted

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.

share|improve this answer
1  
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

Your Answer

 
discard

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.