How to Calculate Weekly Sums in Google Sheets

Introduction to Weekly Data Aggregation in Google Sheets

Aggregating values by time unit is a common necessity when analyzing performance metrics, such as sales volumes, website traffic, or resource consumption. While daily or monthly summaries are straightforward, weekly aggregation requires a slightly more complex approach because weeks are not a standard, easily separable unit within a date column alone. To effectively summarize data by week, we must first derive a numerical index representing the week number of the year for every date entry. This numerical index then serves as the criterion for our conditional summation.

The method outlined below ensures accuracy and repeatability. It uses dynamic formulas that adjust automatically as new data is added, providing a powerful and efficient alternative to manual sorting or complex pivot tables, particularly when dealing with large datasets spanning multiple months or years. Understanding the role of each function—from indexing the weeks to isolating unique criteria and finally executing the conditional sum—is key to mastering this data manipulation technique in Google Sheets.

Prerequisites: Understanding the Core Functions

Successfully summarizing data by week relies on three fundamental functions working in tandem. Before proceeding with the implementation steps, it is beneficial to grasp the specific role and syntax of each required component:

• The WEEKNUM function: This function is crucial for transforming a raw date into a recognizable week identifier. It returns the week number of the year for a given date. For instance, the first few days of January will typically return ‘1’, while dates in mid-July might return ’28’. The standard syntax is =WEEKNUM(date).
• The UNIQUE function: Once we have extracted the week number for every date, we need a clean list of the distinct weeks present in our dataset. The UNIQUE function handles this filtering process, returning only the non-duplicate entries from a specified range. This list forms the criteria against which we will perform the summation.
• The SUMIF function: This is the final aggregation step. The SUMIF function sums values in a specified range based on a single condition (criterion). In this context, it will sum the sales figures (the sum range) only if the corresponding week number (the range) matches a unique week number from our generated criteria list.

Mastering these three functions provides a pathway not only for weekly summation but also for conditional data analysis across many other grouping categories within your spreadsheet workflow.

Step 1: Preparing the Raw Data Structure

The initial step requires organizing the data into a standard table format containing at least two critical columns: the date column and the corresponding numerical value column (the values you wish to sum). For this example, we will track the total sales of a product across various dates over a short period.

It is crucial that the dates are formatted correctly within Google Sheets (usually recognized automatically as dates) to ensure the subsequent time-based functions operate without error. Establishing a clear, organized structure in the initial stage simplifies the formula application and enhances the overall readability of the subsequent analysis. We begin by entering the raw transactional data, demonstrating the total sales recorded on various dates:

In this example, Column A holds the transaction date, and Column B holds the sales volume (the metric we intend to aggregate). Columns C and D are typically left blank initially, reserved for the calculation steps we will execute in the following stages of this detailed guide. Ensure that your date column is consistent and complete before moving forward.

Step 2: Isolating the Week Number with the WEEKNUM function

The crucial step that enables weekly aggregation is the extraction of the numerical week index from the date column. This is achieved using the powerful WEEKNUM function. This function takes a date as its input and returns an integer representing which week of the year that date falls into, typically ranging from 1 to 52 or 53. This index serves as the unique key for grouping our sales data.

To implement this, navigate to the cell directly adjacent to your first data point in a new column dedicated to the week index. In our structure, this corresponds to cell D2. Here, we will reference the corresponding date in Column A (cell A2) and apply the function. The standard formula we input into cell D2 is precisely:

=WEEKNUM(A2)

After entering the formula, press Enter. Google Sheets will display the week number for the date in A2. To apply this logic to the entire dataset, we must utilize the fill handle—the small square at the bottom-right corner of the selected cell. We drag and fill this formula down to every remaining cell in Column D, covering all rows that contain date entries in Column A. This action dynamically adjusts the cell reference (A2 becomes A3, A4, and so on) ensuring that every transaction is correctly mapped to its respective week index.

The result is a new column (Column D) that serves as the basis for grouping. Every row now has a consistent identifier—the week number—which allows us to proceed to the conditional summation step seamlessly. This preparation is the bedrock of the entire aggregation process, transforming chronological data into groupable data segments suitable for advanced data analysis techniques.

Step 3: Generating Unique Weekly Criteria using the UNIQUE function

Our next requirement is to establish a distinct list of the weeks that exist within our data. Since transactions may span several days within the same week, Column D contains many repetitive week numbers. The goal of the summary is to produce one total per week, requiring a clean list of criteria for the subsequent conditional summation.

This task is perfectly suited for the UNIQUE function. This powerful array function is designed to take an input range and automatically return an output list containing only the unique values found within that range, eliminating all duplicates in a single operation. We typically place this criteria list in a new column, such as Column E, starting in cell E2.

In cell E2, we enter the following formula, referencing the entire range of calculated week numbers (D2 through D12 in this specific example):

=UNIQUE(D2:D12)

Upon execution, the UNIQUE function spills the results down Column E, generating a concise list of all unique week numbers present in the dataset (in this case, weeks 1, 2, and 3). This column now functions as the core criteria list, defining the exact number of rows our final weekly summary table will contain.

This approach is particularly efficient because it is dynamic. If your source data later expands to include transactions for Week 4, the output of the UNIQUE function will automatically update to include ‘4’ in the criteria list, ensuring your summary table remains comprehensive without requiring manual adjustment of the criteria range. This dynamism is one of the chief advantages of using this formula combination over static grouping methods.

Step 4: Calculating Aggregate Totals using the SUMIF function

With the criteria established, we can now perform the aggregation using the SUMIF function. This function is essential for conditional calculations and requires three arguments to operate effectively: the range to check the condition against, the specific criterion, and the sum_range containing the values to be added up.

In our structure, the arguments map as follows:

1. Range: The entire column of calculated week numbers ($D$2:$D$12). We must use absolute references (using dollar signs) to ensure this range does not shift when the formula is dragged down.
2. Criterion: The specific unique week number we are currently evaluating (E2). This must be a relative reference so it changes (E2, E3, E4…) as we drag the formula down.
3. Sum Range: The column containing the sales figures or values we wish to sum ($B$2:$B$12). This must also be an absolute reference.

We input the comprehensive formula into cell F2:

=SUMIF($D$2:$D$12, E2, $B$2:$B$12)

This formula instructs Google Sheets to look through the weekly index in Column D, find all instances matching the unique week number listed in E2, and then sum the corresponding sales figures from Column B. After calculating the sum for the first week, we then drag and fill this formula down to the remaining cells in Column F, automatically calculating the total sales for Week 2, Week 3, and any subsequent weeks listed in the unique criteria column.

Interpreting and Utilizing the Weekly Summary Results

The final output in Column F provides a clear, concise summary of the aggregate performance metrics, grouped precisely by the calendar week. This weekly aggregation is significantly more informative than reviewing daily transactions and offers a balanced perspective that smooths out daily fluctuations, making underlying trends more apparent.

From the calculated output, we can immediately derive actionable insights regarding product performance over time, which forms the basis for effective reporting:

• The first week of the year generated a total sales volume of 22 units.
• The second week demonstrated a slight increase, resulting in 24 total sales.
• The third week showed a substantial jump in performance, achieving 63 total sales.

Such results are invaluable for operational review. For instance, the significant leap in sales during Week 3 prompts further investigation: Was this driven by a specific marketing campaign, a change in pricing, or seasonal demand? This process transforms raw data into a structured format ready for deeper data analysis and visualization, enabling management to make data-driven decisions based on tangible, time-bound results. The ability to identify these weekly patterns quickly is a powerful advantage of this aggregation method.

Further Resources for Advanced Analysis

While the combination of the WEEKNUM function, UNIQUE function, and SUMIF function provides a robust solution for weekly aggregation, Google Sheets offers many other time-series analysis tools. Expanding your knowledge of conditional summing and date manipulation will significantly enhance your spreadsheet proficiency and ability to produce complex reports.

For those interested in exploring related aggregation techniques, particularly those involving financial or cumulative metrics, the following resources may be highly beneficial:

How to Calculate Year to Date Values in Google Sheets