WPS Spreadsheet: Create Dynamic Monthly Subtotals with FILTER, BYCOL, and LAMBDA
Question details
The user needs a dynamic subtotal formula that filters matching rows and calculates totals across monthly columns.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains criteria in columns A and B, monthly values beginning in column D, and a helper spill array that determines the range width. The subtotal should expand automatically without VBA or volatile functions.
- Observed behavior
- The desired subtotal must include only rows above the current subtotal row, match both criteria, and spill one total for each monthly column without creating a circular reference.
Identify the helper spill range, such as D7#, and confirm which columns contain the two criteria and which columns contain monthly values. Make sure the source range ends on the row above the subtotal formula so the formula does not reference itself.
Build a dynamic subtotal with LET, FILTER, DROP, CHOOSECOLS, and BYCOL
Use this approach when the monthly range should expand with a helper spill array and the criteria columns may change position.
The formula first creates an array limited to the rows above the subtotal, removes the criteria columns from the data, filters rows matching both criteria, and then sums each remaining column with BYCOL. It avoids volatile functions such as OFFSET and INDIRECT.
Replace the row and cell references with the locations in your worksheet. TEXTBEFORE extracts the criterion from labels such as "North Subtotal".
Click the first cell in the subtotal row, such as D10, and confirm that the source range will stop at the row above it.
Enter =LET(arr,DROP($D$7#:A10,1),data,FILTER(DROP(arr,,COLUMNS(arr)-COLUMNS($D$7#)),(CHOOSECOLS(arr,1)=A10)*(CHOOSECOLS(arr,2)=TEXTBEFORE(B10," Subtotal"))),BYCOL(data,LAMBDA(c,SUM(c)))) and press Enter.
Review the spilled results across the monthly columns. Each result should total rows whose first criterion matches A10 and whose second criterion matches the text before " Subtotal" in B10.
Modify $D$7#, A10, B10, and the starting row in DROP to match your sheet. Keep the source range above the subtotal row to prevent a circular reference.
Use a fixed monthly range with BYCOL and FILTER
Use this shorter option when the criteria and monthly columns have a stable layout and the helper array width does not need to be calculated dynamically.
Create Expandable Subtotals in WPS Spreadsheet Without VBA
WPS Spreadsheet can use dynamic-array formulas to filter matching records and calculate monthly subtotals across a changing helper-array range. The familiar formula bar and worksheet interface make it easy to enter, adjust, and verify LET, FILTER, and BYCOL formulas.
- 1. Open the workbook: Launch WPS Office, open the spreadsheet, and locate the helper spill array and subtotal row.
- 2. Select the output cell: Click the first cell of the subtotal row and ensure the source range includes only rows above that cell.
- 3. Enter the formula: Paste the LET, FILTER, CHOOSECOLS, and BYCOL formula into the formula bar, then press Enter.
- 4. Review and customize: Check the spilled monthly totals and update the criteria references, helper-array reference, or starting row as needed.
Frequently Asked Questions
Why must the subtotal formula exclude its own row?
Including the current subtotal row makes the formula reference its own result, which creates a circular reference. Limit the source range to the rows above the subtotal, such as D8:...:9 when the subtotal is on row 10.
How does the formula determine which rows to subtotal?
FILTER keeps rows where the first criteria column equals the value in A10 and the second criteria column equals the text before " Subtotal" in B10. The multiplication operator combines both conditions as an AND test.
How can I make the formula follow inserted or deleted columns?
Use COLUMNS(arr)-COLUMNS($D$7#) as the second DROP argument so the criteria columns are removed based on the current array width rather than a fixed number.
What does BYCOL do in this subtotal formula?
BYCOL processes each remaining monthly column separately. Its LAMBDA function applies SUM to each column and returns one subtotal per month.
Can this formula replace VBA macros?
Yes, when the WPS Spreadsheet version supports the required dynamic-array functions. LET, DROP, FILTER, CHOOSECOLS, BYCOL, and LAMBDA perform the filtering and column-by-column totals without VBA or volatile functions.
