Function Problems

Use SUMPRODUCT and SUBTOTAL to Ignore Hidden Rows in Excel

Nimra MalikNimra Malik Nov 20, 2026 868 views

Question details

Calculate the sum of products from two columns while excluding hidden rows.

Use SUMPRODUCT and SUBTOTAL to Ignore Hidden Rows in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

In Excel, click an empty cell outside the ranges you want to calculate.

2
Enter the formula

Type =SUMPRODUCT(B2:B10,SUBTOTAL(109,OFFSET(C2,ROW(C2:C10)-ROW(C2),0,1))) and press Enter.

3
Adjust the ranges

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.

4
Check the result

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.

Combine SUMPRODUCT with SUBTOTAL to Exclude Hidden Rows
Choose the correct SUBTOTAL number: Use 109 to exclude manually hidden and filtered rows. Use 9 instead if you want to include manually hidden rows while still excluding filtered rows.
Work with visible rows in WPS

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. 1. Open your workbook: Open WPS Spreadsheets and use Open to select your workbook.
  2. 2. Choose a result cell: Click an empty cell outside the data ranges.
  3. 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. 4. Check visible rows: Press Enter, then hide or filter a data row and check that the result changes.
Calculate a sum of products from visible rowsOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and controls
microsoft office alternative - wps office

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.