3

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?

3

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.

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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