Use SUMPRODUCT and SUBTOTAL to Ignore Hidden Rows in Excel
Question details
Calculate the sum of products from two columns while excluding hidden rows.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains corresponding values in columns B and C, and the user wants a total that reflects only visible rows.
- Observed behavior
- A standard SUMPRODUCT formula includes values in hidden rows instead of limiting the calculation to visible rows.
Identify the matching ranges you want to multiply, such as B2:B10 and C2:C10. Make sure both ranges cover the same rows, and decide whether manually hidden rows should be excluded as well as filtered rows.
Combine SUMPRODUCT with SUBTOTAL to Exclude Hidden Rows
Use this formula to multiply values in two columns and sum only the visible rows.
SUBTOTAL with function number 109 excludes both manually hidden rows and rows hidden by a filter. OFFSET supplies each cell in column C to SUBTOTAL, and SUMPRODUCT multiplies the resulting values by column B.
In Excel, click an empty cell outside the ranges you want to calculate.
Type =SUMPRODUCT(B2:B10,SUBTOTAL(109,OFFSET(C2,ROW(C2:C10)-ROW(C2),0,1))) and press Enter.
In the formula bar, replace B2:B10 and C2:C10 with your matching ranges. Replace both single-cell references to C2 with the first cell of your second range.
Hide a row using Home > Format > Hide & Unhide > Hide Rows, or apply a filter to the data. Verify that the result excludes the hidden row.

Calculate Visible-Row Products in WPS Spreadsheets
Use the same SUMPRODUCT, SUBTOTAL, and OFFSET approach in WPS Spreadsheets to calculate products while excluding hidden and filtered rows.
- 1. Open your workbook: Open WPS Spreadsheets and use Open to select your workbook.
- 2. Choose a result cell: Click an empty cell outside the data ranges.
- 3. Enter the formula: Enter =SUMPRODUCT(B2:B10,SUBTOTAL(109,OFFSET(C2,ROW(C2:C10)-ROW(C2),0,1))) in the formula bar, adjusting the references to match your data.
- 4. Check visible rows: Press Enter, then hide or filter a data row and check that the result changes.

Frequently Asked Questions
Why does SUMPRODUCT include hidden rows?
SUMPRODUCT calculates using the values in its referenced ranges regardless of row visibility. Combine it with SUBTOTAL and OFFSET to exclude hidden rows.
What is the difference between SUBTOTAL 9 and 109?
Both use SUM and exclude rows hidden by a filter. Function number 109 also excludes manually hidden rows, while 9 includes them.
How do I adapt the formula for different columns?
Replace B2:B10 and C2:C10 with ranges covering the same rows. Also replace each C2 reference inside OFFSET and ROW with the first cell of your second range.
Will the formula work when I filter the data?
Yes. SUBTOTAL excludes filtered-out rows with either function number 9 or 109. Use 109 if manually hidden rows should also be excluded.




