Function Problems

WPS Spreadsheet SUMPRODUCT for Visible Rows with Text Criteria

Huma Ashraf ChHuma Ashraf Ch Nov 12, 2026 869 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click the cell where you want the visible-row total to appear.

2
Enter the formula

Type =SUMPRODUCT(SUBTOTAL(3,OFFSET(DDProposalFinal[Vendor],ROW(DDProposalFinal[Vendor])-MIN(ROW(DDProposalFinal[Vendor])),,1)),(DDProposalFinal[Account]=D12)*DDProposalFinal[USD Amount]) and press Enter.

3
Filter the table

Use the filter arrows in the DDProposalFinal table to hide rows. The result updates to include only visible rows whose Account value matches D12.

Result: Change the value in D12 or adjust the table filter to recalculate the matching visible-row total.

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. 1. Open the workbook: Launch WPS Office, open the workbook in WPS Spreadsheet, and locate the DDProposalFinal table.
  2. 2. Prepare the filter and criterion: Filter the table as needed and enter the account name to match in cell D12.
  3. 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. 4. Review the result: Press Enter, then change D12 or the table filters to confirm that the total updates for matching visible rows.
Totals only the visible rows that remain after filteringMatches account names or other text criteria such as the value in D12Works with familiar spreadsheet formulas and table column referencesSupports Microsoft Excel XLSX files and common formula workflows
microsoft office alternative - wps office

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.