To sum values with multiple criteria while ignoring rows hidden by a filter, use SUMPRODUCT combined with the SUBTOTAL function via OFFSET or a helper column. The most direct formula is: =SUMPRODUCT((range1=criteria1)*(range2=criteria2)*SUBTOTAL(9,OFFSET(first_cell,ROW(range)-MIN(ROW(range)),0))). This works because SUBTOTAL with function number 9 (SUM) returns 0 for hidden rows, and SUMPRODUCT multiplies that with your criteria.
Why SUMPRODUCT and SUBTOTAL Don’t Naturally Mix
Excel’s SUBTOTAL function is designed to ignore filtered-out rows, but it only works on a contiguous range. SUMPRODUCT evaluates arrays cell by cell, but it has no built-in awareness of which rows are hidden. When you filter a table, SUMPRODUCT still includes all rows in its calculation, even those hidden by the filter. To combine the two, you need a way to tell SUMPRODUCT which rows are visible. The standard trick is to create an array of SUBTOTAL results, one per row, using OFFSET to generate a single-cell reference for each row.
The Core Formula: How It Works
Assume your data is in rows 2 to 20, with column A containing categories, column B containing regions, and column C containing sales amounts. You want to sum sales where column A is “Widget” and column B is “North”, but only for rows visible after filtering.
Enter this formula in cell E1 (or any empty cell):
=SUMPRODUCT((A2:A20="Widget")*(B2:B20="North")*SUBTOTAL(9,OFFSET(C2,ROW(C2:C20)-MIN(ROW(C2:C20)),0)))
Here’s the breakdown:
(A2:A20="Widget")returns an array of TRUE/FALSE values, which Excel treats as 1/0.(B2:B20="North")does the same for the region.OFFSET(C2, ROW(C2:C20)-MIN(ROW(C2:C20)), 0)creates an array of references, each pointing to a single cell in C2:C20. For row 2, the offset is 0, so it references C2; for row 3, offset 1, referencing C3, and so on.SUBTOTAL(9, OFFSET(...))returns an array where each element is the sum of the single cell—but if that row is hidden,SUBTOTALreturns 0.- Multiplying the criteria arrays by the
SUBTOTALarray gives 1 only for rows that match both criteria and are visible. SUMPRODUCTthen sums the sales values for those rows.
In this example, if rows 3 and 5 are hidden and row 3 has a matching “Widget” and “North” with sales 100, and row 5 has 200, those are excluded. The result only includes visible matching rows.
Using a Helper Column for Simplicity
If the OFFSET formula seems complex, a helper column is a cleaner alternative. Add a column (say D) and enter this formula in D2, then copy down:
=SUBTOTAL(9, C2)
This returns the value in C2 if the row is visible, and 0 if hidden. Then use a simple SUMPRODUCT:
=SUMPRODUCT((A2:A20="Widget")*(B2:B20="North")*D2:D20)
This is easier to read and debug. The helper column approach is often preferred when you need the formula in multiple places or when you want to avoid volatile functions like OFFSET (which recalculates whenever the sheet changes).
Handling Blank Cells and Text Criteria
If your criteria cells can be blank, you need to handle that explicitly. For example, if you want to sum all rows where column A equals “Widget” and column B equals whatever is in cell F1 (which might be blank), use:
=SUMPRODUCT((A2:A20="Widget")*(B2:B20=F1)*SUBTOTAL(9,OFFSET(C2,ROW(C2:C20)-MIN(ROW(C2:C20)),0)))
But if F1 is blank, this will only match rows where B is also blank. If you want blank to mean “all”, you’d need an OR condition. Also, be careful with text that looks like numbers—Excel treats them as text, so use quotes around numeric-looking text in criteria.
Common Errors and How to Fix Them
One frequent issue is getting a #VALUE! error. This usually happens when the ranges in the SUMPRODUCT are not the same size. Ensure that all arrays have the same number of rows (e.g., A2:A20, B2:B20, C2:C20). Another common error is the formula returning 0 when you expect a number. This often occurs because the SUBTOTAL function with OFFSET is not returning the expected array—check that you used ROW(C2:C20)-MIN(ROW(C2:C20)) correctly and that your data starts in row 2. Also, if you have merged cells or hidden rows due to grouping (not filtering), SUBTOTAL may not behave as expected.
Another misconception: SUBTOTAL(9, ...) ignores rows hidden by a filter, but it does not ignore rows hidden manually (right-click > Hide). If you need to ignore manually hidden rows, you must use SUBTOTAL(109, ...) instead. The number 109 also sums, but it ignores both filtered and manually hidden rows.
Alternative Approaches: AGGREGATE and Dynamic Arrays
If you have Excel 365 or Excel 2021, you can use the FILTER function to get a dynamic array of visible matching rows, then sum them. First, you need a helper column that marks visibility, say column D with =SUBTOTAL(103, A2) (COUNTA) to return 1 if visible, 0 if hidden. Then:
=SUM(FILTER(C2:C20, (A2:A20="Widget")*(B2:B20="North")*(D2:D20=1)))
This is more readable and avoids the OFFSET volatility. Another option is AGGREGATE, but it doesn’t directly handle multiple criteria without array formulas. For older Excel versions, the SUMPRODUCT + SUBTOTAL approach is the standard.
Frequently Asked Questions
Can I use SUMPRODUCT with SUBTOTAL on a filtered table? Yes, but only with the OFFSET technique or a helper column. Directly nesting SUBTOTAL inside SUMPRODUCT without OFFSET won’t work because SUBTOTAL expects a range, not an array.
Why does my formula return 0 even when there are visible matches? Check that your criteria ranges are correctly referenced and that you’re using the right SUBTOTAL function number. Also, ensure that the OFFSET part references the correct column—if your data starts in row 1, adjust the MIN(ROW(...)) accordingly.
Does this work with Excel for Mac? Yes, the formulas are identical. The only difference is keyboard shortcuts for entering array formulas (if you use older CSE arrays), but SUMPRODUCT doesn’t require CSE, so it works the same.
Can I use wildcards in the criteria? Not directly in SUMPRODUCT. You’d need to use functions like ISNUMBER(SEARCH(...)) or LEFT to mimic wildcards. For example, to match any text starting with “Wid”, use (LEFT(A2:A20,3)="Wid").
Is there a performance difference between the OFFSET and helper column methods? The OFFSET method is volatile, meaning it recalculates every time any cell changes, which can slow down large workbooks. The helper column method is non-volatile and generally faster, especially with thousands of rows.
Practical Example with Steps
Let’s walk through a full example. Suppose you have a sales table in A1:C20. A1 is “Item”, B1 is “Region”, C1 is “Sales”. Data starts in row 2. You filter the table to show only “Widget” items and “North” region. You want the sum of visible sales.
- In cell E1, type the formula:
=SUMPRODUCT((A2:A20="Widget")*(B2:B20="North")*SUBTOTAL(9,OFFSET(C2,ROW(C2:C20)-MIN(ROW(C2:C20)),0))) - Press Enter. The result shows the sum of visible rows that match both criteria.
- To test, filter the table to hide some matching rows and watch the result update.
If you prefer the helper column, add a header in D1 as “Visible”, then in D2 enter =SUBTOTAL(9,C2) and copy down. Then in E1 use =SUMPRODUCT((A2:A20="Widget")*(B2:B20="North")*D2:D20).
Remember to save your workbook before experimenting with filters, as undo may not revert filter changes in all cases.
Related
- Excel Rank Values in Range: RANK, RANK.EQ, RANK.AVG
- Excel Print Merge File Showing X? Fixes
- Excel Profit and Loss Template: Free Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Compound Interest Calculator in Excel: Formulas and TipsYes. In Microsoft Excel for Microsoft 365 on Windows, enter the starting balance, annual interest rate, number of…
- LAMBDA in Excel: A Practical Guide to Custom FunctionsYes—Excel’s LAMBDA function lets you create reusable custom functions without writing VBA. For a quick test, with no…
- Replace Multiple Words in Excel: Methods and FormulasTo replace several different words in Excel, use Ctrl+H and run Find and Replace once for each word,…
- DATEDIF in Excel: Calculate Age From a Date of BirthTo calculate age from a date of birth in Excel, enter the birth date in A2 and use…
- Excel Formula for Workdays Between Dates: NETWORKDAYSUse Excel’s NETWORKDAYS function to count Monday-to-Friday workdays between two dates, optionally excluding holidays: =NETWORKDAYS(start_date,end_date,[holidays]). It counts both…
- How to Add IFERROR in Excel to Handle Formula ErrorsTo add IFERROR in Excel, wrap your existing formula inside IFERROR and specify what to show if that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.