Function Problems

How to Use Excel SUBTOTAL Formula for Filtered Rows with Multiple Criteria

Natalie Taylor Oct 1, 2026 868 views

Question details

Calculate the sum of filtered, visible rows based on a specific condition or multiple criteria.

How to Use Excel SUBTOTAL Formula for Filtered Rows with Multiple Criteria

Product: Excel
Device & OS: not provided
Scenario: Attempting to sum values in a dataset where filters are applied and specific text/status conditions must also be met.
Observed behavior: The standard SUMIFS function does not ignore hidden or filtered rows, meaning it calculates totals for all data instead of just the visible rows.

Before you start

Verify the exact cell ranges for both your numeric values and your criteria status, ensuring there are no completely blank rows disrupting your data range.

Solution 1Recommended

Use SUMPRODUCT Combined with SUBTOTAL and OFFSET

Create an array formula that checks the visibility of each row individually before applying your specific condition.

Because SUMIFS cannot distinguish between hidden and visible rows, you must use a combination of functions. By nesting SUBTOTAL inside OFFSET, Excel evaluates each row one by one. The SUMPRODUCT function then multiplies the visible rows by your specific criteria to return the correct total.

Identify your data ranges

Determine the range containing the numbers you want to sum (e.g., I6:I33) and the range containing the criteria you want to check (e.g., O6:O33).

Input the combined formula

Select the cell where you want the result to appear and enter the formula: =IFERROR(SUMPRODUCT(SUBTOTAL(9,OFFSET(I6,ROW(I6:I33)-ROW(I6),0)),--(O6:O33="Active")),"Error")

Customize the criteria

Replace the word "Active" in the formula with the specific text, number, or cell reference that matches your required condition. Press Enter to calculate.

Use SUMPRODUCT Combined with SUBTOTAL and OFFSET

Understanding SUBTOTAL Function Numbers: The number 9 in the SUBTOTAL function specifies the SUM operation. It ignores rows hidden by a filter. If you want to also ignore rows that have been manually hidden via right-click, change the 9 to 109.

Advanced Formula Support

Calculate Complex Formulas Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, including the SUMPRODUCT, SUBTOTAL, and OFFSET combinations. You can easily analyze filtered data with multiple conditions without losing performance.

1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the data you want to analyze.
2. Apply data filters: Navigate to the Data tab and click 'Filter' to apply dropdowns to your column headers, then filter the rows as needed.
3. Enter the formula: Type the combined SUMPRODUCT and SUBTOTAL formula into your target summary cell, updating the ranges to match your sheet.
4. Calculate the result: Press Enter. WPS Spreadsheet will instantly calculate the sum for only the visible rows that match your criteria.

100% compatible with Microsoft Excel formulas, functions, and formattingProcesses complex array calculations with high speed and stabilityFree built-in data filtering, sorting, and pivot table tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMIFS include hidden or filtered rows?

The SUMIFS function evaluates the underlying data range structurally. It does not have the built-in capability to check the visual display property (hidden or visible) of a row, which is why it includes all data in its calculation.

Can I add more conditions to this formula?

Yes. You can add additional criteria by multiplying more arrays within the SUMPRODUCT function. Just add another set of parentheses with the double negative, like this: --(Range2="Criteria2").

What does the double negative (--) do in the formula?

The double negative (called a double unary operator) coerces TRUE and FALSE boolean values into 1s and 0s. This allows the SUMPRODUCT function to mathematically multiply and sum the arrays.

Why is the OFFSET function necessary here?

Normally, SUBTOTAL evaluates an entire range as a single block. By using OFFSET combined with ROW, you force SUBTOTAL to evaluate an array of single-cell references, allowing it to determine the visible status of each row individually.

Excel & Data Problems