Search code examples
dategoogle-sheetsgraphchartsgoogle-query-language

Create Google Sheets chart based on amount of times date appears in the cell

Based on one cell with dates (some of them repeats multiple times):

  • 06.02.2022
  • 06.02.2022
  • 08.02.2022
  • 10.02.2022
  • 17.02.2022

How to create the Google Sheets chart, where line goes up only when calendar day is exist in cell (and goes up multiply, based on x times when date repeats in cell)?

As a result:

enter image description here

Solution

  • see if this will do:

    =QUERY(A1:A; "select A,count(A) where A is not null group by A label count(A)''")

    enter image description here

    update:

    =SORT({QUERY(A1:A; "select A,count(A) where A is not null group by A label count(A)''"); 
 QUERY(SEQUENCE(MAX(A:A)-MIN(A:A)+1; 1; MIN(A:A))*{1\0}; 
 "where not Col1 matches '"&TEXTJOIN("|"; 1; N(A:A))&"'")}; 1; 1)

    enter image description here

    enter image description here