How to Sum Filtered or Noncontiguous Cells in WPS Spreadsheets
Question details
The user needs a spreadsheet total that includes only currently filtered or noncontiguous cells, excluding hidden rows that a normal SUM range would include.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Calculating totals after applying an AutoFilter or selecting nonadjacent cells in a worksheet.
- Observed behavior
- A standard SUM formula includes hidden or unintended cells when the filter changes, so the total does not match the visible rows.
Open the workbook in WPS Spreadsheets and confirm whether the data uses AutoFilter, manual hidden rows, or a one-time nonadjacent selection. This determines whether SUBTOTAL, AGGREGATE, or a manual SUM formula is the best fit.
Use SUBTOTAL to sum visible cells after filtering
Use this solution when you need the total to update automatically as you change AutoFilter criteria.
SUBTOTAL is the safest built-in way to total only rows currently visible in a filtered list. Function number 9 sums, and the function automatically excludes rows hidden by an AutoFilter.
Click the cell where the total should appear, for example F2, in your WPS Spreadsheets worksheet.
Select your data range, click Home > AutoFilter, and choose a filter value from a column header.
Type =SUBTOTAL(9,A2:A100) in the result cell and press Enter. Replace A2:A100 with your actual data range.
Change the filter criteria and confirm that the total recalculates using only visible rows.
Sum visible cells with AGGREGATE and ignore error values
Use this when the visible range may contain error values and you want the total to ignore both hidden rows and error values.
Sum noncontiguous cells with a manual SUM formula
Use this when you want a one-time sum of a few nonadjacent cells or ranges and do not need it to respond to filters.
Sum Filtered and Noncontiguous Ranges in WPS Spreadsheets
Use WPS Spreadsheets' built-in SUBTOTAL and AGGREGATE functions to total visible rows or nonadjacent selections. WPS Office opens and saves Microsoft Excel .xlsx and .xls files, so you can work with the same formulas and formatting in a free, lightweight suite.
- 1. Open your workbook: Launch WPS Spreadsheets and open the .xlsx or .xls file containing the data you need to sum.
- 2. Select the result cell: Click the cell where the total should appear, such as below the filtered column.
- 3. Enter a visible-cell total formula: Type =SUBTOTAL(9,A2:A100) or =AGGREGATE(9,7,A2:A100) and press Enter.
- 4. Apply filters or select noncontiguous cells: Use Home > AutoFilter to filter the list, or start =SUM( and hold Ctrl to select nonadjacent ranges.
- 5. Save your workbook: Click File > Save As and keep the Excel-compatible .xlsx format if you need to share it with Microsoft Excel users.
Frequently Asked Questions
Why does my SUM formula include hidden rows after I filter data?
Because SUM adds every cell in the referenced range regardless of visibility. Use =SUBTOTAL(9,A2:A100) to sum only rows visible after an AutoFilter, or =SUBTOTAL(109,A2:A100) to also exclude manually hidden rows.
How do I sum only visible cells after filtering in WPS Spreadsheets?
Click the result cell, type =SUBTOTAL(9,A2:A100), and press Enter. The formula recalculates whenever you change the AutoFilter.
Can I sum nonadjacent cells without writing a VBA macro?
Yes. Click the result cell, type =SUM(, then hold Ctrl and select each cell or range you want to add. WPS Spreadsheets inserts the comma separators automatically.
What is the difference between SUBTOTAL 9 and SUBTOTAL 109?
Function number 9 ignores rows hidden by AutoFilter. Function number 109 additionally ignores rows you hide manually with the Hide command.
Does WPS Spreadsheets support the Excel FILTER function?
Availability may vary by version. For a reliable filtered total, use SUBTOTAL or AGGREGATE instead; they work in WPS Spreadsheets and Excel files.
