In Google Sheets, the Query is the suitable function to summarise data. No matter whether the data type is text, number or date. To create a daily/weekly/monthly/quarterly/yearly report summary, in Google Sheets, no doubt Query is the best.

A daily transaction is a part and parcel of every business, especially sales. Whatever your business you may have some data to record on daily basis. It can be sales, purchase or cash transactions.

Even a blogger does have his daily dose of data in the form site traffic, page impressions, CTR etc.

If this is the case, then you might want to make a summary of your daily transactions. Here are a few examples of how to create a daily/weekly/monthly/quarterly/yearly report summary in Google Sheets using Query.

You can use my formulas out of the box by adjusting your sales/purchase report columns as per my report format. I have included my sheet containing the formulas and demo data at the end of this tutorial.

Query to Create Daily/Weekly/Monthly/Quarterly/Yearly Report Summary

In Query, you can use the following scalar functions to create a summary report based on a date column. Date column here means a column that contains the transaction dates.

month()

quarter()

year()

Does these three scalar functions are enough?

I know what we want is a Query formula to create daily/weekly/monthly/quarterly/yearly report summary.

There are five types of date column summary. But for the daily report, the Query group clause is enough. For the weekly summary, we should use one date function that’s not in Query. You can see that in the formula examples below.

Query to Create Daily Report in Google Sheets

This is a mocked sales data. From this, I am going to create a daily summary of sales report.

The sales summary is just a basic one involving only two columns. That is column A and column F.

Note: Refer to my Google Sheets Functions Guide to learn Query as well as other popular functions.

Formula: Daily Sales Summary Involving Date and Total Qty. Columns

=QUERY(A1:F8,"Select A, sum(F) where A is not null group by A")

This is the Query in Cell I1. Some of you may wish to include the product name with the summary. Then you can use this Query formula.

Formula: Daily Sales Summary Involving Date, Product, and Total Qty. Columns

=QUERY(A1:F8,"Select A,C, sum(F) where A is not null group by A,C")

You will get a date wise and product wise daily summary report.

Query to Create Monthly Reports in Google Sheets

In the formulas to create daily/weekly/monthly/quarterly/yearly reports, except for the weekly summary report, all the formulas are almost identical. So I will detail the Query formula to create the Weekly report at the last part.

Formula: Monthly Sales Summary Involving Date and Qty. Columns

Here the difference lies in the use of the scalar functionmonth(). I’ve just underlined this addition in the above screenshot.

=QUERY(A1:F8,"Select month(A)+1, sum(F) where A is not null group by month(A)+1")

Tip: The Query scalar functionmonth() returns 0 for January and 11 for December. So you must add +1 to this function to get 1 for January, 2 for February and so on.

Formula: Monthly Sales Summary Involving Date, Product, and Qty. Columns

Needless to say, here also I have used themonth() scalar function.

=QUERY(A1:F8,"Select month(A)+1,C, sum(F) where A is not null group by month(A)+1,C")

Query to Create Yearly Reports in Google Sheets

As I have told you about the similarity of the Query to create daily/weekly/monthly/quarterly/yearly reports in Google Sheets. Here the scalar functionyear() makes the difference.

Formula: Yearly Sales Summary Involving Date and Qty. Columns

=QUERY(A1:F8,"Select year(A), sum(F) where A is not null group by year(A)")

Formula: Yearly Sales Summary Involving Date, Product, and Qty. Columns

=QUERY(A1:F8,"Select year(A),C, sum(F) where A is not null group by year(A),C")

Query to Create Quarterly Reports in Google Sheets

Replace theyear() scalar function in Query withquarter() to create a quarterly summary of the sales report.

Formula: Quarterly Sales Summary Involving Date and Qty. Columns

=QUERY(A1:F8,"Select quarter(A), sum(F) where A is not null group by quarter(A)")

Formula: Quarterly Sales Summary Involving Date, Product, and Qty. Columns

=QUERY(A1:F8,"Select year(A),C, sum(F) where A is not null group by year(A),C")

Query to Create Weekly Reports in Google Sheets

There is no scalar function calledweeknum() to use. That makes the following two formulas little messy. We must use here one Google Sheets date function called WEEKNUM with Query.

This function returns unique week numbers for each week. For example, the week number of 17/10/2018 is 42 and 23/10/2018 is 43.

You can test that as follows=weeknum("14/10/2018"). See how I am using the WEEKNUM function in Query.

Formula: Weekly Sales Summary Involving Date and Qty. Columns

=query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),query(B2:G)},"Select Col1,Sum(Col6) where Col1>0 group by Col1")

Formula: Weekly Sales Summary Involving Date, Product, and Qty. Columns

=query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),query(B2:G)},"Select Col1,Col3, Sum(Col6) where Col1>0 group by Col1,Col3")

I know you need a detailed tutorial for the weekly summary report. You can follow this Google Sheets tutorial – How to Create A Weekly Summary Report in Google Sheets.

Sheet Template

Additional Tips/Resources to Create Daily/Weekly/Monthly/Quarterly/Yearly Report Summary in Google Sheets.

1. Filter by Month and Year in Query in Google Sheets.

2. Month, Quarter, Year Wise Grouping in Pivot Table in Google Sheets.

3. How to Group Data by Month and Year in Google Sheets.

4. Google Sheets Query: How to Convert Month in Number to Month Name in Text.