How to Use SUBTOTAL and SUMIF in Excel

Formulas & functions · Updated November 22, 2026

This guide shows you how to use Excel’s SUBTOTAL and SUMIF functions to summarize data. SUBTOTAL calculates totals that can ignore hidden rows, while SUMIF adds values based on a condition. You’ll learn the syntax, step-by-step examples, and common pitfalls.

What You’ll Need

  • Microsoft Excel for Microsoft 365 (Windows or Mac) – the steps work in Excel 2010 and later, but some ribbon paths differ slightly.
  • A worksheet with data arranged in columns (e.g., a list of sales with amounts and categories).
  • Basic familiarity with entering formulas and using the fill handle.

Step-by-Step

Using SUBTOTAL

  1. Understand the function syntax: =SUBTOTAL(function_num, ref1, [ref2], ...). The function_num is a number that tells Excel which operation to perform. For SUM, use 9 (includes hidden rows) or 109 (ignores hidden rows). For AVERAGE, use 1 or 101; for COUNT, 2 or 102; for MAX, 4 or 104; for MIN, 5 or 105.
  2. Enter a simple SUBTOTAL formula: Suppose your sales amounts are in cells B2:B10. Click cell B11 and type =SUBTOTAL(9, B2:B10). Press Enter. The result is the sum of all values in B2:B10. If you later filter the data, the formula updates to sum only visible rows if you use 109 instead of 9.
  3. Use SUBTOTAL with filtered data: To get a total that respects filtering, use =SUBTOTAL(109, B2:B10). When you apply a filter to your data (e.g., filter by region), this formula recalculates to show the sum of only the visible rows.
  4. Combine SUBTOTAL with other operations: You can use the same pattern for average: =SUBTOTAL(101, B2:B10) gives the average of visible cells. For count, =SUBTOTAL(102, B2:B10) counts numbers in visible cells.
  5. Note: SUBTOTAL ignores other SUBTOTAL formulas in its range, which prevents double counting when you have nested subtotals.

Using SUMIF

  1. Understand the syntax: =SUMIF(range, criteria, [sum_range]). The range is the cells you want to evaluate against the criteria. The criteria is the condition (e.g., “North”, “>1000”, or a cell reference). The optional sum_range is the actual cells to sum; if omitted, Excel sums the cells in range.
  2. Set up your data: For example, put region names in A2:A20 and sales amounts in B2:B20. You want the total sales for the “North” region.
  3. Enter a SUMIF formula: In cell D2, type =SUMIF(A2:A20, "North", B2:B20). Press Enter. Excel sums all amounts in B2:B20 where the corresponding cell in A2:A20 equals “North”. If your data has 5 North entries with amounts 100, 200, 300, 400, 500, the result is 1500.
  4. Use a cell reference for the criteria: Instead of typing “North” directly, put the region name in cell D1 and use =SUMIF(A2:A20, D1, B2:B20). This makes the formula easy to change.
  5. Use wildcards: For partial matches, use asterisks (*) or question marks (?). For example, =SUMIF(A2:A20, "N*", B2:B20) sums amounts for regions starting with “N”.
  6. Use numeric criteria: To sum amounts greater than 1000, use =SUMIF(B2:B20, ">1000") (note that sum_range is omitted, so it sums the same range). For a cell reference, use =SUMIF(B2:B20, ">"&D1, B2:B20) where D1 contains 1000.

Common Mistakes

  • Using the wrong function_num in SUBTOTAL: Using 9 instead of 109 when you want to ignore hidden rows. Remember: 9 includes hidden rows, 109 excludes them.
  • Forgetting to lock ranges with absolute references: When copying SUMIF formulas, relative references change. Use $A$2:$A$20 if you want to keep the range fixed.
  • Mismatched range sizes: In SUMIF, the range and sum_range must be the same size. If they aren’t, Excel may return unexpected results.
  • Using text criteria without quotes: In SUMIF, text criteria must be enclosed in double quotes, like “North”. If you reference a cell, don’t put quotes around the cell reference.
  • Assuming SUBTOTAL works like SUM on filtered data: SUBTOTAL with function 9 ignores hidden rows only when they are hidden by filtering, not when you manually hide rows. Use 109 to ignore manually hidden rows as well.

Tips

  • Use SUBTOTAL for grand totals in a filtered list: It’s the standard way to create totals that update when you filter.
  • Combine SUMIF with wildcards for flexible criteria: For example, =SUMIF(A2:A20, "*East*", B2:B20) sums all regions containing “East”.
  • Use SUMIFS for multiple criteria: If you need to sum based on more than one condition, use SUMIFS instead. Its syntax is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).
  • Test with a small dataset first: Before applying to large data, verify your formulas on a few rows to ensure the logic is correct.
  • Use tables (Insert > Table) for dynamic ranges: If you convert your data to an Excel Table, formulas like SUMIF can reference table columns (e.g., =SUMIF(Table1[Region],"North",Table1[Sales])) and automatically expand as you add rows.

Troubleshooting

  • SUBTOTAL returns 0 or wrong value: Check that the range is correct and that there are no errors in the referenced cells. Also verify that you used the right function_num.
  • SUMIF returns 0 unexpectedly: Ensure the criteria matches exactly. Text is case-insensitive, but leading/trailing spaces matter. Use =TRIM() on the criteria or the data if needed.
  • SUMIF returns #VALUE! error: This often happens when the range and sum_range are different sizes. Adjust the ranges so they match.
  • SUBTOTAL doesn’t ignore hidden rows: If you manually hide rows (right-click > Hide), function_num 9 will include them. Use 109 to ignore manually hidden rows.
  • Formulas not updating after filtering: Make sure calculation is set to Automatic (Formulas > Calculation Options > Automatic). If it’s manual, press F9 to recalculate.

Frequently Asked Questions

Can I use SUBTOTAL and SUMIF together?

Yes, you can nest them. For example, to sum visible rows that meet a condition, you might use an array formula like =SUMPRODUCT(SUBTOTAL(109,OFFSET(B2:B20,ROW(B2:B20)-MIN(ROW(B2:B20)),0,1)),--(A2:A20="North")). This is advanced; for most cases, use a helper column or a pivot table.

What is the difference between SUM and SUBTOTAL?

SUM adds all cells in a range. SUBTOTAL can perform various operations (sum, average, count, etc.) and can optionally ignore hidden rows. SUBTOTAL is designed for use with filtered lists.

How do I sum based on a date range with SUMIF?

Use criteria with comparison operators. For example, to sum sales after January 1, 2025, use =SUMIF(B2:B20, ">"&DATE(2025,1,1), C2:C20) where B contains dates and C contains amounts.

Does SUMIF work with text that contains numbers?

Yes, but the criteria must match exactly. For example, if a cell contains “123”, use “123” as criteria. Wildcards can help with partial matches.

Why does my SUBTOTAL formula show a #DIV/0! error?

This happens when you use function 101 (average) and there are no visible numeric cells. Check that the range contains numbers and that rows are not hidden in a way that excludes all values.

Can I use SUMIF with a cell reference for the criteria?

Yes. Simply use the cell reference without quotes, like =SUMIF(A2:A20, D1, B2:B20). If you need to compare to a value in a cell, use & for concatenation, e.g., =SUMIF(B2:B20, ">"&D1).

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.