google sheets
I have a column (A) that contains a series of dates in the format mm/dd/yyyy. I want to run a count to return how many of them were in 2013.
Is this possible?
I tried using this formula, but it didn't work:
=COUNTIF (ARRAYFORMULA ( LEFT (C:C, 4)), "2013")
Use the following formula to accomplish that.
=QUERY(
'GR Live Campaign Tracker'!J:L, // data range
"SELECT J, COUNT(L) WHERE L IS NOT NULL GROUP BY J", // select
0 // headers
)
Added the formula into your example file.
You can reproduce the required table using a formula (this will only work on the newest version of Sheets):
=QUERY({A:A,A:A},"select Col1, count(Col2) where Col1 is not null group by Col1 label Col1 'Date', count(Col2) 'Number'",0)
And then reference that output for your graph.
If you are on the old version:
=ArrayFormula(QUERY(IF({1,1},A:A),"select Col1, count(Col2) where Col1 is not null group by Col1 label Col1 'Date', count(Col2) 'Number'",0))
Best Answer
In addition to the solution provided by OnenOnlyWalter, here's another formula:
The
YEARfunction simply returns the year in 4 digits. TheARRAYFORMULAwill just take on the complete column and theCOUNTIFwill finish it off by counting the occurrences.If you're using a header, then you can adjust the above formula like this: