WPS Spreadsheet: Sum Products in Visible Filtered Table Rows by Date
Question details
Calculate the sum of multiplied values while including only the rows currently visible after filtering a table by date.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A table contains quantity and work-time values, and the user needs a filtered total based only on rows that remain visible.
- Observed behavior
- A normal SUMPRODUCT calculation includes hidden or filtered-out rows instead of summing only the visible records.
Confirm the table name and the exact column names for Quantity and Work Time. Apply the required date filter to the table before entering the formula.
Use SUMPRODUCT with SUBTOTAL and OFFSET for visible filtered rows
Use this formula when the date filter is already applied and the total must ignore rows hidden by the filter.
SUBTOTAL returns a result for each row through OFFSET, allowing SUMPRODUCT to multiply the relevant columns while excluding filtered-out rows. Replace the table and column names if your workbook uses different names.
Click the filter arrow in the table's Date column, select the required date or date range, and click OK.
Click an empty cell outside the table where you want the visible-row total to appear.
Type =SUMPRODUCT(Table1[Quantity],SUBTOTAL(9,OFFSET(Table1[Work Time],ROW(Table1[Work Time])-MIN(ROW(Table1[Work Time])),0,1))) and press Enter.
Change the Date filter and confirm that the result updates to include only the currently visible rows.
Calculate filtered visible-row totals in WPS Spreadsheet
WPS Spreadsheet can calculate a filtered table total with SUMPRODUCT, SUBTOTAL, and OFFSET. Apply a date filter, enter the formula, and keep the result synchronized with the rows currently visible in the table.
- 1. Open the spreadsheet: Launch WPS Office, open your workbook in WPS Spreadsheet, and locate the table containing the date, quantity, and work-time columns.
- 2. Apply the date filter: Select the filter arrow in the Date column, choose the date criteria, and click OK to display only the required records.
- 3. Enter the formula: In a blank result cell, enter =SUMPRODUCT(Table1[Quantity],SUBTOTAL(9,OFFSET(Table1[Work Time],ROW(Table1[Work Time])-MIN(ROW(Table1[Work Time])),0,1))) and press Enter.
- 4. Verify filtered results: Change or clear the Date filter and check that the calculated total changes with the visible table rows.
Frequently Asked Questions
Why does a regular SUMPRODUCT include filtered-out rows?
SUMPRODUCT normally evaluates every row in its referenced ranges, including rows hidden by an AutoFilter. Combining it with SUBTOTAL and OFFSET lets the formula identify and total only visible rows.
Which parts of the formula should I change for my table?
Replace Table1 with your table name, Quantity with the column containing the values to multiply, and Work Time with the numeric column used by SUBTOTAL to test row visibility.
How do I calculate the result for a specific date range?
Open the filter arrow in the Date column, choose Date Filters, select a condition such as Between, enter the start and end dates, and click OK before calculating the total.
Will the formula update when I change the table filter?
Yes. After the formula is entered, changing the table's date or other filters recalculates the SUMPRODUCT result for the rows that remain visible.
What if my data is not formatted as a table?
Convert the range to a table from the Table tab or use equivalent cell ranges in the formula. Table references are preferred because they expand automatically when new records are added.
