WPS Spreadsheet: Sum Values Through Today with Merged Weekly Headers
Question details
The user needs to sum daily values from a table through the current date while the visible weekly headings are merged.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet uses merged weekday or weekly headings, and the user wants a running total that includes every day up to today.
- Observed behavior
- Merged heading cells do not provide a separate date criterion for each day, so they cannot reliably be used as the SUMIFS criteria range.
Add or verify one actual date for every daily column in the header row, such as row 7, and confirm that the values to be totaled are in the matching row, such as row 8. Keep the merged weekday headings for display if needed, but use the unmerged date row for calculations.
Use actual daily dates with SUMIFS
Use this method when merged weekly headings must remain visible but the total should include every value dated today or earlier.
A merged heading represents a visual group rather than a separate date in each column. Store the real date for every day in a dedicated row, then format those dates as abbreviated weekdays so the sheet keeps its existing appearance.
In WPS Spreadsheet, enter the actual date for each day in row 7 across the columns used by the data, such as E7:AM7.
Select the date row, open Format Cells from the right-click menu or press Ctrl+1, choose Custom, and enter ddd so the dates display as Mon, Tue, Wed, and so on.
Select the total cell and enter =SUMIFS(E8:AM8,E7:AM7,"<="&TODAY()), replacing the ranges with the matching value and date ranges in your worksheet.
Press Enter and check that only values whose dates are today or earlier are included. The result updates when WPS Spreadsheet recalculates the workbook on a later day.
Build Date-Based Running Totals in WPS Spreadsheet
WPS Spreadsheet lets you store real dates behind readable weekday labels and use SUMIFS with TODAY() to calculate totals through the current date. Its familiar spreadsheet tools support a smooth workflow for Excel-compatible workbooks.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the merged weekly headings.
- 2. Create the date criteria row: Enter one actual date per day in a visible or hidden row, then use Format Cells and the custom format ddd to display weekday abbreviations.
- 3. Add the calculation: In the result cell, enter =SUMIFS(E8:AM8,E7:AM7,"<="&TODAY()) using the value row and date row from your sheet.
- 4. Check the result: Press Enter and verify that the total includes dates up to today while excluding future dates.
Frequently Asked Questions
Why can’t I use merged weekday headings in SUMIFS?
Merged headings do not contain a separate date value in every daily column. SUMIFS needs one criterion value for each cell in its criteria range, so use an unmerged row containing the actual date for each day.
How do I show Mon, Tue, and Wed while keeping real dates?
Select the date row, open Format Cells with Ctrl+1, choose Custom, and enter ddd. The cells retain their actual dates while displaying abbreviated weekday names.
What formula sums values through today in WPS Spreadsheet?
Use =SUMIFS(E8:AM8,E7:AM7,"<="&TODAY()), where E8:AM8 contains the values and E7:AM7 contains the corresponding actual dates.
Will the total update automatically tomorrow?
Yes. TODAY() returns the current date, so the formula includes the new day after the workbook recalculates. Press F9 or reopen the workbook if automatic recalculation is disabled.
