How to Calculate Rolling Weekly Totals in an Excel Table
Question details
The user needs to compute continuous Monday-to-Sunday weekly totals directly inside an Excel table automatically, without manual calculations, handling datasets that may have missing dates.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a dynamic dataset containing dates and values where aggregate totals need to reset at the start of each new week while keeping all data visible in one unified table.
- Observed behavior
- The user is looking for a formula-based solution to track rolling weekly sums within the table itself rather than extracting the data into a separate Pivot Table.
Ensure your data is formatted as an official Excel Table (Insert > Table) so that formulas automatically expand as you add new rows, and verify your dates are formatted as true date values rather than text.
Use Calculated Columns with WEEKDAY and IF Functions
Create a 'Start of Week' identifier column to group dates, and then use an IF formula to reset the running total at the beginning of each new week.
By utilizing the WEEKDAY function, you can mathematically determine the Monday of any given week regardless of missing dates in your sequence. An IF statement can then check if the week has changed compared to the row above it, either resetting the sum or adding to the running total.
Insert a new column in your table and name it 'Start of Week'. Enter the formula =[@Date]-WEEKDAY([@Date],3). This subtracts the current weekday index from the date, returning the date of that week's Monday.
Insert another column for your weekly totals. Assuming your 'Start of Week' column is column D and your values are in a column named 'ValueA', select the first data cell in this new column.
Input a formula similar to: =IF(D1<>[@[Start of Week]],[@ValueA],E1+[@ValueA]). Make sure to adjust 'D1' to reference the cell immediately above your current 'Start of Week' cell, and 'E1' to reference the cell immediately above your formula cell.
Press Enter. Because it is an Excel Table, the formula will automatically spill down the column. Check the points where dates transition to a new week to ensure the total successfully resets.
Calculate Weekly Totals Seamlessly with WPS Spreadsheet
WPS Spreadsheet provides full support for advanced table formatting and dynamic formulas like WEEKDAY and IF, making it incredibly easy to track rolling weekly totals just as you would in Microsoft Excel.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your dates and values.
- 2. Format as Table: Highlight your data and press Ctrl+T (or go to Insert > Table) to format it as a dynamic table.
- 3. Calculate week starts: Insert a new column and apply the WEEKDAY formula to group your dates by week.
- 4. Add the rolling total: Insert a final column and use the IF formula to conditionally add values or reset the total at the start of a new week.
Frequently Asked Questions
Why does WEEKDAY([@Date],3) return Monday as the start of the week?
In spreadsheet applications, the WEEKDAY function with a return_type of 3 assigns Monday as 0 through Sunday as 6. Subtracting this number from your original date effectively rolls the date backward to the most recent Monday.
What happens if there are missing dates in the dataset?
Because the Start of Week formula relies strictly on the mathematical date value of the current row rather than the sequential order of rows, missing dates will not disrupt the calculation. The formula will correctly identify the week regardless of skipped days.
Can I use a Pivot Table instead of formulas to get weekly totals?
Yes. You can insert a Pivot Table, right-click the Date field, select 'Group', and group by 'Days' while setting the number of days to 7 starting on a Monday. However, this creates a separate summary report rather than keeping the running totals side-by-side with your raw table data.
