WPS Spreadsheet SUMPRODUCT for Visible Rows with Text Criteria
Question details
The user needs a SUMPRODUCT formula that totals amounts only from visible rows while matching an account name or other text criterion.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A filtered table contains account names and USD amounts, and the user wants to calculate a total for visible rows matching the account selected in D12.
- Observed behavior
- A standard SUMPRODUCT calculation does not automatically exclude filtered or hidden rows, so the formula must combine SUBTOTAL and OFFSET with the text-matching condition.
Confirm that the data is stored in a table named DDProposalFinal with Account, Vendor, and USD Amount columns. Apply the filter or hide the rows you want excluded, then enter the account criterion in cell D12.
Use SUMPRODUCT with SUBTOTAL and OFFSET for one account
Use this formula when one account in D12 should be matched and only visible table rows should contribute to the total.
SUBTOTAL checks whether each row is visible, while OFFSET evaluates the rows individually. The account comparison then limits the total to the selected text criterion.
Click the cell where you want the visible-row total to appear.
Type =SUMPRODUCT(SUBTOTAL(3,OFFSET(DDProposalFinal[Vendor],ROW(DDProposalFinal[Vendor])-MIN(ROW(DDProposalFinal[Vendor])),,1)),(DDProposalFinal[Account]=D12)*DDProposalFinal[USD Amount]) and press Enter.
Use the filter arrows in the DDProposalFinal table to hide rows. The result updates to include only visible rows whose Account value matches D12.
Match several accounts in Microsoft 365 with a dynamic formula
Use this option in Microsoft 365 when the criterion can be any account listed in D12:D14 and the required dynamic-array functions are available.
Calculate filtered visible-row totals in WPS Spreadsheet
WPS Spreadsheet can calculate a total for filtered rows by combining SUMPRODUCT with SUBTOTAL and OFFSET, then applying an account or text comparison. Use the compatible single-account formula for a reliable result in the selected cell.
- 1. Open the workbook: Launch WPS Office, open the workbook in WPS Spreadsheet, and locate the DDProposalFinal table.
- 2. Prepare the filter and criterion: Filter the table as needed and enter the account name to match in cell D12.
- 3. Insert the visible-row formula: Select an output cell and enter =SUMPRODUCT(SUBTOTAL(3,OFFSET(DDProposalFinal[Vendor],ROW(DDProposalFinal[Vendor])-MIN(ROW(DDProposalFinal[Vendor])),,1)),(DDProposalFinal[Account]=D12)*DDProposalFinal[USD Amount]).
- 4. Review the result: Press Enter, then change D12 or the table filters to confirm that the total updates for matching visible rows.

Frequently Asked Questions
Why does SUMPRODUCT need SUBTOTAL for filtered rows?
SUMPRODUCT alone includes hidden or filtered rows. SUBTOTAL evaluates each row's visibility, allowing the formula to include only rows that remain visible.
Which cell contains the text criterion in the single-account formula?
The formula compares DDProposalFinal[Account] with D12. Enter the account name you want to match in D12, or replace D12 with another cell reference.
What does the Vendor column do in the SUBTOTAL portion?
The Vendor column provides a nonblank row reference for SUBTOTAL. If Vendor can contain blanks, use another table column that is populated for every data row.
Can I match several account names at once?
In Microsoft 365, use the LET, LAMBDA, and MAP formula with the account list in D12:D14. If those functions are unavailable, run the single-account formula for each account or combine separate results.
Will the formula update when I change the table filter?
Yes. After the formula is entered, filtering or unfiltering DDProposalFinal changes which rows SUBTOTAL identifies as visible, so the total recalculates automatically.




