Calculation Issues

How to Calculate Visible-Row Subtotals for Rate Multiplied by Quantity in Excel

John WilsonJohn Wilson Nov 3, 2026 872 views

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.

How to Calculate Visible-Row Subtotals for Rate Multiplied by Quantity
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click on the cell where you want the visible-row subtotal to be displayed.

2
Enter the formula

Type the formula: =SUMPRODUCT(B2:B5*C2:C5*SUBTOTAL(103,OFFSET(A2,ROW(A2:A5)-MIN(ROW(A2:A5)),0)))

3
Adjust the cell references

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).

4
Calculate the result

Press the Enter key. If you apply a filter to your data table, the total will automatically recalculate to include only the visible rows.

Use SUMPRODUCT, SUBTOTAL, and OFFSET for Visible Rows
Understanding the SUBTOTAL argument: The '103' in the SUBTOTAL function corresponds to COUNTA, specifically configured to ignore manually hidden rows and rows hidden by filters.
Powerful Spreadsheet Tool

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. 1. Open your data in WPS: Launch WPS Spreadsheet and open your .xlsx file containing the rate and quantity columns.
  2. 2. Apply data filters: Navigate to the Data tab and click 'Filter' to set up visibility conditions for your rows.
  3. 3. Input the formula: Select your total cell and paste the combined SUMPRODUCT and SUBTOTAL formula.
  4. 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.
Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in advanced functions for complex data analysis and conditional calculations.Intuitive Data tab interface for applying filters and viewing subtotals instantly.Free, lightweight, and fast alternative for everyday spreadsheet tasks.
microsoft office alternative - wps office

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.