Excel SUBTOTAL Formula: How to Use It Correctly

Formulas & functions · Updated November 22, 2026

The SUBTOTAL formula in Excel is =SUBTOTAL(function_num, ref1, [ref2], ...). For example, =SUBTOTAL(9, A2:A10) returns the sum of values in A2:A10, ignoring any rows hidden by filters. This function is the standard way to build totals that stay accurate when you filter or hide rows.

What Does SUBTOTAL Actually Do?

SUBTOTAL performs one of 11 operations (sum, average, count, max, min, etc.) on a range, but with two key behaviors that ordinary functions lack:

  • It ignores rows hidden by a filter (when you use function numbers 1–11).
  • It ignores rows hidden manually (when you use function numbers 101–111).
  • It skips other SUBTOTAL formulas inside its range, so nested subtotals don’t double-count.

For instance, if you have sales data in B2:B100 and you filter to show only the East region, =SUBTOTAL(9, B2:B100) will sum only the visible East rows. A plain =SUM(B2:B100) would still sum all rows, filtered or not.

The Function Numbers You Need to Know

The first argument is a number that tells SUBTOTAL which operation to perform. The numbers 1–11 ignore filtered rows only; 101–111 ignore both filtered and manually hidden rows.

  • 1 or 101 – AVERAGE
  • 2 or 102 – COUNT (numbers only)
  • 3 or 103 – COUNTA (non-empty cells)
  • 4 or 104 – MAX
  • 5 or 105 – MIN
  • 6 or 106 – PRODUCT
  • 7 or 107 – STDEV (sample standard deviation)
  • 8 or 108 – STDEVP (population standard deviation)
  • 9 or 109 – SUM
  • 10 or 110 – VAR (sample variance)
  • 11 or 111 – VARP (population variance)

In practice, you’ll mostly use 9/109 for sums and 3/103 for counts. The difference matters when you hide rows manually (right-click > Hide) versus using a filter. If you want the total to react to both, use 101–111.

How to Write the SUBTOTAL Formula (Step by Step)

Assume you have a list of monthly expenses in cells A2:A13 (values 100, 150, 200, 250, 300, 350, 400, 450, 500, 550, 600, 650). To get the sum of all visible cells:

  1. Click the cell where you want the total, say A15.
  2. Type =SUBTOTAL(9, A2:A13) and press Enter.
  3. Excel returns 4650 (the sum of all 12 values) because nothing is hidden yet.
  4. Now apply a filter (Data > Filter) and uncheck a few values, say 100 and 650. The formula immediately recalculates to 3900, ignoring those two filtered rows.

If you manually hide row 5 (value 300) without a filter, =SUBTOTAL(9, A2:A13) still returns 4650. Change it to =SUBTOTAL(109, A2:A13) and it returns 4350, skipping the hidden row.

When to Use SUBTOTAL vs SUM, AVERAGE, or COUNT

Use SUBTOTAL whenever your data has filters or you plan to hide rows for reporting. Common scenarios:

  • Creating a filtered sales report with a grand total that updates automatically.
  • Building a dashboard where users can filter by date, region, or product.
  • Adding subtotals within a list that already has other subtotals (to avoid double counting).

Use plain SUM, AVERAGE, or COUNT when you always want the full range included, regardless of filtering or hiding. For example, a permanent total row that should never change when someone filters the data above it.

One common misconception: SUBTOTAL does not ignore errors or text. If a cell contains #DIV/0!, the sum will return an error. Use AGGREGATE (available in Excel 2010 and later) if you need to ignore error values as well.

Nested Subtotals and the Subtotal Command

Excel also has a built-in Subtotal command (Data > Subtotal) that inserts SUBTOTAL formulas automatically. It groups data by a column (like Region) and adds a subtotal row after each group, plus a grand total. The inserted formulas use function number 9 (sum) by default. If you run that command, you’ll see SUBTOTAL formulas in the rows labeled “Total”.

Because SUBTOTAL ignores other SUBTOTAL formulas, the grand total at the bottom correctly sums only the original data, not the intermediate subtotal rows. That is why using SUBTOTAL for grouped reports is safer than writing =SUM for each group and then summing those sums.

Common Problems and How to Fix Them

  • Wrong result when filtering: You used function numbers 1–11 but also manually hid rows. Switch to 101–111 to cover both.
  • Formula returns 0 or ignores numbers: The range may contain text that looks like numbers. Check for leading apostrophes or cells formatted as text. Use =SUBTOTAL(9, A2:A10) on a numeric range.
  • SUBTOTAL ignores rows hidden by a filter, but not rows hidden by grouping: Grouped (outlined) rows are considered manually hidden, so use 101–111.
  • You need to ignore error cells: SUBTOTAL cannot do that. Use =AGGREGATE(9, 6, A2:A10) where 6 means “ignore error values”.
  • Copying the formula down gives wrong references: If you copy a SUBTOTAL formula, Excel adjusts relative references. Use absolute references like $A$2:$A$10 if you need a fixed range.

Frequently Asked Questions

What is the difference between SUBTOTAL(9) and SUBTOTAL(109)?

Function number 9 ignores rows hidden by a filter only. Function number 109 ignores rows hidden by a filter and rows hidden manually (right-click > Hide). If you never hide rows manually, both give the same result.

Can SUBTOTAL count text cells?

Yes, use function number 3 (COUNTA) or 103. It counts non-empty cells, including text. Function number 2 or 102 counts only numeric values.

Does SUBTOTAL work in Google Sheets?

Yes, Google Sheets has the same SUBTOTAL function with the same syntax and function numbers. The behavior with filters is identical. However, the Subtotal command (Data > Subtotal) does not exist in Google Sheets; you must type the formula manually.

Why does my SUBTOTAL formula show a #VALUE! error?

This usually happens if the first argument is not a valid function number (1–11 or 101–111) or if you used a cell reference instead of a number. For example, =SUBTOTAL(A1, B2:B10) is invalid because A1 contains a number but Excel expects the number itself, not a reference. Write =SUBTOTAL(9, B2:B10).

Can I use SUBTOTAL with conditional formatting?

Yes, but SUBTOTAL only returns a value; it does not affect formatting. You can use the result in a formula for conditional formatting, but the formatting won’t change based on hidden rows. For dynamic highlighting of visible cells, consider using the AGGREGATE function or a helper column with SUBTOTAL(103, ...) to mark visible rows.

Is there a keyboard shortcut to insert SUBTOTAL?

No direct shortcut exists. You can press Alt+= to insert a SUM, then manually change it to SUBTOTAL. Or use the Subtotal command (Data > Subtotal) which inserts the formula for you, but that command is not available in Excel for the web.

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.