SUBTOTAL Formula in Excel: How to Use It and Filter Data

Formulas & functions · Updated November 22, 2026

To total a range in Excel while excluding rows hidden by a filter, use =SUBTOTAL(9,B2:B10). This example assumes the values to add are in B2:B10. SUBTOTAL can also calculate averages, counts, and other results, and it updates when you filter a list. Before starting, make sure your data is arranged in a consistent range with a header row and no blank rows splitting the list.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows. The SUBTOTAL function also works in current Excel for Mac and Excel for the web.
  • A worksheet range with a header row and the values you want to summarize. If you want the result to respond to filters, include the whole list in the same range.
  • A blank cell outside the list for the result, so the total does not get included in its own calculation.

Step-by-Step

  1. Arrange and check your data. Put one type of information in each column, such as Date, Category, and Sales, and use a single header row. Keep the records in adjacent rows. A blank row inside the list can cause Excel to treat only part of the data as the filter range. Check that the values you plan to summarize are numbers, not numbers stored as text. If you are unsure, select a few cells and check their alignment or number format, and inspect any imported data for stray spaces or symbols.

  2. Choose a result cell. Click an empty cell above or below the list, or in a separate summary area. Avoid placing the result inside the range being calculated. For a list with sales in B2:B10 and a header in B1, you could put the result in B11. A cell outside the records is important because SUBTOTAL is designed to ignore other SUBTOTAL results in referenced ranges, but keeping a clear separation makes the sheet easier to check and maintain.

  3. Enter a SUBTOTAL formula. With sales values in B2:B10 and the result cell outside that range, enter =SUBTOTAL(9,B2:B10). Press Enter. The first argument, 9, tells Excel to add the values; the second argument is the range to calculate. If B2:B10 contains 120, 80, 150, 50, and 100, with the remaining cells blank, the result is 500. Blank cells do not change this sum.

  4. Choose the calculation code that matches your task. The first argument is called the function_num. Common choices are 9 for SUM, 1 for AVERAGE, 2 for COUNT of numeric cells, and 3 for COUNTA, which counts nonblank cells. For example, with numeric scores in C2:C8 and the result outside that range, use =SUBTOTAL(1,C2:C8) to calculate an average. If those scores are 70, 80, 90, and 100, with the other cells blank, the result is 85. For a count of numeric entries in the same layout, use =SUBTOTAL(2,C2:C8); that example returns 4. For a count of nonblank entries in a list of names in A2:A8, use =SUBTOTAL(3,A2:A8). It counts cells containing text or numbers, but not empty cells.

  5. Decide how manually hidden rows should behave. Function numbers 1 through 11 include rows that you hide manually, but still exclude rows removed by a filter. Numbers 101 through 111 do both: they exclude filtered-out rows and rows hidden manually. The final digit indicates the calculation, so 9 and 109 both mean SUM. With values in B2:B10 and the result outside the range, use =SUBTOTAL(109,B2:B10) if manually hidden rows should not count toward the sum. Use 9 if they should count. A filter hides records temporarily through filtering; manually hiding a row is a separate action, so choose the code based on how you expect the result to behave.

  6. Turn on filters for the list. Click a cell in the data range, then select Data > Filter. Excel adds drop-down arrows to the header row. If you filter a category or date column to show only selected records, a SUBTOTAL formula that covers the value range updates to calculate the visible records. On Excel for Mac and Excel for the web, use the Data tab and choose Filter; the ribbon layout may vary slightly by version. You can also create an Excel table with Insert > Table, making sure the option for headers is selected when your range has a header row.

  7. Test the formula with a filter. Filter a column so that some records are hidden, then check the result. For instance, if B2:B6 contains 120, 80, 150, 50, and 100, the unfiltered sum is 500. If a category filter leaves only the records with values 120 and 150 visible, a SUBTOTAL sum over B2:B6 returns 270. Clear the filter afterward to restore the full list. This quick test confirms that the range covers the intended records and that you did not use an ordinary SUM when you need a filter-responsive total.

  8. Copy or extend the formula carefully. If new records will be added, consider formatting the list as a table so the data range can expand as you add rows. If you use a fixed range instead, check that new records fall inside it; a formula referring only to B2:B10 will not include a new value typed in B11. Keep the formula cell outside the filtered records and clearly label it, such as Total of visible sales, so readers know what it represents.

Excel also supports other calculations through SUBTOTAL. The usual function numbers are 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 7 STDEV, 8 STDEVP, 9 SUM, 10 VAR, and 11 VARP. Adding 100 to a number from 1 through 11 tells SUBTOTAL to exclude manually hidden rows as well as filtered-out rows. For example, 4 and 104 both calculate a maximum, but 104 ignores manually hidden rows. If you use a less common calculation, confirm that its treatment of hidden rows matches your reporting needs.

Common Mistakes

  • Choosing the wrong function number. Using 1 calculates an average, not a total; use 9 for a sum. Choosing 9 when you also need manually hidden rows excluded will still include them. Use 109 for a sum that ignores those rows.

  • Including the formula cell in its own range. A range that includes the result cell can make the sheet confusing and may lead to unexpected behavior when other calculations are added. Put the result outside the records and check the first and last cells in the reference.

  • Assuming SUBTOTAL ignores every hidden row in every mode. All SUBTOTAL function numbers ignore rows hidden by filtering. Only the 101–111 series also ignores rows hidden manually. If manually hidden records should remain part of the result, use 1–11 instead.

  • Using an incomplete range. A formula that stops above the last record will omit later entries, even when those entries are visible. Review the displayed range in the formula bar, especially after adding rows or sorting data.

  • Expecting text-formatted numbers to add normally. If a value looks like a number but is stored as text, SUM may not include it. Clean or convert the source data rather than assuming SUBTOTAL will fix the data type automatically.

  • Confusing SUBTOTAL with grouped subtotals. SUBTOTAL is a worksheet function you enter in a cell. Excel’s outline or grouping features can also create subtotals for categories, but that is a different workflow. For a filter-responsive summary of a range, enter the function directly.

Tips

  • Use a clear label next to the result, such as Visible sales total, to distinguish a filter-responsive calculation from a grand total that always includes every record.

  • Keep the source data as a simple rectangle: one header row, consistent columns, and no blank rows or merged cells in the list. This makes filtering and range checking more reliable.

  • Use a table when the list will grow. Tables make it easier to filter records and generally expand as you add data. Put the summary outside the table, or use the table’s Total Row option if you want a built-in summary row.

  • For a simple sum that should include manually hidden rows but exclude filtered rows, use 9. For a sum that should exclude both kinds of hidden rows, use 109. Decide this before sharing the workbook so other people interpret the total correctly.

  • Check a result with a small known example before relying on a large report. Clear filters to compare the formula with the full total, then apply a filter with an easy-to-check subset.

  • Remember that SUBTOTAL ignores other SUBTOTAL formulas within its reference range. This helps when you create separate subtotal calculations in a larger list, because those intermediate results are not added again into a broader subtotal.

Troubleshooting

The result does not change after filtering. Check that the formula references the values in the filtered records, not a separate or incomplete range. Confirm that the filter applies to the same list and that you filtered records rather than merely sorting them. A filter hides rows that do not match; sorting only changes their order.

The result is too large after hiding rows manually. If you used a function number from 1 through 11, manually hidden rows still count. Change to the matching number from 101 through 111, such as 109 instead of 9 for SUM, if those hidden rows must be excluded.

The result is too small or misses a visible record. Inspect the range in the formula and extend it to cover every record. Then check whether a value is stored as text, whether the filter is excluding that record, or whether a row was manually hidden while using a 101–111 code.

The formula displays as text instead of calculating. Select the cell and check whether its number format is Text. Change the format to General or Number, then re-enter the formula so Excel evaluates it. Also make sure there is no apostrophe or leading space before the equals sign.

You see an error or an unexpected value. Check that the first argument is a valid function number from 1 to 11 or 101 to 111, and that the reference contains the intended cells. If you copied the formula from another locale or app, confirm that commas are the argument separators in your Excel settings; Excel installations configured for other regional conventions may display different separators. Make sure the formula cell is not part of a range that creates a circular reference.

Frequently Asked Questions

What is the difference between SUBTOTAL and SUM? SUM adds the values in its range and does not automatically exclude rows hidden by a filter. SUBTOTAL can calculate a sum while ignoring filtered-out rows, which makes it useful for totals that should reflect the records currently displayed.

Which SUBTOTAL number should I use for a total? Use 9 for SUM when manually hidden rows should still count. Use 109 when manually hidden rows should be excluded too. Both options exclude rows removed by a filter.

Does SUBTOTAL work with filters in Excel tables? Yes. If the formula references the table’s value rows, filtering the table changes which records are visible, and SUBTOTAL recalculates for the visible records. Keep the result outside the table’s data rows or use the table Total Row.

Can SUBTOTAL calculate an average or count? Yes. Use function number 1 for AVERAGE, 2 for COUNT of numeric cells, or 3 for COUNTA of nonblank cells. Choose the corresponding number in the 101–111 series if manually hidden rows should be excluded as well.

Why does my subtotal include a row I hid? The formula may use a number from 1 through 11. Those codes include manually hidden rows. Switch to the corresponding code from 101 through 111 if manually hidden rows should not be counted.

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.