When your subtotal rows contain Excel SUBTOTAL formulas, place an outer SUBTOTAL over the detail-and-subtotal range: it skips the inner subtotal results, so details are counted once. For example, with amounts in B2:B9, detail values in B2:B4 and B6:B8, and subtotal formulas in B5 and B9, enter =SUBTOTAL(9,B2:B9) in a blank cell outside that range. It returns the combined detail total, not the detail total plus the subtotal rows again.
What You’ll Need
- An Excel worksheet with numeric detail amounts and subtotal rows.
- For the simplest method, subtotal cells should contain formulas made with
SUBTOTAL, rather than typed or pasted numbers. - A blank cell outside the range you plan to total, so the formula does not include itself.
- Excel for Microsoft 365 on Windows for the menu directions below. The formulas also work in current Excel for Mac; Excel for the web supports the formulas, though some outline and subtotal controls may differ.
Step-by-Step
- Check which rows are details and which are subtotals. Look at the formula bar when you select a subtotal cell. A formula such as
=SUBTOTAL(9,B2:B4)indicates that the row is a genuine Excel subtotal. The number 9 tells Excel to sum its referenced values. If the cell instead contains a number, or a regularSUMformula, the outer formula will not automatically recognize it as a subtotal. - Confirm your data layout. In the example, column B contains amounts in B2:B9. Rows 2 through 4 and 6 through 8 are detail rows; B5 and B9 hold subtotals. The first group is 120, 80, and 100, giving 300. The second group is 75, 125, and 100, also giving 300. Keeping a small, clear example in mind makes it easier to check the final answer.
- Choose the right SUBTOTAL option. The function number 9 sums values and includes rows hidden manually with Hide. Function number 109 also sums, but excludes manually hidden rows. Both options ignore rows removed by an AutoFilter and ignore nested
SUBTOTALresults in the range. Use 9 if manually hidden details should still count; choose 109 if they should be excluded. - Enter the outer formula below or beside the data. With detail values in B2:B4 and B6:B8, and subtotal formulas in B5 and B9, enter
=SUBTOTAL(9,B2:B9)in B10 or another blank cell outside B2:B9. The answer is 600: the six detail amounts add to 600, and the two 300 subtotal results are not added a second time. For the version that excludes manually hidden rows, enter=SUBTOTAL(109,B2:B9)instead. - Press Enter and verify the result. Compare the displayed answer with the sum of the underlying detail groups, not with the sum of every visible number in the amount column. A normal
SUMacross B2:B9 would return 1,200 in this example: 600 of detail plus 600 of subtotal results. That contrast is a useful check that your formula is doing what you intended. - Check what happens when filtering. If the data has an AutoFilter, apply a filter and watch the outer subtotal update. A
SUBTOTALformula excludes filtered-out rows. Since subtotal rows made withSUBTOTALare also ignored by the outer formula, the result reflects the visible detail rows rather than adding subtotal lines to the visible detail again. - If subtotal values are not formulas, sum by a label instead. Suppose column A contains the word Subtotal on subtotal rows and column B contains their values, with the range A2:B9 covering the data. Enter
=SUMIF(A2:A9,"Subtotal",B2:B9)in an empty cell. If the two labeled subtotal cells contain 300 each, this returns 600. Replace the criterion with the exact label used in your sheet; the label must match the cells in column A. - For a new grouped report, consider Excel’s Subtotal command. Sort the list by the field that defines each group, select a cell in the list, then choose Data > Subtotal on the Windows ribbon. In the dialog, choose the grouping field, keep Sum as the function, and select the numeric column to total. Click OK. Excel inserts subtotal rows and an outline; a further
SUBTOTALover the range can then produce the overall total without double-counting those generated subtotal rows.
Common Mistakes
Using SUM on a range that includes subtotal rows. SUM adds every number in its range, including detail and subtotal amounts. Use an outer SUBTOTAL when the inner subtotal rows themselves use SUBTOTAL. If you need to use SUM, select only detail rows or sum rows identified by a reliable label.
Expecting SUBTOTAL to recognize every total row. It skips nested SUBTOTAL formulas, not every formula that looks like a subtotal. A row calculated with SUM, or a pasted value, is treated as ordinary content. If those rows are included in the outer range, they will be counted. Change the inner formulas to SUBTOTAL where appropriate, or use a label-based method such as SUMIF.
Choosing 9 or 109 without considering hidden rows. Both options respond to filters and ignore nested subtotals. Their difference is manually hidden rows: 9 includes them, while 109 excludes them. This can explain why a result changes—or does not change—as expected when someone hides rows.
Including the result cell in its own range. If the formula is placed in B10, a range such as B2:B10 includes the formula itself and causes a circular reference. Keep the result cell outside the referenced range, or adjust the range so it ends at the last data or subtotal row.
Tips
- Use consistent subtotal formulas throughout the report. A mixture of
SUBTOTAL,SUM, and typed totals makes it harder to calculate a reliable overall figure. - Keep the detail rows and subtotal rows in one continuous range where possible. Blank spacer rows are usually harmless, but check that the range starts and ends in the intended places.
- Give subtotal labels a consistent name if you plan to use
SUMIF. Clear labels also help readers distinguish group totals from detail amounts. - Remember that filtering and manually hiding are different. Test both if the report is used by people who hide rows, and select 9 or 109 to match the intended rule.
- If you use the Data > Subtotal command on an existing report, inspect the generated rows and formulas before relying on them. Keep a copy of important source data, especially before restructuring or sorting a list.
Troubleshooting
The result is twice what you expected. Check whether the subtotal cells use SUM or contain fixed numbers. An outer SUBTOTAL does not skip those. Convert the subtotal formulas to SUBTOTAL, or identify subtotal rows with a consistent label and use a conditional sum.
The result is too low after hiding rows. If you used SUBTOTAL(109,...), manually hidden rows are excluded. Use function number 9 if those rows should remain in the answer. If you filtered the list, both 9 and 109 exclude the filtered-out rows; clear the filter if you need the full-list total.
The result is zero or ignores subtotal rows. Verify that the amount cells contain numeric values, not numbers stored as text. Also check that a label-based criterion exactly matches the subtotal labels, including spaces. For a SUMIF approach, confirm that the first range contains the labels and the sum range contains the amounts, and that both ranges cover the same rows.
Excel shows a circular-reference warning. Move the total formula outside the range it references or shorten the range. For example, if the total formula is in B10, use a range ending at B9 rather than B10.
The Data > Subtotal command is unavailable or behaves unexpectedly. The command is designed for a regular list, not an Excel Table. Convert the table to a range if appropriate, then sort by the grouping column and try again. In Excel for Mac, look for Subtotal on the Data tab; in Excel for the web, the command may not be available in the same way, so use formulas or open the workbook in the desktop app.
Frequently Asked Questions
Does SUBTOTAL automatically ignore subtotal rows?
It ignores results from nested SUBTOTAL formulas inside its reference. It does not automatically detect rows labeled Total, cells with fixed amounts, or totals calculated with SUM.
Should I use 9 or 109?
Use 9 when manually hidden rows should count and 109 when they should not. Both exclude filtered-out rows and nested subtotal results.
Can I sum only visible subtotals?
If the subtotal rows contain SUBTOTAL formulas, an outer SUBTOTAL excludes those inner results and responds to filtering. To add only subtotal rows as individual values, use a consistent label and a conditional formula such as SUMIF; consider whether filtering should also affect that result.
Will this work if I use Excel’s Subtotal command?
Yes. The command creates subtotal rows using SUBTOTAL formulas, which an outer SUBTOTAL can ignore. Keep the outer formula outside the range it totals.
Can I use this for averages or counts instead of sums?
Yes. SUBTOTAL supports other operations, including average and count, by using a different function number. Make sure the inner and outer operations suit the result you need; for summing, use 9 or 109 as described above.
Related
- JMP Text to Columns: How to Split Data
- Group Rows in Power BI: Methods, Steps, and Limits
- Can You Hide a Column in Power BI? How It Works in Reports
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Validation Must Be a Date? No—Here’s HowNo, Excel data validation does not have to be a date. You can restrict entries to whole numbers,…
- Excel FV for One Day: Daily Rate and Annuity ExplainedIn Excel, calculate a one-day future value with FV by using a one-day interest rate and setting the…
- Excel Blue Line Print Area: What It MeansIn Excel, blue lines in Page Break Preview mark page breaks—the boundaries between printed pages—not necessarily the edge…
- Train Inventory Spreadsheet: What to Track and HowA train inventory spreadsheet is a practical Excel list of locomotives and rail vehicles, with fields for identification,…
- Google Sheets Merge Cells Shortcut and OptionsGoogle Sheets has no dedicated one-step keyboard shortcut for merging cells. In a browser, press Alt+/ on Windows…
- How to Filter in Google Sheets: Step-by-Step GuideTo filter data in Google Sheets, select a cell in your table, choose Data > Create a filter,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.