How to Link Multiple Excel Sheets with Formulas and SUM in WPS Office
Question details
The user wants to combine values from several worksheets and calculate totals, including a total for data from the previous 24 hours.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a workbook that has multiple worksheets and needs a formula to total fixed ranges or dynamically match column headers.
- Observed behavior
- Manual cell selection across several sheets is difficult; a formula is required to aggregate values from multiple worksheets and, where timestamps exist, filter for the last 24 hours.
Open your workbook in WPS Office Spreadsheets and identify the sheet names, target ranges, and whether a date/time column exists. This determines whether to use a fixed SUM, a dynamic FILTER/TOCOL formula, or a SUMIFS formula for the previous 24 hours.
Use SUM with Separate Sheet References for Fixed Ranges
Use this when the worksheet names and cell ranges are already fixed and you need a quick total from several sheets.
This is the simplest approach. WPS Office Spreadsheets uses the same syntax as Excel, so an Excel workbook can use this formula without changes.
Click the cell where the total should appear, then type =SUM(.
Click the first worksheet tab and select the range such as D5:E5, type a comma, then repeat for the next sheet.
Press Enter. The result is similar to =SUM('Titanium Data'!D5:E5,'Dryer Data'!D5:F5,'B1 Data'!D5:F5,'Retail Blend Data'!D5:F5).
Create a Dynamic Header-Based Sum with FILTER and TOCOL
Use this when you want to sum row 5 across a range of worksheets based on a matching header in row 3.
Total Only the Previous 24 Hours with SUMIFS and a Date/Time Column
Use this when your source data has a timestamp column and you need to sum values created or updated in the last 24 hours.
Calculate Multi-Sheet Totals in WPS Office Spreadsheets
WPS Office Spreadsheets lets you total fixed ranges, match headers dynamically, and filter records from the previous 24 hours with Excel-compatible formulas. You can work in the familiar spreadsheet interface and save back to .xlsx without losing formulas.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the worksheets you want to total.
- 2. Enter a formula: Click the destination cell, then type =SUM( and select the ranges from each worksheet, or use SUMIFS/FILTER for dynamic totals.
- 3. Check the result: Press Enter to confirm the formula and verify that the returned total matches the selected sheets and ranges.
- 4. Save your workbook: Press Ctrl+S or click Menu > Save As and choose Excel Workbook .xlsx to keep formulas intact.
Frequently Asked Questions
How do I sum the same range across multiple sheets by using sheet references?
Use a formula such as =SUM('Titanium Data'!D5:E5,'Dryer Data'!D5:F5,'B1 Data'!D5:F5,'Retail Blend Data'!D5:F5). In WPS Office Spreadsheets, click each sheet tab while building the formula; WPS enters the sheet name and range automatically.
Can I use a 3D reference to sum all worksheets between two sheets?
Yes. For consecutive sheets you can use a starting and ending sheet name separated by a colon, such as =SUM('Titanium Data:Retail Blend Data'!D5). This totals the same cell or range in every sheet in that group.
How do I calculate totals for the previous 24 hours in WPS Office?
Add a date/time column to your data and use SUMIFS with a criteria such as ">="&NOW()-1. If the data is on multiple sheets, wrap several SUMIFS results inside =SUM(SUMIFS(...),SUMIFS(...)).
Does WPS Office support FILTER and TOCOL for dynamic cross-sheet formulas?
WPS Office supports many dynamic array functions. If FILTER or TOCOL is not available in your version, update WPS Office to the latest release or use the fixed SUM method.
What should I do when a sheet name contains spaces in a formula?
Wrap the sheet name in single quotes, for example 'Titanium Data'!D5. WPS Office does this automatically when you click a sheet tab while creating a formula.
