In Google Sheets, the Query function is the most suitable method for summarizing data, regardless of whether it is text, numbers, or dates. Whether you have a business with daily transactions or you’re a blogger tracking site traffic, Query is the best tool to create daily, weekly, monthly, quarterly, or yearly report summaries.
Query to Create Daily Report in Google Sheets
To create a daily report in Google Sheets, you can use the Query formula along with the group clause. Here’s an example of how to create a daily sales report:
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")If you want to include the product name in the summary, you can use this Query formula instead:
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")This will give you a daily summary report organized by date and product.
Query to Create Monthly Reports in Google Sheets
Creating monthly reports in Google Sheets is similar to creating daily reports, except for the use of the month() scalar function. Here’s an example:
Formula: Monthly Sales Summary Involving Date and Qty. Columns
=QUERY(A1:F8,"SELECT MONTH(A)+1, SUM(F) WHERE A IS NOT NULL GROUP BY MONTH(A)+1")Note that the month() function returns 0 for January and 11 for December, so you need to add +1 to the result to get the actual month number. You can also include the product name in the summary by using this formula:
Formula: Monthly Sales Summary Involving Date, Product, and Qty. Columns
=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
To create yearly reports in Google Sheets, you can use the year() scalar function. Here’s an example:
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)")If you want to include the product name in the summary, you can use this formula:
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
To create quarterly reports in Google Sheets, you can use the quarter() scalar function. Here’s an example:
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)")If you want to include the product name in the summary, you can use this formula:
Formula: Quarterly Sales Summary Involving Date, Product, and Qty. Columns
=QUERY(A1:F8,"SELECT QUARTER(A), C, SUM(F) WHERE A IS NOT NULL GROUP BY QUARTER(A), C")Query to Create Weekly Reports in Google Sheets
To create weekly reports in Google Sheets, there is no built-in scalar function called weeknum(). Instead, you can use the WEEKNUM function along with Query. Here’s an example:
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")If you want to include the product name in the summary, you can use this formula:
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")These formulas will give you a weekly summary report organized by date and product.
Now that you know how to create daily, weekly, monthly, quarterly, and yearly report summaries using Query in Google Sheets, you can start analyzing your data more efficiently. Remember to use the appropriate scalar functions and adjust the formulas based on your specific data.
For more tutorials and resources on Google Sheets and data analysis, visit Crawlan.com.
Sheet Template
Additional Tips/Resources to Create Daily/Weekly/Monthly/Quarterly/Yearly Report Summary in Google Sheets:
- Filter by Month and Year in Query in Google Sheets.
- Month, Quarter, Year Wise Grouping in Pivot Table in Google Sheets.
- How to Group Data by Month and Year in Google Sheets.
- Google Sheets Query: How to Convert Month in Number to Month Name in Text.
