In Google Sheets, the QUERY function is the perfect tool to summarize data, regardless of whether the data type is text, numbers, or dates. Without a doubt, QUERY is the best function to create daily, weekly, monthly, quarterly, and yearly summary reports in Google Sheets.

Daily transactions are a crucial part of many businesses, especially in sales. No matter what type of business you run, you likely have data to record daily—whether it’s sales, purchases, or cash transactions. Even bloggers have their daily data to track, such as site traffic, page impressions, CTR, and more.

In such cases, you might want to create a summary of your daily transactions, traffic, or any other activity you record. Below, I’ll show you how to create daily, weekly, monthly, quarterly, and yearly summary reports in Google Sheets using the QUERY function.

You can use my formulas directly by adjusting your sales/purchase report columns to match my format. I’ve included a sample sheet with formulas and demo data at the end of this tutorial.

Including all the daily, weekly, monthly, quarterly, and yearly summary reports in one tutorial serves a purpose. You can convert daily reports into other types (except weekly) by simply modifying the scalar function used. I hope you’ll appreciate how I’ve laid out the formulas below.

Query Scalar Functions to Create Daily, Weekly, Monthly, Quarterly, and Yearly Summary Reports

In the QUERY function, you can use the following scalar functions to create summary reports based on a date column (a column containing transaction dates):

• month(): Returns the month value from a date or datetime value. It’s zero-based, meaning month numbers range from 0 to 11, where 0 represents January and 11 represents December. To get standard month numbers (1–12), use month()+1.
• quarter(): Returns the quarter from a date or datetime (timestamp) value.
• year(): Returns the year value from a date or datetime (timestamp) value.

For weekly reports, there’s no specific scalar function in QUERY. Instead, we’ll use a native Google Sheets function to convert dates into weeks and use that column for summarization.

Let’s use these QUERY scalar functions and native Sheets functions to create daily, weekly, monthly, quarterly, and yearly summary reports in Google Sheets.

Sample Data

For all examples, we’ll use the following basic data to easily check the results:

The sample data is in columns A1:C , with sales dates, products, and sales quantities (in tonnage).

1. Query to Create Daily Summary Reports in Google Sheets

Suitable when you have multiple transactions in a day:

You might be surprised to learn that you can use the QUERY function on this sample data to create five different types of daily summary reports.

1. Grouped by Date:

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

2. Pivoted by Date:

=QUERY(A1:C,"Select sum(C) where A is not null pivot A")

3. Grouped by Date and Product:

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

4. Grouped by Date, Pivoted by Product:

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

5. Grouped by Product, Pivoted by Date:

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

Out of these five QUERY formulas, choose the one that suits your needs for creating a daily summary report. Once you understand these formulas, converting them to create monthly, quarterly, and yearly summary reports becomes straightforward.

Tip: In the above formulas, I used the SUM() aggregation function. You can replace it with AVG(), MIN(), MAX(), or COUNT() depending on your reporting needs.

2. Query to Create Monthly Summary Reports in Google Sheets

Suitable when you have data spanning multiple months in a year and multiple transactions per month:

To transform the daily summary report into a monthly summary report, replace A with month(A)+1 in the SELECT, GROUP BY, and PIVOT clauses. Here are the updated formulas:

1. =QUERY(A1:C,"Select month(A)+1, sum(C) where A is not null group by month(A)+1")
2. =QUERY(A1:C, "Select sum(C) where A is not null pivot month(A)+1")
3. =QUERY(A1:C, "Select month(A)+1, B, sum(C) where A is not null group by month(A)+1, B")
4. =QUERY(A1:C, "Select month(A)+1, sum(C) where A is not null group by month(A)+1 pivot B")
5. =QUERY(A1:C,"Select B, sum(C) where A is not null group by B pivot month(A)+1")

3. Query to Create Quarterly Summary Reports in Google Sheets

Suitable when you have data spanning multiple quarters in a year and multiple transactions per quarter:

For quarterly reports, use the quarter() function instead of month(). Here are the formulas:

1. =QUERY(A1:C,"Select quarter(A), sum(C) where A is not null group by quarter(A)")
2. =QUERY(A1:C, "Select sum(C) where A is not null pivot quarter(A)")
3. =QUERY(A1:C, "Select quarter(A), B, sum(C) where A is not null group by quarter(A), B")
4. =QUERY(A1:C, "Select quarter(A), sum(C) where A is not null group by quarter(A) pivot B")
5. =QUERY(A1:C,"Select B, sum(C) where A is not null group by B pivot quarter(A)")

4. Query to Create Yearly Summary Reports in Google Sheets

Suitable when you have data spanning multiple years:

To create yearly summary reports, use the year() function in the SELECT, GROUP BY, and PIVOT clauses:

1. =QUERY(A1:C,"Select year(A), sum(C) where A is not null group by year(A)")
2. =QUERY(A1:C, "Select sum(C) where A is not null pivot year(A)")
3. =QUERY(A1:C, "Select year(A), B, sum(C) where A is not null group by year(A), B")
4. =QUERY(A1:C, "Select year(A), sum(C) where A is not null group by year(A) pivot B")
5. =QUERY(A1:C,"Select B, sum(C) where A is not null group by B pivot year(A)")

5. Query to Create Weekly Summary Reports in Google Sheets

Suitable when you have data spanning multiple weeks in a year:

Creating weekly reports is slightly more complex. To simplify, we’ll use a helper column.

In cell D1, enter the following formula to return week numbers corresponding to the dates in column A:

=ArrayFormula(VSTACK("Week #", WEEKNUM(A2:A, 2)*(A2:A<>"")))

This returns Monday–Sunday week numbers. For Sunday–Saturday weeks, replace 2 with 1 in the WEEKNUM function.

Now, use the QUERY formulas for daily reports with two changes:

1. Select the data range A1:D instead of A1:C to include the helper column.
2. Replace column A in the SELECT, GROUP BY, and PIVOT clauses with column D.

Here are the updated formulas:

1. Grouped by Week:

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

2. Pivoted by Week:

=QUERY(A1:D, "Select sum(C) where A is not null pivot D")

3. Grouped by Week and Product:

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

4. Grouped by Week, Pivoted by Product:

=QUERY(A1:D,"Select D, sum(C) where A is not null group by D pivot B")

5. Grouped by Product, Pivoted by Week:

=QUERY(A1:D,"Select B, sum(C) where A is not null group by B pivot D")

Conclusion

The formulas above are the simplest way to create daily, weekly, monthly, quarterly, and yearly summary reports in Google Sheets.

You may want to customize these reports further—for example, displaying month names instead of numbers, showing week start and end dates instead of week numbers, or summarizing by month and year.

For more advanced techniques, explore the resources below or search this site.

Sample Sheet

Resources

1. How to Group Data by Month and Year in Google Sheets
2. Google Sheets: Using SUMIF to Sum by Month and Year
3. Group Dates in Pivot Table in Google Sheets (Month, Quarter, and Year)
4. Creating Month-Wise Summary in Google Sheets (Query Formula)
5. Sum Data by Month (Not Year) in Google Sheets with SUMIF
6. Month-Wise Pivot Table in Google Sheets Using Date Column
7. Google Sheets Query: Convert Month Number to Month Name
8. Creating a Weekly Summary Report in Google Sheets
9. How to Group by Week in Pivot Table in Google Sheets
10. How to Sum by Week of the Month in Google Sheets
11. How to Sum Values by the Current Week in Google Sheets
12. Query to Calculate Hours Worked Week-Wise in Google Sheets
13. Sum Current Work Week Range with QUERY in Google Sheets
14. SUMIF to Sum by Current Work Week in Google Sheets
15. Summarize Data by Week Start and End Dates in Google Sheets
16. How to Group Dates by Quarter in Google Sheets