How to get the count of inputs (complaints) received per day on google sheets?

Question

Repeating dates

I receive n number of complaints via Typeform and have linked the typeform with google sheets.

For now, I have the data of all those complaints received from February 16 to March 16. The number of complaints i received per day vary, like 5 complaints of Feb 16th, 2 on 17th , 7-8 on 18th, you get the gist.

I would like to make a report using Bar graph which displays a simple formation of number of complaints received per day (ex. 5,10,15) on the vertical axis by dates (eg. Feb 14, Feb 15, Feb 16) in the horizontal axis.

That is all!

EDIT: I now have the dates being repeated in the graph , Can someone help me with the dates not being repeated ? thanks

Answer 1

Use the QUERY() function to get a distinct list of dates and the COUNT aggregation function to find the count of complaints.

More info in the official Docs and (in my opinion better) more info from Ben Collins here

Answer 2

What about trying the below:

Create another table with dates and sum where days will contain the unique values for each date using:

=UNIQUE(A2:A19)

Where A2:A19 is the range where your duplicate dates are.

As for the sum of values, you can use:

=ARRAYFORMULA(SUMIF(A2:A19,unique(A2:A19),B2:B19))

Where 'A2:A19' is the range for dates and 'B2:B19' is the range for values.

Then you proceed to create your chart.

Something to consider:

Your dates include time stamps, which will make the dates different and therefore be repeated even after using unique(), so if time is important keep this in mind or remove it.