Excel Formula to Multiply Only Filtered Rows Using SUBTOTAL and SUMPRODUCT

Excel users often struggle to multiply filtered rows, as standard formulas mistakenly include hidden data. While basic SUBTOTAL functions easily sum or average visible rows, they fail when calculating products across filtered datasets. Resolving this grants you precise, dynamic calculations that update automatically based on your active filters.

Under the stipulation that this approach requires combining SUMPRODUCT with an OFFSET array, you can seamlessly multiply variables like Unit Price by Quantity. Below, we will explore the exact formula construct and step-by-step implementation guide to master this technique.

When working with large datasets in Excel, filtering data is one of the most common ways to isolate specific information. However, performing calculations on only those filtered (visible) rows can quickly become a headache. While functions like SUM, AVERAGE, and COUNT have direct counterparts in the versatile SUBTOTAL function, performing multiplication exclusively on filtered rows requires a bit more ingenuity.

Whether you need to find the product of a single column of filtered numbers or calculate the sum of products (such as Quantity × Unit Price) for only the visible rows, this guide will walk you through the exact formulas, logic, and modern techniques to achieve this seamlessly.

The Core Mechanism: Understanding the SUBTOTAL Function

To perform calculations on filtered rows, we must understand the SUBTOTAL function. Unlike standard functions, SUBTOTAL is designed to ignore rows that have been hidden by a filter.

The syntax for SUBTOTAL is:

=SUBTOTAL(function_num, ref1, [ref2], ...)

The function_num argument determines which mathematical operation to perform and how to handle hidden cells:

• Function numbers 1-11: Include manually hidden rows but ignore rows hidden by filters.
• Function numbers 101-111: Ignore both manually hidden rows and rows hidden by filters (highly recommended for clean reporting).

The Hidden Gem: Function Number 6 (PRODUCT)

Many users do not realize that SUBTOTAL actually has a built-in multiplication feature. Function number 6 (or 106) corresponds to PRODUCT. If you simply want to multiply a single column of numbers together and have that product update dynamically when you filter your table, you can use:

=SUBTOTAL(106, B2:B20)

This formula will exclusively multiply the visible cells in the range B2:B20, ignoring any rows filtered out by Excel's autofilter interface.

The Real Challenge: Row-by-Row Multiplication (SUMPRODUCT with Filtered Rows)

While multiplying a single column is straightforward, the business scenario is often more complex. Usually, you need to multiply values across columns on a row-by-row basis (e.g., Quantity * Unit Price) and then sum those results-but only for the visible rows.

A standard =SUMPRODUCT(B2:B10, C2:C10) will calculate the sum of products for all rows, completely ignoring your active filters. To bypass this, we must build a dynamic array formula that flags visible rows with a 1 and filtered rows with a 0.

The Classic Formula (Excel 2013 and Later)

To sum the products of filtered rows exclusively, use the following robust formula:

=SUMPRODUCT(B2:B10, C2:C10, SUBTOTAL(3, OFFSET(B2, ROW(B2:B10)-ROW(B2), 0)))

How This Formula Works (Deconstructed)

This formula is elegant because it forces SUBTOTAL to evaluate each row individually rather than as a single block. Here is the step-by-step breakdown:

1. ROW(B2:B10)-ROW(B2): This creates an array of sequential numbers starting from 0: {0; 1; 2; 3; 4; 5; 6; 7; 8}.
2. OFFSET(B2, {0; 1; 2; ...}, 0): This creates an array of individual cell references: {B2; B3; B4; ...; B10}.
3. SUBTOTAL(3, ...): Function number 3 is COUNTA. When applied to each individual cell reference generated by the OFFSET array, it returns 1 if the cell is visible and 0 if it is hidden or filtered out. This produces a virtual mask like {1; 0; 1; 1; 0; ...}.
4. SUMPRODUCT(B2:B10, C2:C10, {1; 0; 1; ...}): Finally, SUMPRODUCT multiplies the corresponding elements of your columns. Because the hidden rows are multiplied by 0 (courtesy of the SUBTOTAL mask), their values are negated, leaving you with the sum of products for only the visible rows.

The Modern Excel Way: Using FILTER and MAP (Excel 365)

If you are using Microsoft 365 or Excel 2021, you can avoid complex, volatile functions like OFFSET. Volatile functions recalculate every time any change is made to the worksheet, which can slow down large workbooks. Modern Excel allows for cleaner, highly performant formulas.

Method A: The Dynamic Helper Array

By using the new BYROW or MAP functions combined with LAMBDA, you can cleanly target visible rows:

=SUM(MAP(B2:B10, C2:C10, LAMBDA(qty, price, qty * price * SUBTOTAL(103, qty))))

In this formula, MAP loops through each row of the dataset. For each row, it multiplies the quantity by the price, and then multiplies that by SUBTOTAL(103, qty). If the row is filtered out, SUBTOTAL returns 0, cleanly eliminating that row's product from the final SUM.

The Best Practice: The Helper Column Approach

While mega-formulas are impressive, they can be difficult for other users to audit or maintain. If you are working on a collaborative sheet or a massive dataset, the Helper Column Method is the industry-standard best practice for both performance and simplicity.

Step 1: Create a Visibility Flag

Add a new column to your table, typically named "Visible". In the first data row of this column (for example, cell D2), enter the following formula:

=SUBTOTAL(103, A2)

Note: Point the formula to a column (like Column A) that is guaranteed to always have data in it (like an ID or Name column). Drag this formula down to the bottom of your dataset.

When you filter your data, the visible rows will display a 1 in this column, while hidden rows will display 0.

Step 2: Write a Simple SUMPRODUCT Formula

Now, your calculation formula is incredibly clean, fast, and easy for any user to understand:

=SUMPRODUCT(B2:B10, C2:C10, D2:D10)

Because the "Visible" column (Column D) acts as a binary multiplier (1 or 0), it automatically handles the filtering logic without taxing Excel's calculation engine with volatile array formulas.

Summary: Choosing the Right Method

Conclusion

Excel's SUBTOTAL function is incredibly powerful when paired with filtering. If you simply need to multiply a range of visible cells, SUBTOTAL(106, range) is your quick fix. For more complex calculations like summing products of filtered rows, choosing between the legacy SUMPRODUCT+OFFSET trick, the modern MAP/LAMBDA functions, or the highly efficient helper column method will depend entirely on your version of Excel and the size of your dataset.

By implementing these strategies, you can build dynamic, interactive dashboards and reports that recalculate flawlessly every time a user changes a filter.