Creating a month-wise summary report in Google Sheets doesn’t have to be a daunting task. In fact, there are several options available to achieve this, including the Pivot Table, SUMIF function, and the versatile QUERY function.
In Excel, there are various menu commands and functions for generating such reports. However, in Google Sheets, you can adopt almost all of these methods except for the Subtotal. But don’t worry, because we have the QUERY function, which is the most powerful and versatile tool in Google Sheets.
So, let’s dive into this tutorial and learn how to create a month-wise summary report in Google Sheets using the QUERY function.
How to Create a Month Wise Summary Report in Google Sheets
Sample Data:
In this tutorial, we will prepare a month-wise summary of each product by grouping the date and product columns. Follow the step-by-step instructions below:
Step 1: Group by Month
The following QUERY formula can be used to group a date column by month and sum another column in Google Sheets:
=QUERY(A1:F,"SELECT MONTH(A)+1, SUM(F) GROUP BY MONTH(A)",1)The formula takes three arguments:
- The range of data to be queried (A1:F in this case).
- The query statement, which selects the months of the dates in column A and sums the values in column F, grouping the results by month.
- The number of header rows (1 in this case).
However, the month numbers in the result might appear “incorrect” because the MONTH scalar function in QUERY returns month numbers as zero-based integers. For example, January is shown as 0, February as 1, and so on.
To fix this, you can adjust the month numbers using the following formula:
=QUERY(A1:F,"SELECT MONTH(A)+1, SUM(F) GROUP BY MONTH(A)+1",1)You can also remove any blank rows in the result by adding a condition:
=QUERY(A1:F,"SELECT MONTH(A)+1, SUM(F) WHERE A IS NOT NULL GROUP BY MONTH(A)+1",1)To make the labels tidy, you can use the LABEL clause:
=QUERY(A1:F,"SELECT MONTH(A)+1, SUM(F) WHERE A IS NOT NULL GROUP BY MONTH(A)+1 LABEL MONTH(A)+1 'MONTH', SUM(F) 'TOTAL'",1)
We have successfully created a basic month-wise summary report in Google Sheets, which includes a month column and a total column.
Step 2: Group by Month and Product
To group the data by both the month and product, you can include the product column in both the SELECT and GROUP BY clauses. Here is the final formula:
=QUERY(A1:F,"SELECT MONTH(A)+1, B, SUM(F) WHERE A IS NOT NULL GROUP BY MONTH(A)+1, B LABEL MONTH(A)+1 'MONTH', SUM(F) 'TOTAL'",1)
Congratulations! You have successfully created a month-wise summary report using the QUERY function in Google Sheets.
COUNT, AVERAGE, MIN, and MAX in Month Wise Summary in Google Sheets
When creating a month-wise summary report using the QUERY function, you can also use aggregation functions other than the SUM function. For example, you can calculate the average, count, maximum, and minimum values in a group of data.
To calculate the monthly average sales summary of products, you can use the following formula:
=QUERY(A1:F,"SELECT MONTH(A)+1, B, AVG(F) WHERE A IS NOT NULL GROUP BY MONTH(A)+1, B LABEL MONTH(A)+1 'MONTH', AVG(F) 'TOTAL'",1)Conclusion
When replacing QUERY data with imported data using the IMPORTRANGE formula, remember to replace column identifiers with column numbers in the query statement. For example, use Col1 to represent the first column, Col2 for the second column, and so on.
Here is an example formula:
=QUERY(IMPORTRANGE("URL","Sheet1!A1:F"),"SELECT MONTH(Col1)+1, Col2, AVG(Col6) WHERE Col1 IS NOT NULL GROUP BY MONTH(Col1)+1, Col2 LABEL MONTH(Col1)+1 'MONTH', AVG(Col6) 'TOTAL'",1)If you prefer to use month names instead of numbers, you can use the EOMONTH function in the QUERY data.
You can explore more helpful tutorials on Google Sheets Query:
- Google Sheets Query: How to Convert Month in Number to Month Name in Text
- How to Group Data by Month and Year in Google Sheets
Happy querying!
