Combine SUMIF and SUBTOTAL in Excel

Formulas & functions · Updated November 22, 2026

You can combine SUMIF and SUBTOTAL by using the SUMPRODUCT function with SUBTOTAL and OFFSET, or by using the newer AGGREGATE function with a condition array. The most common approach is: =SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B20,ROW(B2:B20)-MIN(ROW(B2:B20)),0,1)),--(A2:A20="North")) — this sums values in column B only for rows where column A equals “North” and the row is visible after filtering.

Why You Can’t Just Nest SUMIF Inside SUBTOTAL

Excel’s SUBTOTAL function is designed to ignore rows hidden by a filter (or manually hidden rows, depending on the function number). However, SUBTOTAL cannot evaluate a condition like “equals North” — it only performs aggregate operations (sum, average, count, etc.) on a range. Conversely, SUMIF applies a condition but ignores filter settings; it sums all cells that meet the criteria, even if those rows are hidden. Nesting them directly, like =SUBTOTAL(9,SUMIF(...)), would sum the SUMIF results across the range, which is not what you want. The workaround is to use SUMPRODUCT to multiply the SUBTOTAL result for each row by a condition array, effectively filtering both by the condition and by visibility.

How the SUMPRODUCT + SUBTOTAL + OFFSET Formula Works

The formula uses three key components:

  • SUBTOTAL(9, OFFSET(range, row_offset, 0, 1)) — This returns an array of subtotal values, one for each row in the range. The OFFSET function creates a single-cell range for each row, and SUBTOTAL with function number 9 (SUM) returns the value if the row is visible, or 0 if hidden (because the single cell is hidden).
  • --(condition_range = "criteria") — This converts TRUE/FALSE to 1/0, creating an array of 1s for rows that match the condition.
  • SUMPRODUCT multiplies the two arrays element-wise and sums the products, giving the total of visible rows that meet the condition.

Example: Suppose you have data in A2:B20, with names in A2:A20 and sales in B2:B20. To sum sales for “North” in visible rows only, enter this formula in a cell:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B20,ROW(B2:B20)-MIN(ROW(B2:B20)),0,1)),--(A2:A20="North"))

This formula works in Excel 365, Excel 2021, and earlier versions. It is an array formula but does not require Ctrl+Shift+Enter because SUMPRODUCT handles arrays natively. If you have a header row, adjust the range accordingly (e.g., B2:B20).

Using AGGREGATE as a Simpler Alternative

Excel 2010 and later have the AGGREGATE function, which can ignore hidden rows and apply conditions. However, AGGREGATE does not directly accept a criteria range like SUMIF; it works with array operations. To sum visible rows where a condition is met, you can use:

=SUMPRODUCT(AGGREGATE(9,7,OFFSET(B2:B20,ROW(B2:B20)-MIN(ROW(B2:B20)),0,1)),--(A2:A20="North"))

Here, AGGREGATE(9,7,...) uses function 9 (SUM) and option 7 (ignore hidden rows and error values). This is essentially the same as the SUBTOTAL version but with error handling. Note that both formulas are array formulas, but SUMPRODUCT handles them without special entry.

How to Choose Between SUBTOTAL and AGGREGATE

Use SUBTOTAL if you want the formula to respect both filtered rows and manually hidden rows (when using function numbers 1-11). Use AGGREGATE if you need to ignore error values as well, or if you want more control over which hidden rows are ignored (option 5 vs 7). For most cases, the SUBTOTAL version is sufficient and more widely understood. If you are using Excel 365, you can also use the FILTER function combined with SUBTOTAL in a dynamic array formula, but that requires a different approach.

Common Problems and How to Fix Them

  • Formula returns 0 even when there are visible matches: Check that the range references are correct, especially the OFFSET row calculation. Ensure that the condition range (A2:A20) matches the data range (B2:B20) in size.
  • Manual hidden rows are not ignored: If you manually hide rows (right-click > Hide), SUBTOTAL with function 9 ignores them, but if you use function 109 (which is for SUM but ignores only filtered rows, not manually hidden rows), you need to use 9. In the formula above, we use 9, so it ignores both.
  • Errors in the data (e.g., #DIV/0!): SUBTOTAL will ignore error values only if you use function numbers 101-111, but those ignore only filtered rows, not manually hidden. To ignore errors and hidden rows, use AGGREGATE with option 7.
  • Array formula not calculating: If you are using an older Excel version and the formula is not calculating correctly, ensure you press Ctrl+Shift+Enter if needed (though SUMPRODUCT should not require it).

Comparing with SUMIFS and Other Methods

If you don’t need to ignore hidden rows, simply use SUMIFS or SUMIF. For example, =SUMIF(A2:A20,"North",B2:B20) gives the total for North regardless of filters. To include multiple conditions, use SUMIFS. The SUMPRODUCT method is specifically for when you need to respect filtering. Another alternative is to use a helper column: add a column with a formula like =SUBTOTAL(9,B2) (but that would only work for each row individually) — actually, a better helper column is to use =SUBTOTAL(103,B2) to mark visible rows, then use SUMIFS on that helper column. But the SUMPRODUCT method avoids helper columns.

Frequently Asked Questions

Can I use SUMIF with SUBTOTAL directly? No, you cannot nest them directly because SUBTOTAL expects a range, not a sum. The SUMPRODUCT method is the standard workaround.

Does this formula work in Google Sheets? Yes, the same formula works in Google Sheets, but note that Google Sheets’ SUBTOTAL function numbers differ slightly: function 9 is still SUM, but you may need to use 109 for ignoring manually hidden rows? Actually, in Google Sheets, SUBTOTAL with 9 ignores filtered rows, but not manually hidden rows. The formula structure is the same.

How do I sum visible rows with multiple criteria? Extend the condition by multiplying more arrays: =SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B20,ROW(B2:B20)-MIN(ROW(B2:B20)),0,1)),--(A2:A20="North"),--(C2:C20="East")).

Why does my formula return #VALUE!? This usually happens if the ranges are not the same size. Ensure that all ranges (the data range, the condition range, and the OFFSET range) have the same number of rows.

Can I use this with a table (structured references)? Yes, but you need to adjust the OFFSET part. For example, if your table is named Table1, use OFFSET(Table1[Sales],ROW(Table1[Sales])-MIN(ROW(Table1[Sales])),0,1) — but note that ROW(Table1[Sales]) returns an array of row numbers, which works.

Is there a simpler way in Excel 365? Yes, you can use the FILTER function: =SUM(FILTER(B2:B20,(A2:A20="North")*SUBTOTAL(9,OFFSET(B2:B20,ROW(B2:B20)-MIN(ROW(B2:B20)),0,1)))) but that is not shorter. The SUMPRODUCT method is still the most compatible.

Final Tips for Reliable Results

Always test the formula with a small dataset and known values. If you are using Excel for Mac, the same formulas work, but note that the OFFSET function can be volatile and slow on large datasets. For very large datasets, consider using a helper column with =SUBTOTAL(103,B2) and then a SUMIFS on that helper. Also, remember that the formula will recalculate when you filter or hide rows, but if you have calculations set to manual, press F9 to recalculate.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.