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
- Understand the function syntax:
=SUBTOTAL(function_num, ref1, [ref2], ...). Thefunction_numis a number that tells Excel which operation to perform. For SUM, use9(includes hidden rows) or109(ignores hidden rows). For AVERAGE, use1or101; for COUNT,2or102; for MAX,4or104; for MIN,5or105. - 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 use109instead of9. - 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. - 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. - Note: SUBTOTAL ignores other SUBTOTAL formulas in its range, which prevents double counting when you have nested subtotals.
Using SUMIF
- Understand the syntax:
=SUMIF(range, criteria, [sum_range]). Therangeis the cells you want to evaluate against the criteria. Thecriteriais the condition (e.g., “North”, “>1000”, or a cell reference). The optionalsum_rangeis the actual cells to sum; if omitted, Excel sums the cells inrange. - 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.
- 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. - 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. - 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”. - 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
9instead of109when 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$20if you want to keep the range fixed. - Mismatched range sizes: In SUMIF, the
rangeandsum_rangemust 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
SUMIFSinstead. 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
- Excel Template for Saving Goal Tracking
- Excel Template for Project Expense Tracking
- Excel Template for Paying Off Debt: Free & DIY
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Find Duplicate Entries in Excel with FormulasTo find duplicate entries in Excel, select the cells you want to check and choose Home > Conditional…
- How to Sort in Excel Without Mixing Up Your DataTo sort in Excel without mixing up your data, select the entire table or a cell inside a…
- XLOOKUP Partial Match: Wildcard Examples in ExcelFor a contains-style partial match, put the text to find in E2 and use =XLOOKUP("*"&E2&"*",A2:A7,B2:B7,"No match",2). The final…
- Excel MIN Function Examples: Find the Smallest ValueExcel’s MIN function returns the smallest number in a set of values. For example, with scores in B2:B8,…
- Compare Two Sheets in Excel: Methods, Formulas, and TipsTo compare two Excel sheets, check corresponding cells with a formula such as =IF(January!A2=February!A2,"Match","Different"), then fill it across…
- Excel VLOOKUP Shortcut Keys: What Actually WorksExcel has no dedicated keyboard shortcut that inserts or runs VLOOKUP by itself. The quickest keyboard approach is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.