Google-sheets – How to make Google Sheets tell me how many of each date are in a column

google sheetsgoogle-sheets-charts

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:

A chart representation of the above data

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.

Best Answer

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

Related Topic