How to Calculate Visible-Row Subtotals for Rate Multiplied by Quantity in Excel
Question details
The user needs to calculate the total sum of a Rate column multiplied by a Quantity column, but strictly for visible (unfiltered or unhidden) rows.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating accurate cost subtotals in a filtered dataset where direct multiplication (Rate x Quantity) is required, but hidden or filtered rows must be excluded from the final sum.
- Observed behavior
- A standard SUMPRODUCT formula includes both hidden and visible rows in its calculation, requiring a specific combination of array functions to restrict the calculation to visible rows only.
Ensure your data ranges for Rate, Quantity, and the reference column are of equal length, and verify which columns contain the data you intend to multiply.
Use SUMPRODUCT, SUBTOTAL, and OFFSET for Visible Rows
Combine SUMPRODUCT with SUBTOTAL and OFFSET to dynamically multiply corresponding rows while identifying and excluding hidden ones.
By nesting SUBTOTAL and OFFSET inside SUMPRODUCT, Excel can evaluate the visibility of each row individually. SUBTOTAL with argument 103 returns 1 for visible rows and 0 for hidden rows, acting as a dynamic filter inside the SUMPRODUCT array.
Click on the cell where you want the visible-row subtotal to be displayed.
Type the formula: =SUMPRODUCT(B2:B5*C2:C5*SUBTOTAL(103,OFFSET(A2,ROW(A2:A5)-MIN(ROW(A2:A5)),0)))
Change B2:B5 to match your Rate column, C2:C5 to match your Quantity column, and A2:A5 to an adjacent column that always contains data (used to check row visibility).
Press the Enter key. If you apply a filter to your data table, the total will automatically recalculate to include only the visible rows.

Calculate the Total for All Rows (Ignoring Filters)
If you need the total for all data regardless of filtering or visibility, use the standard SUMPRODUCT function.
Easily Calculate Advanced Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports complex array formulas, including SUMPRODUCT, SUBTOTAL, and OFFSET. You can seamlessly calculate visible-row subtotals, filter data, and manage large datasets with high performance.
- 1. Open your data in WPS: Launch WPS Spreadsheet and open your .xlsx file containing the rate and quantity columns.
- 2. Apply data filters: Navigate to the Data tab and click 'Filter' to set up visibility conditions for your rows.
- 3. Input the formula: Select your total cell and paste the combined SUMPRODUCT and SUBTOTAL formula.
- 4. View dynamic totals: Press Enter. Filter your data as needed, and WPS Spreadsheet will instantly update the total to reflect only the visible rows.

Frequently Asked Questions
Why use OFFSET with SUBTOTAL in this formula?
OFFSET is used to feed SUBTOTAL an array of individual cell references instead of a single continuous range. This forces SUBTOTAL to evaluate the visibility of each row one by one, returning an array of 1s (visible) and 0s (hidden) that SUMPRODUCT uses to nullify hidden row values.
Can I use this formula for multiplying more than two columns?
Yes. You can expand the SUMPRODUCT function to include additional columns. For instance, if you have a Discount column in D, you can modify the formula to =SUMPRODUCT(B2:B5*C2:C5*D2:D5*SUBTOTAL(...)).
Will this formula ignore both manually hidden rows and filtered rows?
Yes, because the SUBTOTAL function uses the argument 103 (COUNTA ignoring hidden values), it will correctly exclude both rows hidden by a data filter and rows that have been manually hidden via the right-click menu.




