HomeGoogle DocsSpreadsheetHow to Create a Weekly Summary Report in Google Sheets

How to Create a Weekly Summary Report in Google Sheets

Published on

Have you ever created a weekly summary report in Google Sheets, as shown below? If not, get ready! You can learn here how to create a weekly summary report in Google Sheets.

How to Create a Weekly Summary Report in Google Sheets

It looks like a Pivot Table group-by-week summary in Google Sheets. But with Pivot Table in Google Sheets, you can’t generate a report like this.

Here, for the example purpose, I am using a three-column personal daily expense sheet. From this sheet, I want to find my weekly expense summary that also description-wise.

If you carefully analyze the above report populated in the range E2:J8 (the range is auto-adjusting based on the data in Column A2:C), you can understand one thing. It has some similarities with the Pivot Table report.

I’ll use the Google Sheets Query function and it’s Pivot feature to create this weekly summary report.

I am sure about one thing. Some of you may be familiar with creating monthly summary reports in Google Sheets.

But it’s tricky to create a weekly summary in Google Sheets. Here is that awesome formula example.

Sample Data for Creating a Weekly Summary Report in Google Sheets

On a blank Spreadsheet, type the below data as it’s. It spreads out only in a few rows, but enough to populate a weekly summary report in Google Sheets.

Sample Data for Weekly Summary in Google Sheets

Before going to the steps, first, try the below master formula in your sheet. You may find the formula a little complicated, but it’s not so. You can learn it.

I’ll try to explain every bit of this formula in detail so that you can create a weekly summary report in Google Sheets, hassle-free.

Query Formula to Populate Weekly Summary from Date Column (Master Formula)

First, apply the following formula (to summarise the data in A2:C) in Cell E2 and see the result. Then I will explain to you how to create this Query formula to summarise your data week-wise.

={query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C},"Select Col2,Sum(Col3) where Col1>0 group by Col2 Pivot Col1 Label Col2'Description'",0);transpose(query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C},"Select Sum(Col3) where Col1>0 group by Col1 label Sum(Col3)'Grand Total'",0))}

You only require the bold part of the formula to populate the weekly summary.

The rest of the formula is to populate the total row at the end of the summary.

You may ask why I’m telling this because I want to convince you that the formula is not much complicated as you may think.

To make this tutorial easy for you, I’ve created a sample sheet where you can see the above sample data and the master formula in action.

Further, I’ve split the master formula to formula_1, formula_2, and formula_3 as detailed below under different subtitles. I’ve entered that also into the shared sheet to enable you to learn how to create a weekly summary report in Google Sheets step-by-step.

Weekly Demo and Formula

The above spreadsheet file is in the “Copy” mode. Now back to our tutorial.

Weekly Report Using Query – Master Formula Explanation

From the above sample data, it’s impossible to create a weekly summary report in Google Sheets. Do you know why?

The reason is the date entry in column A. First, you should convert the dates in column A to weeks. Wait! No need to do anything manually.

Our master formula handles this. Here it’s just an explanation of how the master formula does this.

Formula 1: Converting Date Column to Week Numbers and Retaining Other Columns (Query Data)

For explanation, we can split the master formula that creates a weekly summary in Google Sheets into three parts. They are Formula_1, Formula_2, and Formula_3.

Here is the explanation for the first part. I’ve applied this formula part separately in cell L2 in the above-shared sheet for you.

={ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C}

Go through the explanations below.

The WEEKNUM Array Formula to Convert Date Column Entry to Week Numbers

=ARRAYFORMULA(WEEKNUM(A2:A))

This formula converts dates in the range A2:A to Week Numbers.

Each week in a year has different identification numbers called Week numbers. Here the week begins on Sunday and ends on Saturday.

If you want any other start and end day like Monday to Sunday, please check date functions under my Function guide. There you can see how to use the Google Sheets WEEKNUM function in detail.

Retaining Remaining Columns in the Source

Another part of the above formula 1 is the cell range B2:C. It just returns the referred column range as it is.

=B2:C
The Role of WEEKNUM in Weekly Summary Report

On the screenshot, I’ve marked the individual results of the above WEEKNUM and cell range reference. Additionally, you can see that I’ve used ARRAYFORMULA, IF, and LEN functions.

We have to use the ARRAYFORMULA with WEEKNUM when the latter is being used in a range. If it’s in a single cell, it’s not required.

Then the use of IF and LEN. This combo is most common in almost all formulas where infinite ranges (A2:A) are in use. It will exclude blank rows in the output.

If our range is A2:A13, you can avoid the use of this combo. I have a dedicated tutorial on the combined use of IF and LEN here. If you wish, you can check that.

Formula 2: Pivot Week Numbers to Create Weekly Summary in Sheets

Please scroll up and check the master formula for the bold part.

In the shared Google Sheet, you can see this formula (bold part of the master formula) entered in cell P2.

=query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C},"Select Col2,Sum(Col3) where Col1>0 group by Col2 Pivot Col1 Label Col2'Description'",0)

I have already explained part of this formula, i.e., {ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C}, above which acts as the DATA in Query.

The rest is simple if you know how to use the Query function in Google Doc Spreadsheets. It generates the weekly summary.

Yup! It is my answer to how to create a weekly summary report in Google Sheets. Let me explain it. I know I should first introduce you to the Query function syntax.

Query Syntax for Explanation Purpose

QUERY(data, query, [headers])

If we compare the above Formula 2 with Query syntax, the below part is our “data” in Query.

{ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C}

We have already learned it.

The rest of the part of the formula is the “query.” It groups the data column 2 (description) and sum Column 3 (amount).

In “headers,” I’ve put 0 as our data doesn’t contain any header row (See the above screenshot). Further, I’ve used the Pivot clause in Query.

You can use the formula as it’s with any data by just changing the cell references. If you want to learn this in detail, please check the below two tutorials.

  1. Learn Query Function with Examples in Google Sheets.
  2. How to Use QUERY Function Similar to Pivot Table in Google Sheets.

The result would be as below.

Description Wise Summary and Pivot

We have already created a weekly summary report in Google Sheets. Here 1, 2, 3, 4, and 5 are week numbers. But what we lack is a total row at the bottom of this data.

Let’s see how to achieve it.

Formula 3: Weekly Total Column at the Bottom of Weekly Summary Report in Query

How to create a Total row at the end of the summary? Here is that formula. You can see this formula as entered in Cell V2 of the shared sheet.

=transpose(query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C},"Select Sum(Col3) where Col1>0 group by Col1 label Sum(Col3)'Grand Total'",0))

This formula is the one that’s not in bold in our master formula. This Query formula populates a total row.

It has all the elements of Formula 2 above except minor changes in Query. It’s actually a copy of Formula 2.

What are those changes?

In formula 2, we grouped column 2, i.e., the Description. Then used the Pivot clause to distribute the group total to week numbers.

But here, the grouping applied to column 1 (week number) as we want the weekly sum and, of course, no Pivot.

Additionally, you can see the TRANSPOSE function in the formula because the Query formula in formula 3 returns the summary (total) vertically. We want it horizontally.

Formula 4: Weekly Summary Report with Grand Total Column in Sheets

There is no formula 4. It’s our final formula. This formula is the combination of above formula 2 and 3 that just joined by Curly Braces.

The above is an advanced formula to create a weekly summary report in Google Sheets.

With the help of the above details and analyzing the shared Google Sheet, I hope you can learn it.

If you want any help related to this formula, please drop it in the comments below. Enjoy!

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

14 COMMENTS

    • Hi, John,

      I’m not clear. You can copy my sample sheet and point out the error or leave your suggestions within that sheet.

      Share that copy in your comment/reply below.

  1. Hello!

    Your formula worked successfully for me!

    But I would like to get the Week number to display as the end of week date (Day of Week, Day, Month, Year).

    I tried using WEEKDAY formulas but only returned a non-formattable number.

    Any suggestions to make this work? Thanks!!

  2. I used the Weeknum formula in this example with mostly success.
    It starts at week 2 though. My first date in column A is 1/4/2021, shouldn’t that be Week 1?
    Col1 is dates and Col10 is Price.

    ={query({ArrayFormula(if(len('2021ContractsData'!A2:A),
    (WEEKNUM('2021ContractsData'!A2:A,2)),)),'2021ContractsData'!A2:J},
    "Select Col1, Count(Col10) Where Col2 is not NULL group by Col1 Label Col1 'Week',Count(Col10) 'Contracts'",0)}

    I also get an error if I add Sum in the query:
    “Error: Unable to parse query string for Function Query Parameter 2: AVG_SUM_ONLY_NUMERIC”
    Thank you for the multitude of pages and instructions.

    • Hi, Gene Wilkins,

      I have tested your formula. It has one issue.
      We have added one virtual column to the data that contains week numbers. It’s column # 1.
      So the Sum and Count must be performed on column 11, not on column 10.

      ={query({ArrayFormula(if(len('2021ContractsData'!A2:A),
      (WEEKNUM('2021ContractsData'!A2:A,2)),)),'2021ContractsData'!A2:J},
      "Select Col1, Count(Col11), Sum(Col11) Where Col2 is not NULL group by Col1
      Label Col1 'Week',Count(Col11) 'Contracts',Sum(Col11) 'Amount'",0)}

      Regarding the issue with the week number, please check the functions WEEKNUM and ISOWEEKNUM on my function guide.

  3. I tried applying the formula as is to my data and restructured my data to reflect exactly what is shown in the examples above. I’m getting the following error code.

    Error
    “Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 9999. Actual: 9997.”

    • Hi, rex wu,

      Thanks for sharing your sheet that enabled me to find the cause of the said error.

      I have updated the formula in this tutorial and also in my sample sheet. That will solve your issue.

  4. Hi,

    Thanks a lot for creating these amazing tutorials on Google sheet. I’m learning a lot from you.

    I hope you can help me create a similar report like this. Instead of the Week number, I would like to create an array to show the exact first date of that Week number (Monday being the first day and Sunday is the last day).

    I’m currently, manually adding a formula on my Datasheet to get that date from your previous post too.

    I’m sharing the report to you, hope you can take a look at it.

    Thanks in advance.

    • Hi, Allan,

      Please ignore the yellow highlighted helper columns in the “Data” tab. I have added a new column (cyan) that returns week start dates.

      Find the below formula in cell G1 in your “Data” tab.

      ={"Week";ArrayFormula(if(len(A2:A),A2:A-WEEKDAY(A2:A,2)+1,))}

      You can read the formula explanation HERE.

      Regarding the summary based on week start dates, there are four formulas in the “Report” tab. The corresponding cells are highlighted in cyan.

      Here is your copy of the Sheet – Weekly Start Date Summary – II.

      Best,

      • Wow, that’s really cool. That is what I need in the report that I’m doing.

        If I need to create a report based on Campaign Name. I know I can use some formula like SumIFs and SumProduct to do this. Is there an arrayformula to do this?

        Thanks so much.

        • Hi, Allan,

          That seems quite easy!

          Copy the formula from B7 to B18 (Report tab) and change the column G to C in both the Select and Group clause in Query. Similarly, copy the B9 formula to B20 and make the above same changes.

          You can check my above-shared sheet (Sheet shared in my previous reply) for the modified formulas.

          Best,

  5. Very nice! This is very close to what I am looking for.

    I tried adjusting with similar data but only created errors. Once I added monthly columns the errors came up.

    I have included an example sheet, see the “Modified Days of Week” tab. I also wanted to see the amounts for the current Days of the week.

    Link: Removed by admin

    Can you help with fixing the errors?

    • Hi, Howard,

      Sorry for the delay in replying. The formula you entered is correct and perfectly follows my instructions. The mistake was from my side!

      Sorry to say that I failed to foresee one error related to blank cells in the description and amount columns.

      Minor tweaking of the formula can sort out the issue.

      You just need to replace the code query(B6:E) to B6:E. It appears twice in the formula. Please give enough rows and columns for this weekly summary formula to expand.

      Also please check Formula to Sum by Current Week in Google Sheets.

LEAVE A REPLY

Please enter your comment!
Please enter your name here