Combining SUBTOTAL and SUMIF lets you sum values that meet a condition while ignoring rows hidden by a filter. The trick is that SUBTOTAL alone cannot apply criteria, and SUMIF alone ignores filters. By using SUBTOTAL with the OFFSET function to create a range that only includes visible rows, you can get a conditional sum that updates when you filter your data. This guide shows you the exact formula and how to set it up step by step.
What You’ll Need
- Microsoft Excel (the steps work in Excel 2010 and newer, including Microsoft 365).
- A dataset with at least two columns: one for the condition (e.g., region) and one for the values to sum (e.g., sales).
- Your data should be in a contiguous range with headers in row 1 (recommended but not required).
- Basic familiarity with entering formulas in Excel.
Step-by-Step
- Prepare your data. For this example, put regions in column A (A2:A20) and sales amounts in column B (B2:B20). Leave row 1 for headers like “Region” and “Sales”.
- Understand the core formula. The standard way to sum visible rows that meet a condition is:
=SUMPRODUCT(SUBTOTAL(109,OFFSET(B2:B20,ROW(B2:B20)-MIN(ROW(B2:B20)),0,1)),--(A2:A20="West"))This works by creating a separate range for each row, checking if that row is visible with
SUBTOTAL(109,...), then multiplying by the condition. The--converts TRUE/FALSE to 1/0. - Enter the formula. Click on a cell outside your data, say D2. Type the formula above, but adjust the ranges to match your data. For our example, with regions in A2:A20 and sales in B2:B20, the formula is exactly as shown. Press Enter.
- Test it without filters. Before filtering, the formula should return the same as a regular
SUMIF: for “West”, if the sales are 100, 200, and 300, the result is 600. - Apply a filter. Click anywhere in your data, go to the Data tab, and click Filter. Use the dropdown arrow in column A to uncheck some regions, leaving only rows you want to see. The formula will now sum only the visible rows that also match “West”.
- Use with multiple criteria. If you need more than one condition, multiply additional
--(range=criteria)terms inside the SUMPRODUCT. For example, to sum sales where region is “West” and product is “Widget”:=SUMPRODUCT(SUBTOTAL(109,OFFSET(B2:B20,ROW(B2:B20)-MIN(ROW(B2:B20)),0,1)),--(A2:A20="West"),--(C2:C20="Widget"))This assumes product names are in column C.
- Alternative with SUMPRODUCT and SUBTOTAL only. If you don’t need a condition, you can simply use
=SUBTOTAL(109,B2:B20)to sum visible rows. But for conditional sums, the OFFSET method is the standard solution. - For Excel for the web or Mac: The same formula works, but ensure you use commas as argument separators (not semicolons). On Mac, the keyboard shortcut for filtering is Cmd+Shift+L instead of Ctrl+Shift+L.
Common Mistakes
- Using SUBTOTAL with SUMIF directly. For example,
=SUMIF(A2:A20,"West",SUBTOTAL(109,B2:B20))is invalid because SUMIF expects a range, not a single value. - Forgetting to adjust the OFFSET height. The
OFFSETfunction must return a single cell for each row. UsingOFFSET(B2:B20,ROW(B2:B20)-MIN(ROW(B2:B20)),0,1)is correct; changing the height to something else breaks the logic. - Not using double unary (–). If you omit the
--, the TRUE/FALSE values won’t multiply correctly, and you’ll get a #VALUE! error. - Using SUBTOTAL function number 9 instead of 109. 9 includes hidden rows, 109 ignores them. You must use 109 to respect filtering.
- Filtering by rows that are manually hidden. SUBTOTAL(109) ignores rows hidden by filtering, but it also ignores rows hidden manually (right-click > Hide). If you only want to ignore filter-hidden rows, you need a more complex approach, but for most cases 109 is fine.
Tips
- Use a helper column for simplicity. If you find the OFFSET formula too complex, add a helper column that marks each row as visible or not. For example, in column D, enter
=SUBTOTAL(103,A2)which returns 1 if the row is visible, 0 if hidden. Then use=SUMIFS(B2:B20,A2:A20,"West",D2:D20,1). This is easier to read and maintain. - Name your ranges. Use Excel’s Name Manager (Formulas > Name Manager) to define names like
Salesfor B2:B20 andRegionfor A2:A20. Then the formula becomes=SUMPRODUCT(SUBTOTAL(109,OFFSET(Sales,ROW(Sales)-MIN(ROW(Sales)),0,1)),--(Region="West"))which is more readable. - Use the FILTER function (Excel 365). If you have Microsoft 365, you can use
=SUM(FILTER(B2:B20,(A2:A20="West")*SUBTOTAL(103,OFFSET(B2:B20,ROW(B2:B20)-MIN(ROW(B2:B20)),0,1))))but this is not simpler than the SUMPRODUCT version. - Test with a small dataset. Before applying to large data, test the formula on a few rows to ensure it returns expected values.
Troubleshooting
- #VALUE! error. This usually means the ranges in the OFFSET and the condition are not the same size. Check that
ROW(B2:B20)-MIN(ROW(B2:B20))returns an array of the same length as your data. Ensure your data starts in row 2 and ends in row 20 as per the example. - Result is 0 when it shouldn’t be. Check that the condition text matches exactly, including case and spaces. Also ensure that the rows you expect to be visible are not actually hidden by the filter. Use the filter dropdown to confirm.
- Formula works but doesn’t update when filtering. Make sure you are using
SUBTOTAL(109,...)and not9. Also, if you have manually hidden rows, they are treated as hidden by 109. To only exclude filter-hidden rows, you would need a more advanced formula usingAGGREGATEor a helper column withSUBTOTAL(103,...). - Excel for the web gives a #NAME? error. This might happen if you are using a function not available in that version. The SUMPRODUCT and OFFSET functions are available, so double-check that you have not misspelled any function names.
- Performance issues on large datasets. The OFFSET formula is volatile and can slow down large workbooks. If you have thousands of rows, consider using a helper column instead, or use the
SUBTOTALfunction with theAGGREGATEfunction in a more complex array formula.
Frequently Asked Questions
Can I use SUMIFS with SUBTOTAL?
Not directly. SUMIFS cannot accept a SUBTOTAL result as a range. You must use the OFFSET method or a helper column as described above.
Does SUBTOTAL work with filtered data only?
Yes, SUBTOTAL with function number 109 ignores rows hidden by a filter. It also ignores rows hidden manually, which is often desirable.
What does the 109 in SUBTOTAL mean?
109 is the function number for SUM that ignores hidden rows. Other numbers like 9 include hidden rows. The full list is in Excel’s documentation.
Can I use this formula in a pivot table?
No, you would typically use a calculated field or a measure in a pivot table to achieve the same result. The formula works on regular worksheet data.
Why does my formula return a #REF! error?
This often happens if you delete rows or columns that are referenced in the OFFSET function. Make sure your ranges are correct and not broken.
Is there a simpler way to sum visible rows with criteria in Excel 365?
Yes, you can use the FILTER function combined with SUBTOTAL to create a dynamic array, but the SUMPRODUCT method is still the most compatible across versions.
Related
- Excel Strikethrough: How to Add It & Shortcuts
- Excel Conditional Formatting: How It Works
- Power Query Group By: Keep All Columns?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calculate Hours in Excel: Formulas and Time FormatsTo calculate elapsed hours in Excel, put the start time in A2 and end time in B2, then…
- Easy Way to Sum in Excel: Formulas and ShortcutsThe easiest way to sum numbers in Excel is to select the blank cell beneath or beside them…
- Excel Formula for Workdays in a MonthThe simplest Excel formula for workdays in a month is =NETWORKDAYS(start_date, end_date). For example, =NETWORKDAYS(DATE(2025,1,1), DATE(2025,1,31)) returns 23…
- Excel Row Number Formula Examples and UsesIn Excel, enter =ROW() to return the row number of the cell containing the formula; for example, in…
- COUNTIF Within a Range: How to Count Cells That Meet a ConditionThe direct answer: use the COUNTIF function with a range and a criterion, like =COUNTIF(A2:A100, "Yes"), which counts…
- Find Duplicate Names in Excel: Formulas and ToolsTo find duplicate names in Excel, select the name cells and choose Home > Conditional Formatting > Highlight…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.