Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
add comment

2 Answers

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
add comment

This should work:

SELECT count(A) AS row_count,
CASE 
    WHEN A LIKE '%/2009' THEN 2009
    WHEN A LIKE '%/2010' THEN 2010
END as year
FROM your_table
GROUP BY 
CASE 
    WHEN A LIKE '%/2009' THEN 2009
    WHEN A LIKE '%/2010' THEN 2010
END
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
add comment

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.