Create Month Wise Summary in Google Sheets Using Query Formula

0
116
Create Month Wise Summary in Google Sheets

It’s not a tough task to create month wise summary in Google Sheets. There are few good options like Pivot, SUMIF and QUERY.

How to summarise data for month wise in Google Sheets? In Excel, there are plenty of ways like grouping, Pivot Tables, SUMIF etc. In Google Sheets also you can adopt all these methods to create a month wise summary of your report.

You may wonder whether grouping is there in Google Sheets like Excel? Yup! It’s there but with Query function. Here I am going to share you the tips to create a month wise summary report in Google Sheets with a one line Query formula.

How to Create Month Wise Summary in Google Sheets

For example purpose, I am going to create a month wise summary of a dummy sales report below. Follow the steps carefully.

Use this sample data to Create Month Wise Summary in Google Sheets.

sample data for month wise sales report in google sheets

What we are going to do with this data?

We are here going to prepare the month wise, product wise summary of each products and it will look like as below.

Use Query to Create Month Wise Summary in Google Sheets

Steps to Create Month Wise Summary in Google Sheets

First create the above sample data as it is in a blank spreadsheet. In a new sheet we are going to apply our one line Query formula to generate the month wise sales summary.

We have to join 2 main formulas for this purpose. So I will first explain each formulas one by one, independently.


Formula 1

Apply this formula in a new sheet. This is to just understand what the out put of this formula.

{{ArrayFormula(month(‘Sales Report’!A2:A))},{‘Sales Report’!B2:F}}

Tips: I’ve used Curly Brackets in the above formula. If you are unsure about the use of Curly Braces in Google Sheets, please go to the relevant tutorial page Here.

What is the purpose of the above formula? This formula converts each and every date to its numeric value representation of corresponding months. Refer the below image. The second part column A shows the converted value. We are using the above formula only for this purpose.

Generate combined formula to Create Month Wise Summary

How the above formula do the conversion. Here is that formula again.

{{ArrayFormula(month(‘Sales Report’!A2:A))},{‘Sales Report’!B2:F}}

The first part of the formula converts the date to corresponding numeric representation of months in row wise. Then the second part, combines the rest of the data to its right side.

But there is a problem. When we convert date to month this way, you may get unwanted numbers against the blank cells. Don’t worry we can remove that later.

So I hope now you can do such date to month conversion in Google Sheets with your original data also.


Formula 2

Actually to summarise the above sales report, we can use the below Query formula. But we can’t use this as it is. Why? Because we want to Create Month Wise Summary in Google Sheets, not date wise! So we have already a second data with month wise using the above formula.

query(‘Sales Report’!A2:F,“select A,B, sum(F) group by A,B label sum(F)””)

Tips: Learn Query Formula. Please check our Google Sheets Function Guide to pick this formula and learn the usage.

So we should replace the red coloured data range with our first formula.

Here is that combined formula.


Formula 1 and 2 Combined

query({{ArrayFormula(month(‘Sales Report’!A2:A))},{‘Sales Report’!B2:F}},”select A,B, sum(F) group by A,B label sum(F)””)

This also many not work! You should change column label A, B with corresponding column numbers like Col1, Col2 etc. See that modified formula.

query({{ArrayFormula(month(‘Sales Report’!A2:A))},{‘Sales Report’!B2:F}},”select Col1,Col2, sum(Col6) group by Col1,Col2 label sum(Col6)””)

This formula can perfectly summarise your sales report month wise. But as mentioned above, we should clean up the formula as we used infinitive rows like A2:A instead of A2:A7. So the final formula would be as follows. I’ve highlighted the clean up formula part also.


Final Formula to Create Month Wise Sales Summary Using Query

sort(query(ArrayFormula(if(len(‘Sales Report’!A2:A),{{ArrayFormula(month(‘Sales Report’!A2:A))},{‘Sales Report’!B2:F}},””)),”select Col1,Col2, sum(Col6) group by Col1,Col2 label sum(Col6)””))


Conclusion

If you know the use of Query and Curly Braces, you can easily modify this formula for your use. Other wise you may struggle little bit. So first switch to our Function Guide to learn the formulas used above.┬áThat’s all for now. Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here