SUBTOTAL Formula in Excel: How to Apply It Step by Step

Formulas & functions · Updated November 22, 2026

To apply a subtotal formula in Excel, choose what you want to calculate and enter =SUBTOTAL(9,B2:B7) in the total cell when your sales values are in B2:B7. The formula returns 950 for the example below and updates to include only visible rows when you filter the list. First set up a simple range with a header row and make sure the cells you want to summarize contain the right kind of data.

What You’ll Need

  • A worksheet in Microsoft Excel for Microsoft 365 on Windows. The SUBTOTAL formula also works in current Excel for Mac and Excel for the web.
  • A range of data with a header row and at least one column to summarize. Keep each record on its own row and avoid blank rows in the middle of the list.
  • A decision about whether manually hidden rows should count. Filtered-out rows are excluded by SUBTOTAL; a function number determines how the formula treats rows you hide manually.

Step-by-Step

  1. Set up your list. Put a clear label at the top of each column, with one record per row. For example, enter Category in A1 and Sales in B1, then place East, 120 in row 2; West, 80 in row 3; East, 200 in row 4; North, 150 in row 5; West, 100 in row 6; and East, 300 in row 7. Avoid merged cells and blank lines within the list because they can make filtering and range selection confusing.
  2. Decide which subtotal operation to use. SUBTOTAL can calculate a sum, average, count, minimum, maximum, and several other statistics. For a total of sales, use function number 9, which means SUM. Common alternatives are 1 for AVERAGE, 2 for COUNT of numeric values, and 3 for COUNTA of nonblank cells. The formula uses the operation number first and the range to calculate second.
  3. Choose the total cell and enter the formula. In this example, keep the data in rows 1 through 7 and put a label such as Total in A8. With sales in B2:B7, enter =SUBTOTAL(9,B2:B7) in B8. Press Enter. The six sales values add up to 950, so B8 should show 950. The formula does not include the header or its own result because the range starts at B2 and ends at B7.
  4. Turn on filters for the data range. Select a cell in A1:B7, then choose Data > Filter. Excel adds a drop-down arrow to each header. Keep the total row outside the filtered range: here, the list ends on row 7 and the formula is on row 8. If you apply filtering to a larger range that includes the result row, the total can behave unexpectedly or be hidden with the records.
  5. Test the formula with a filter. Open the drop-down arrow in the Category header, clear Select All, select East, and apply the filter. The visible sales are 120, 200, and 300, so the subtotal should change from 950 to 620. Clear the filter to show all records again and confirm that the result returns to 950. This is the key advantage over a regular SUM formula: SUBTOTAL adjusts to rows hidden by filtering.
  6. Choose whether manually hidden rows count. Function numbers 1 through 11 include rows hidden manually, while 101 through 111 exclude them. Both sets exclude rows hidden by a filter. To sum the example sales while also ignoring any rows you hide manually, use =SUBTOTAL(109,B2:B7) in B8, with the same sales layout in B2:B7. It returns 950 when all rows are visible, and returns 620 when you filter for East. If you manually hide a sales row, this version leaves that value out too.
  7. Check the result and preserve the range. Confirm the total against the visible records or a quick hand calculation, especially the first time you use the formula. When adding more records, expand the range so the new rows are included. If you convert the list to an Excel Table, it can expand as you add rows, but check the formula’s references after conversion. In Excel for Mac, enter the formula in a cell in the same way; the ribbon layout can differ slightly, but the SUBTOTAL function and function numbers are the same. Excel for the web also supports the formula.

For a count rather than a sum, use a count function number. With category labels in A2:A7, enter =SUBTOTAL(3,A2:A7) in a cell outside the filter range. It returns 6 for the six nonblank labels and changes to 3 when you filter for East. This is useful for counting visible records, but it counts nonempty cells in the selected range, not necessarily distinct categories.

Common Mistakes

  • Choosing the wrong function number. A subtotal using 3 counts nonblank cells; it does not add their values. Use 9 for a sum, or select another number that matches the result you need.
  • Including the result cell in the range. A range that includes its own subtotal cell can cause confusing outcomes. Keep the formula outside the range it summarizes, or carefully adjust the reference when you move the total.
  • Expecting manually hidden rows to be ignored with every code. Function numbers 1–11 ignore filtered-out rows but still include manually hidden rows. Use the matching number from 101–111 if manually hidden rows should also be excluded.
  • Using text or blanks where you expect numeric values. A sum ignores text, so a number stored as text may be left out without an obvious error. Check the source cells and convert values to numbers if appropriate.
  • Assuming SUBTOTAL creates grouped subtotal rows. The formula calculates a result in the cell where you enter it. It does not insert category headings or separate subtotal lines automatically. Excel’s Data > Outline > Subtotal command is a different feature for adding grouped subtotal rows to a standard range; it is not available for an Excel Table.

Tips

  • Use 109 for a filter-friendly sum when you want both filtered-out and manually hidden rows omitted. Use 9 when manually hidden values should remain part of the total.
  • Keep the total just below or beside the source list, but outside the range you filter. Give it a clear label such as Visible sales total so readers know what it represents.
  • Use consistent data types in each column. If Sales contains a mixture of numbers, text, and blanks, check the entries before relying on the total.
  • For a longer list, format the data as an Excel Table with Insert > Table, or use Ctrl+T on Windows. On Mac, the keyboard shortcut can depend on the keyboard settings; use Insert > Table if Ctrl+T does not open the Create Table dialog. Keep the table’s total row separate from a formula you have placed outside it, or use the Table Design total-row option and choose Sum when that is the behavior you want.
  • Remember that nested SUBTOTAL formulas inside the reference range are ignored by another SUBTOTAL formula. This helps avoid double-counting when a range contains earlier subtotals, but it also means a nested result will not be added again as an ordinary value.
  • When sharing a workbook, label whether the total responds to filters and whether manual hiding affects it. That makes the chosen function number easier for someone else to understand.

Troubleshooting

The total does not change after filtering. Check that the filter is applied to the records included in the formula range and that the formula refers to those rows. SUBTOTAL responds to rows hidden by an Excel filter, not to a filter applied to a different or separate range. Clear and reapply the filter using the header row for the data.

The answer is zero or smaller than expected. Inspect the referenced cells for numbers stored as text, blank cells, or an incomplete range. Click a suspicious value and check whether it is aligned or flagged as text; use Excel’s conversion options when the value is intended to be numeric. Also verify that the formula uses 9 or 109 for a sum rather than a count or other operation.

The formula appears as text in the cell. Change the cell format from Text to General, then edit the cell and press Enter so Excel evaluates the formula. Check that the entry begins with an equals sign and that Show Formulas is not turned on. On Windows, Ctrl+` toggles Show Formulas; on Mac, the shortcut can vary by keyboard, so use the Formulas tab’s Show Formulas control if needed.

The formula shows an error or will not calculate. Check that the function name is spelled SUBTOTAL, the function number is between 1 and 11 or 101 and 111, and the range reference is valid. In the standard English-language Excel syntax shown here, arguments are separated by commas. Some regional settings use semicolons as separators; if Excel rejects the commas, use the separator expected by that installation. Confirm that calculation is not set to Manual if a correct formula is not updating.

The total includes a row you hid yourself. If your formula uses a number from 1 to 11, that is expected: those codes include manually hidden rows. Replace it with the corresponding code 101 to 111. For a sum, use 109. Filtered-out rows remain excluded with either code family.

Frequently Asked Questions

What is the difference between SUBTOTAL and SUM? SUM adds values in its range and does not automatically adjust for rows hidden by a filter. SUBTOTAL excludes filtered-out rows and can also exclude manually hidden rows when you choose a function number from 101 to 111. Use SUM for a straightforward fixed total and SUBTOTAL when the visible records should determine the result.

Which SUBTOTAL number should I use to add values? Use 9 for a sum that still includes manually hidden rows, or 109 for a sum that omits manually hidden rows as well as filtered-out rows. Both numbers calculate a sum and both respond to filters.

Can I use SUBTOTAL to calculate an average or count? Yes. Use 1 for an average, 2 to count numeric cells, or 3 to count nonblank cells. The matching codes 101, 102, and 103 exclude manually hidden rows. All these operations exclude filtered-out rows. Choose a range containing the data you actually want to average or count.

Does SUBTOTAL work with filters in an Excel Table? Yes. The function responds to rows hidden by a Table’s filter. If you use the Table Design total row instead, Excel can create a subtotal calculation for the selected column. Avoid placing a separate formula inside the records themselves, where filtering could hide it.

Why are some values not included in my subtotal? The rows may be filtered out, manually hidden when you use a 101–111 code, or outside the formula’s range. A sum also ignores text entries, including numbers stored as text. Check the function number, the range, the current filter, and the data type of the missing values.

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.