How to Sum Filtered Excel Table Values Selected by a Slicer in WPS Office Spreadsheets
Question details
The user needs to total only the rows that remain visible after a slicer filters an Excel table, without using SUMIF with the slicer's selected value.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user applied a slicer to an Excel table and wants a total that updates dynamically as the slicer selection changes.
- Observed behavior
- A regular SUM does not respond to slicer visibility, and SUMIF requires using the slicer selected value as a criterion; the goal is a total based only on visible rows.
Make sure the values are in a formatted Excel table and that the slicer is connected to the same table you want to total. Place the total formula outside the table so it remains visible while filtering.
Use SUBTOTAL to Sum Visible Values After Slicer Filtering
Use this solution when you want a total that automatically updates after slicer filtering without needing SUMIF or the slicer's selected value.
SUBTOTAL calculates a sum on the table column and ignores rows hidden by filtering, including rows hidden by a slicer. It works directly with the table column, so you do not need to add the slicer selection as a separate condition.
Click an empty cell outside the table, such as below the values column where the total should appear.
Type =SUBTOTAL(9,Table1[Total]) and press Enter. Replace Table1 with your table name and Total with your value column name.
Change the slicer selection and confirm the result updates to sum only the currently visible rows.
Sum Slicer-Filtered Excel Tables in WPS Spreadsheets
WPS Spreadsheets supports Excel tables, slicers, and SUBTOTAL formulas, so you can sum only the rows currently visible after slicer filtering without changing your workbook.
- 1. Open the workbook: Open the Excel workbook in WPS Spreadsheets.
- 2. Confirm the Excel table: Select the data range, click Insert > Table, and confirm the table range and headers.
- 3. Apply a slicer: Use Insert > Slicer to connect a slicer to the table field you want to filter.
- 4. Enter the SUBTOTAL formula: Click a cell outside the table and type =SUBTOTAL(9,Table1[Total]), then press Enter.
- 5. Test the result: Change the slicer selection to confirm the sum updates to visible rows only.
Frequently Asked Questions
Why does SUBTOTAL(9,Table1[Total]) ignore slicer-hidden rows?
Function number 9 performs a sum and ignores rows hidden by filtering. Because a slicer filters the table, SUBTOTAL automatically includes only the visible rows in the result.
Do I need to update the formula when I change the slicer?
No. SUBTOTAL recalculates automatically when the slicer selection changes, so the total stays in sync with the visible rows.
What if my table is not named Table1?
Replace Table1 with your actual table name. You can see the table name by selecting the table and checking the Table Design or Table Tools tab in WPS Spreadsheets.
Can I use a column name with spaces in the SUBTOTAL formula?
Yes. Use the structured reference with brackets, for example =SUBTOTAL(9,Table1[Order Total]), where Order Total is the exact column header name.
What if I manually hide rows as well as filtering?
For slicer or filter-hidden rows, SUBTOTAL(9,...) works. If you also manually hide rows and want them excluded, use =SUBTOTAL(109,Table1[Total]) instead.
