Creating a Weekly Summary Report in Google Sheets

Published on

You can create a weekly summary report in Google Sheets using drag-down formulas, pivot tables, or the QUERY function. We’ll explore each option, starting with basic drag-down formulas.

The sample data below contains purchase dates in column A, expense categories in column B, and amounts in column C. Cells A1:C1 contain the field labels “Date,” “Description,” and “Amount.” Let’s create a weekly summary report using this data.

Sample data

Option 1: Weekly Summary Report Using Helper Columns and Non-Array Formulas

Step 1: Extracting Year and Week Numbers

To start, we need to add columns to capture the year and week number of each transaction. First, enter the field labels “Year” in D1 and “Week” in E1.

In cell D2, enter the following formula to get the year, and drag it down:

=YEAR(A2)

In cell E2, enter the following formula to get the week number, and drag it down:

=WEEKNUM(A2, 2)

This formula calculates the week number based on a Monday-Sunday week (if you want a different start day, replace 2 with the desired number from 1 to 17 as per the WEEKNUM function parameters).

Helper columns displaying year and week numbers for each transaction

Step 2: Setting Up the Weekly Summary Report Layout

In cell G3, enter the following formula to return unique categories (the TOCOL function removes empty cells):

=TOCOL(UNIQUE(B2:B), 1)

In cell H1, enter the following formula to get the unique years and months transposed for the report layout.

=TRANSPOSE(UNIQUE(D2:E))

Your weekly summary layout is now ready.

Step 3: Applying the SUMIFS Formula

Use the SUMIFS function to calculate the weekly totals based on the year, week, and category.

In cell H3, enter the following SUMIFS formula and drag it across and down:

=SUMIFS($C$2:$C, $B$2:$B, $G3, $D$2:$D, H$1, $E$2:$E, H$2)

You can use the SUM function to add a total below each column in H:N.

Weekly summary report created using drag-down formulas

Formula Explanation:

  • SUMIFS syntax: SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])
    • sum_range: $C$2:$C
    • criteria_range1: $B$2:$B
    • criterion1: $G3
    • criteria_range2: $D$2:$D
    • criterion2: H$1
    • criteria_range3: $E$2:$E
    • criterion3: H$2

Option 2: Weekly Summary Report Using a Pivot Table

The pivot table option is a quick way to create a weekly summary without complex formulas. Helper columns for year and week (D and E) are required for this method.

Steps:

  1. Select the range A1:E.
  2. Go to Insert > Pivot Table > Create, which creates a new sheet for the pivot table layout.
  3. Drag “Description” to the Rows section.
  4. Drag “Year” to Columns and then “Week”.
  5. Drag “Amount” to Values.
  6. Add Description to Filters and uncheck “Blanks.”

This will provide a pivoted summary by year, week, and category.

Weekly summary report created using Pivot Table

Option 3: Weekly Summary Report Using the QUERY Function

Using QUERY is another efficient way to create a weekly summary report. Here, too, we’ll use helper columns for year and week.

=QUERY(A1:E, "SELECT B, SUM(C) WHERE A IS NOT NULL GROUP BY B PIVOT D, E", 1)

This formula creates a matrix-style summary, where each row represents a category, and the columns are organized by year and week.

Weekly summary report generated with the QUERY formula

Formula Breakdown:

  • A1:E: Specifies the range of data to query.
  • "SELECT B, SUM(C)": Selects column B (categories) and calculates the sum of values in column C (amounts).
  • WHERE A IS NOT NULL: Ensures only rows with non-empty dates in column A are included.
  • GROUP BY B: Groups the results by each unique category in column B.
  • PIVOT D, E: Creates a pivoted layout, where each combination of values from columns D (Year) and E (Week) becomes a separate column in the output.
  • 1: Specifies that the first row contains headers.

This formula provides a summary table that groups expenses by category, year, and week.

Adding a Total Row

To add a dynamic total row, use the following formula and replace your_formula_here with the QUERY formula above:

=ArrayFormula(LET(ftr, your_formula_here, tc, 1, VSTACK(ftr, HSTACK("Total", DSUM(ftr, SEQUENCE(1, COLUMNS(ftr)-tc, tc+1), IFNA(VSTACK(CHOOSEROWS(ftr, 1), )))))))
Total row added to the weekly summary report

For more information on this approach, check out “Dynamic Total Row for FILTER, QUERY, or ARRAY Results in Sheets.”

Additional Tips

Year Array Formula: Instead of dragging down the year formula, you can use the following array formula in cell D2:

=ArrayFormula(IFERROR(YEAR(DATEVALUE(A2:A))))

Week Array Formula: Similarly, replace the week formula with an array formula in E2:

=ArrayFormula(IFERROR(WEEKNUM(DATEVALUE(A2:A), 2)))

Resources

This guide provides a comprehensive approach to creating weekly summary reports in Google Sheets using different methods for varying levels of complexity and needs. Choose the approach that best fits your reporting requirements!

Prashanth KV
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.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.