How can I make Google Sheets tell me how many of each date are in a column?

Question

I have a column in my spreadsheet that contains only Date values, like:

2014-05-17
2014-06-19
2014-07-16
2014-07-16
2014-07-21
2014-07-21
2014-07-21
2014-07-21
2014-07-21
2014-06-17
2014-06-24
2014-06-24
2014-07-18
2014-07-21
2014-07-21
2014-06-13
2014-06-17

How do I have Google Sheets give me, say, a line chart depicting how many of each date are in the column, like:

If the above dates were the only ones I was working with, this wouldn't be a problem. However, there are up to 20 new dates added every day, and none removed. I want this to happen as automatically as possible, without having to add a new cell or update a formula every day.

Answer 1

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))

Answer 2

Put a column labels at the top of the column

Put in a helper column next to it, and in every cell say

=if(ISBLANK(A1),0,1)

Then choose Data > Pivot Table Report, and enter the data range as A:B

This will create a new tab with the data series you want. You can then create a graph from this.

See an example of this approach here: https://docs.google.com/spreadsheets/d/1UIa4x1A2EWfTJUFUJbM3QkOzp-sHY12w9d7tprVPfDU/edit?usp=sharing

Answer 3

I don't see the need for a helper column:

The data range for the chart is considerably more rows than shown as in use - and the same goes for the pivot table.