How to Use SUBTOTAL Function in Excel - Practical Guide

Learn to use the SUBTOTAL function to calculate sums, averages, and counts while automatically ignoring filtered rows and hidden cells. This powerful formula is essential for financial reports and data analysis, ensuring accurate results when working with filtered datasets without manual recalculation.

Why This Matters

SUBTOTAL ensures accurate calculations on filtered data by excluding hidden rows, critical for financial reporting and data analysis. It prevents errors that occur when using standard SUM formulas on filtered tables.

Prerequisites

•Basic understanding of Excel formulas and cell references
•Familiarity with filtering and hiding data in Excel

Step-by-Step Instructions

Prepare your data

Set up a data range with headers and values, then apply AutoFilter via Data > Filter > AutoFilter to enable filtering capabilities.

Click on the result cell

Select the empty cell where you want the SUBTOTAL result to appear, typically below your data range.

Enter the SUBTOTAL formula

Type =SUBTOTAL(function_number, range) where function_number is 9 for SUM, 1 for AVERAGE, 3 for COUNTA, etc. (Use 100-109 to ignore manually hidden rows).

Specify the data range

Select the cells containing your values (e.g., B2:B100), excluding the header row from the range.

Press Enter and test filtering

Confirm the formula, then apply filters to your data to verify that SUBTOTAL updates automatically while ignoring filtered rows.

Alternative Methods

Use AGGREGATE function

AGGREGATE offers more filtering options and can ignore errors, making it more flexible than SUBTOTAL for complex calculations.

Manual filtering with SUM

Combine SUM with IF and array formulas, though this method is less efficient and doesn't automatically update when filters change.

Tips & Tricks

✓Function numbers 1-11 include hidden rows; use 101-111 to exclude manually hidden rows but include filtered rows.
✓SUBTOTAL ignores other SUBTOTAL functions in the range, preventing double-counting in nested calculations.
✓Always exclude headers from your range to avoid unexpected calculations.
✓Use SUBTOTAL in total rows of formatted tables for automatic recalculation when data changes.

Pro Tips

★Combine SUBTOTAL with named ranges for cleaner, more maintainable formulas across large worksheets.
★Use function number 109 (SUM ignoring hidden rows) in financial statements where manual hiding indicates excluded items.
★Apply SUBTOTAL to multiple columns simultaneously by copying the formula horizontally across your total row.
★Leverage SUBTOTAL in PivotTable-adjacent ranges to maintain calculations independent of pivot restructuring.

Troubleshooting

SUBTOTAL shows unexpected results after filtering

Verify that your range excludes headers and check that you're using the correct function number (9 for SUM, 1 for AVERAGE). Ensure filters are applied to the correct column.

Formula returns #VALUE! error

Check that your range contains only numeric values or that the function number is valid (1-11 or 101-111). Remove any text or error cells from the range.

SUBTOTAL includes hidden rows when it shouldn't

Change your function number from 1-11 to 101-111 to exclude manually hidden rows while only showing filtered data.

Results don't update when filters change

Ensure Automatic Calculation is enabled via Formulas > Calculation Options > Automatic, or press F9 to recalculate manually.

Related Excel Formulas

SUM AVERAGE COUNT AGGREGATE SUMIF COUNTA

Frequently Asked Questions

What's the difference between function numbers 1-11 and 101-111?

Function numbers 1-11 include both filtered and manually hidden rows in calculations. Numbers 101-111 exclude manually hidden rows but still ignore filtered rows, useful for financial statements where hiding indicates excluded items.

Can SUBTOTAL work without applying AutoFilter?

Yes, SUBTOTAL works with any hidden rows or columns, not just filtered data. However, its primary advantage is ignoring filtered rows, so it's most useful when filters are active.

Why should I use SUBTOTAL instead of SUM?

SUBTOTAL automatically adjusts when you filter data, whereas SUM includes all rows regardless of filter status. This makes SUBTOTAL ideal for dashboards and reports where filtered views must show accurate totals.

Does SUBTOTAL work with sorted data?

Yes, SUBTOTAL is unaffected by sorting. It only ignores filtered or manually hidden rows, not rows that have been reordered.

What function number should I use for AVERAGE?

Use 1 for AVERAGE (or 101 to exclude manually hidden rows). Function number 2 calculates COUNT, while 3 is COUNTA for non-empty cells.