How to Show a Closing Balance Only for the Visible Filtered Party in WPS Spreadsheets
Question details
The user needs WPS Spreadsheets formulas that respond to filters so a closing balance appears only for the party currently visible or selected.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A party ledger or transaction table is filtered to a selected party, and the user wants a dynamic closing balance that reflects only those visible rows.
- Observed behavior
- Regular formulas continue to include hidden or filtered-out rows, so the closing balance does not match the currently visible party.
Confirm the party names and balance amounts are in a structured data range without blank header cells, and apply a filter from the Data tab in WPS Spreadsheets so you can verify the formula result.
Use a SUBTOTAL helper column to mark visible rows and calculate the visible party closing balance
Best for a clear, editable helper-column method that updates when the filter changes.
SUBTOTAL with function number 103 returns 1 for rows that remain visible under the active filter and 0 for rows hidden by the filter. Combine this helper column with SUMIFS to total the closing balance for the selected party, or with LOOKUP to return the last visible closing balance.
Click the column to the right of your data, right-click, and choose Insert. Name the new column Visible.
In the first data row of the helper column (for example D2), enter =SUBTOTAL(103,$A2) and drag the fill handle down through the last row.
In a summary cell, enter =SUMIFS($C$2:$C$100,$A$2:$A$100,G2,$D$2:$D$100,1) for a total balance. For a row-by-row closing balance, use =LOOKUP(2,1/(($A$2:$A$100=G2)*($D$2:$D$100=1)),$C$2:$C$100). Adjust column references to match your table.
Select the data range, click Data > Filter, and choose a party from the Party column filter. The summary cell now reflects only visible rows for that party.
Use AGGREGATE for a filtered visible total when the party filter is applied
Use this when your Balance column can be summed or you need error-tolerant totals for the rows currently visible after filtering.
Calculate a filtered party closing balance in WPS Spreadsheets
Use WPS Spreadsheets to build a filter-responsive closing balance with the same SUBTOTAL and AGGREGATE functions you use in Excel, so the result updates automatically for the visible party.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx or .xls workbook that contains the party transaction data.
- 2. Prepare the filtered table: Select the data range, click Data > Filter, and confirm the Party column filter is available.
- 3. Add the visible-row helper: Insert a Visible column and enter =SUBTOTAL(103,$A2), then fill down through all data rows.
- 4. Enter the closing balance formula: Use =SUMIFS($C$2:$C$100,$A$2:$A$100,G2,$D$2:$D$100,1) or the LOOKUP version for the last visible balance.
- 5. Verify the result: Filter to a specific party and confirm the summary cell changes to show only that party's visible closing balance.
Frequently Asked Questions
Why does a normal SUMIFS still include hidden rows in WPS Spreadsheets?
SUMIFS does not test row visibility. Add a helper column with =SUBTOTAL(103,$A2), which returns 1 for visible rows and 0 for hidden rows, then include that helper condition in the formula.
Which SUBTOTAL function number should I use for filtered rows?
Use 103 for COUNTA or 109 for SUM. Function numbers 101-111 ignore rows hidden by filters, whereas 1-11 do not.
Can I show the last visible closing balance instead of the total?
Yes. Create the Visible helper column and use =LOOKUP(2,1/(($A$2:$A$100=G2)*($D$2:$D$100=1)),$C$2:$C$100) to return the last matching visible balance.
Does AGGREGATE work in WPS Spreadsheets files saved as Excel?
Yes. WPS Spreadsheets supports AGGREGATE, and the workbook remains compatible with Microsoft Excel .xlsx and .xls formats.
Why does my closing balance show 0 after applying a filter?
Check that the helper formula uses 103, not 3, that the referenced helper column is copied through all rows, and that the selected party exactly matches the Party column values, including extra spaces.
