Excel SUBTOTAL Function: Complete Guide With Formulas and Examples

The Excel SUBTOTAL function is one of those tools that looks simple on the surface but quietly does a lot of heavy lifting once you understand what it's actually built for. You might have seen it appear automatically when you turn on filters or use the outline feature, and wondered why Excel didn't just stick with regular SUM or AVERAGE.

The short answer? SUBTOTAL is smarter than its plainer cousins. It knows when rows are hidden, when filters are applied, and when you've already got other SUBTOTAL formulas nested inside the same range — and it adjusts accordingly so your totals don't double-count.

In this guide, you'll learn exactly how the SUBTOTAL function works, what each of the function numbers does, the difference between 1–9 and 101–111, and how to use it in real-world spreadsheets without breaking your data. We'll cover filtered ranges, manually hidden rows, the quirks around nested SUBTOTAL formulas, and several practical examples you can copy straight into your own workbook. Whether you're prepping for an Excel certification, an office task, or just trying to stop your totals from including hidden rows, this is the deep dive you've been looking for.

One thing to keep in mind before we go any further. SUBTOTAL is not a fancy replacement for everything. It has limits. It works on columns and ranges of cells, not on entire tables in the structured-reference sense, and it doesn't handle 3D references across sheets. But within its lane, it's brilliant — and once you've seen it ignore filtered-out rows in a sales report or roll up grouped data without you lifting a finger, you'll wonder how you ever lived without it.

Before we dig into syntax, let's clear up the most common point of confusion. SUBTOTAL doesn't just "add things up." It can perform 11 different aggregation operations — SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, and VARP — each selected by a number you pass in as the first argument. That's the magic. You're not just calling one function. You're calling a whole family of them through a single doorway. And because every flavor of SUBTOTAL plays nicely with filters, it's the function Excel reaches for when you click AutoSum after applying a filter.

Here's the basic shape of the formula. =SUBTOTAL(function_num, ref1, [ref2], …). The first argument is required. It tells Excel which calculation you want. The second is the range or ranges you want to aggregate. You can pass up to 254 ranges if you really need to, though most of the time you'll only need one or two. Simple.

Now let's talk about the two number ranges and why they exist. When you pass 1 through 11 as the function number, SUBTOTAL ignores rows that were hidden by a filter, but it still includes rows you manually hid by right-clicking and choosing Hide. When you pass 101 through 111, SUBTOTAL ignores both filtered rows and manually hidden rows. That's the only difference between the two sets. So if you've ever hidden a few rows by hand and noticed your SUM still counts them, that's expected. Switching to SUBTOTAL with a 109 instead of a 9 fixes it instantly.

Why does Excel split these into two ranges instead of just always ignoring hidden rows? Backwards compatibility. The 1–11 range came first, designed for the outline and filter features. Microsoft added 101–111 later when users started asking for manual hide support. Now both sets coexist, and you can choose the behavior you want. Most people, once they learn the difference, use 101–111 by default because it gives more predictable results.

Let's walk through a real example. Imagine you have a sales table with 200 rows. Columns include Date, Region, Salesperson, Product, and Revenue. You've applied a filter to show only the West region. If you put =SUM(E2:E201) at the bottom, Excel will sum all 200 rows regardless of the filter. That's not what you want. Swap in =SUBTOTAL(9, E2:E201) and now the total only reflects visible (filtered) rows. Change the filter to North, and the total updates automatically. No formula edits needed.

That's the killer feature. Dynamic recalculation as filters change. It's why every dashboard, financial model, and pivot-like summary I've ever built leans on SUBTOTAL somewhere. Combine it with conditional formatting and named ranges, and you've got a self-updating report card that survives filter changes without breaking.

Here's a question that trips a lot of people up. What happens if your range includes the header row or other SUBTOTAL formulas? The answer is encouraging. SUBTOTAL automatically ignores any other SUBTOTAL formulas inside its range, so you don't have to worry about double counting when you build nested summaries. Headers, since they're usually text, are skipped by numeric aggregations like SUM, AVERAGE, MAX, and MIN. Only COUNTA (3 or 103) will pick them up, so if you're counting non-empty cells, point your range at the data rows only.

Another important note. SUBTOTAL won't work with arrays in the way some newer dynamic array functions do, and it doesn't accept 3D references like =SUBTOTAL(9, Sheet1:Sheet3!A1:A10). If you need cross-sheet aggregation with filter awareness, you'll have to build it manually with helper columns or use Power Query. For single-sheet work, though, SUBTOTAL is hard to beat.

Let's compare SUBTOTAL to its newer cousin AGGREGATE. Introduced in Excel 2010, AGGREGATE does everything SUBTOTAL does and more. It can ignore error values, ignore other AGGREGATE and SUBTOTAL formulas, and perform additional calculations like MEDIAN, MODE, LARGE, SMALL, PERCENTILE, and QUARTILE. So why would you still use SUBTOTAL? Two reasons. First, AGGREGATE is wordier — it takes more arguments and is less intuitive for quick formulas. Second, SUBTOTAL has decades of muscle memory behind it. It's what shows up when you click AutoSum on filtered data, it's what most templates use, and it's what most online tutorials show.

That said, if you're working with messy data that has errors scattered throughout, AGGREGATE is the better choice. =AGGREGATE(9, 7, A2:A100) sums the range while ignoring both hidden rows AND error cells. SUBTOTAL alone can't do that — a single #DIV/0! error inside the range will propagate up to your total. Worth knowing if your spreadsheets aren't perfectly clean.

One more advanced trick. You can use SUBTOTAL to create a running visible count next to filtered data. Drop =SUBTOTAL(3, $B$2:B2) into the first row of a helper column, then drag down. As you change filters, the column shows 1, 2, 3, … only on visible rows. Useful for numbered exports, row tagging, and conditional logic in dashboards. It's a clever pattern worth keeping in your back pocket.

Let's talk about troubleshooting because SUBTOTAL is one of those functions that occasionally returns numbers you don't expect, and the cause is almost always one of three things. First, you used 9 instead of 109 (or vice versa) and the result includes or excludes rows you didn't expect. Switch the function number and try again.

Second, your range accidentally includes another SUBTOTAL formula and you're confused why the total seems too low or too high. Remember, SUBTOTAL ignores nested SUBTOTAL formulas, so this is usually the intended behavior — but verify by checking each cell. Third, you've got a text value or error in the middle of your numeric range. SUM-style SUBTOTALs ignore text, but errors propagate, so clean those up or move to AGGREGATE.

Performance is rarely an issue with SUBTOTAL, even on large datasets. It's a fast function. But if you're running it across hundreds of thousands of rows along with heavy conditional formatting, you might notice a slight delay when filters change. The fix is usually structural — use a Table, simplify the conditional formatting, or move to Power Pivot for very large datasets. Don't blame SUBTOTAL. It's doing its job well.

One pattern I love that doesn't get enough attention. Using SUBTOTAL to build interactive summary cards above your data. Picture a sales table with filter dropdowns at the top. Above the filters, you've got four little cells showing Total Revenue, Average Sale, Count of Transactions, and Highest Sale. Each one is a SUBTOTAL formula pointed at the corresponding data column.

When the user changes the filter — say, from "All Regions" to "Northeast" — every card updates instantly. It feels almost like a pivot table without the rigidity. And because SUBTOTAL evaluates on every recalculation, the cards stay in sync with whatever the user is exploring. This is the building block of half the financial models you'll ever see.

If you're studying for an Excel certification, you'll almost certainly run into SUBTOTAL questions. The MOS exam loves to ask about it, especially the difference between 1-11 and 101-111, and the behavior when you have nested SUBTOTAL formulas. Memorize the function numbers, practice both ranges, and you'll handle anything they throw at you. For everyday work, just remember the two patterns — SUBTOTAL(9, ...) for filtered sums and SUBTOTAL(109, ...) when you also want to ignore manually hidden rows. Those two cover 80 percent of real use cases.

One more thing worth flagging. SUBTOTAL behaves a little differently inside Excel Tables versus regular ranges. When you toggle on the Total Row option in a Table (via the Table Design tab), Excel quietly drops SUBTOTAL formulas into the row for you. You can pick which aggregation you want from a dropdown — sum, average, count, max, min, and so on — and Excel writes the formula behind the scenes using a structured reference like =SUBTOTAL(109,[Revenue]). That's friendlier than typing the full range, and it keeps your formula resilient to row additions.

Why does that matter? Because if you've ever extended a Table by typing in a new row, you've noticed that all the formulas in your sheet automatically pick up the new data. The Total Row's SUBTOTAL is no exception. Add ten more rows of sales, filter to a specific region, and the total updates without you touching anything. That's the kind of small win that makes Excel feel powerful, and SUBTOTAL is the engine behind it.

If you're coming from a programming background, SUBTOTAL might remind you of a higher-order function — pass in a behavior code (the function number) and a dataset, and it dispatches to the right operation. That's exactly what's happening under the hood. Excel internally maps the function number to one of eleven aggregation routines, then applies it to the filtered range.

Understanding this mental model helps you avoid the common mistake of trying to nest SUBTOTAL where you really wanted SUMIF or AVERAGEIFS. SUBTOTAL doesn't accept criteria. It accepts ranges and a fixed operation code. If you need conditional aggregation on filtered data, you'll combine SUBTOTAL with helper columns or look at SUMPRODUCT-based workarounds.

That workaround pattern is worth a quick mention. To do something like "sum visible rows where Region = West," you create a helper column that returns 1 for visible rows of the target region and 0 otherwise, then SUMPRODUCT the helper against your value column. The helper itself uses SUBTOTAL(103, OFFSET(...)) per row to detect visibility. It's verbose, but it works, and once you've built it you can reuse the pattern across many reports. Power Query and pivot tables eventually win this race for serious analytics, but the SUBTOTAL trick is a great in-between solution that lives entirely in formulas.

Wrapping up, the SUBTOTAL function is one of those Excel features that pays you back many times over the small effort it takes to learn. The basics fit on an index card. Function number, range, done. But the implications are big — dashboards that update with filters, grouped reports that don't double-count, summary cards that feel interactive, and clean totals that ignore the rows you've hidden manually. Once you've used it on real data, you'll find yourself reaching for it instead of plain SUM, especially in any workbook where filtering or hiding rows is part of the workflow.

If you take one thing away from this guide, let it be the difference between the 1-11 series and the 101-111 series. That single distinction — filter-only versus filter-plus-manual-hide — trips up more people than any other aspect of the function. Get that locked in your head, and you'll handle 90 percent of SUBTOTAL questions, whether they come from a certification exam, a coworker, or your own spreadsheet at 11 PM the night before a deadline.

And if you outgrow it? AGGREGATE is waiting on the next shelf. Same idea, broader power, with the ability to ignore errors and run advanced calculations like MEDIAN and PERCENTILE. But for the everyday stuff — totaling a filtered sales list, averaging response times for a chosen region, counting visible records in a tracker — SUBTOTAL remains the workhorse. Keep it in your toolbox. You'll use it more than you expect.

Worth noting too: keyboard shortcuts pair beautifully with SUBTOTAL workflows. Alt+= triggers AutoSum, and when your range is filtered, Excel automatically inserts a SUBTOTAL(9, ...) instead of a plain SUM. That tiny detail tells you Microsoft considers SUBTOTAL the correct default for filtered data. Use the shortcut, save yourself the typing, and let Excel make the right choice for you. The same applies to Ctrl+Shift+L for toggling filters and Ctrl+T for converting a range into a Table.