Excel SUBTOTAL Function: Totals, Counts, and Averages for Filtered or Hidden Rows

Excel SUBTOTAL Function: Accurate Totals, Counts, and Averages for Filtered or Hidden Rows

When you apply a filter to a report, SUM can easily return an incorrect result because it adds everything. The solution is the Excel SUBTOTAL function. It automatically excludes filtered or hidden rows and handles totals, counts, averages, and maximum values in one formula.

Quick Fix

  1. Select the data range and turn on Data > Filter (Alt+A+T).
  2. In the cell for the total, enter =SUBTOTAL(109, amount_range) → returns a total excluding filtered and manually hidden rows.
  3. For a table, use a structured reference such as =SUBTOTAL(9, Table1[Amount]).
  4. For a count, use =SUBTOTAL(103, range) (COUNTA); for an average, use =SUBTOTAL(101, range) (AVERAGE).

SUBTOTAL at a Glance

Function Number Table (1–11 / 101–111)

FunctionInclude Manually Hidden RowsExclude Manually Hidden Rows
AVERAGE1101
COUNT2102
COUNTA3103
MAX4104
MIN5105
PRODUCT6106
STDEV.S7107
STDEV.P8108
SUM9109
VAR.S10110
VAR.P11111

Rule: Rows hidden by filters are always excluded. Manually hidden rows are included with 1–11 and excluded with 101–111. Hidden columns are not excluded.

Nested formulas: Other SUBTOTAL formulas within a SUBTOTAL range are automatically ignored to prevent double-counting.

Why SUM Is Wrong and SUBTOTAL Is Right

SUM adds all values regardless of whether they are visible. SUBTOTAL calculates only visible rows—excluding filtered rows, and manually hidden rows when you use 101–111—so your report shows the true result.

Practical Example 1: Filtered Sales Report Total (9 vs. 109)

Sample Data

DateRegionCategoryQtyPriceAmount
2025-10-01SeoulA21000020000
2025-10-02BusanB11500015000
2025-10-03SeoulA31000030000
2025-10-04DaeguB5800040000
2025-10-05SeoulB41200048000

Filter: Region=Seoul → visible Amount total = 98,000

Formulas

=SUBTOTAL(9, F2:F6)      
=SUBTOTAL(109, F2:F6)    

Practical Example 2: Tables and Structured References

  1. Create a table with Ctrl+T (named Sales).
  2. Total: =SUBTOTAL(9, Sales[Amount])
  3. Also exclude manually hidden rows: =SUBTOTAL(109, Sales[Amount])

Practical Example 3: Visible Item Counts, Averages, and Extremes

  • Count numbers: =SUBTOTAL(102, Sales[Qty])
  • Count nonblank cells: =SUBTOTAL(103, Sales[Product])
  • Average: =SUBTOTAL(101, Sales[Amount])
  • Maximum/minimum: =SUBTOTAL(104, Sales[Amount]), =SUBTOTAL(105, Sales[Amount])

Alternatives and Considerations

  • AGGREGATE: Options such as =AGGREGATE(9,7, Sales[Amount]) can ignore hidden rows and errors.
  • Subtotal command: Use Data > Subtotal to automatically insert subtotals by group.
  • PivotTables: Automatically create multidimensional summaries, subtotals, and grand totals.
  • Cannot exclude hidden columns: SUBTOTAL is row-based. If you need to exclude hidden columns, redesign the calculation around vertical aggregation.

Troubleshooting

IssueCauseSolution
The value does not change after filteringUsing SUMReplace it with SUBTOTAL(9/109)
Manually hidden rows are includedUsing codes 1–11Use codes 101–111
Hidden columns are not excludedThe function is row-basedUse vertical aggregation or AGGREGATE
Double-countingUsing SUM for both higher- and lower-level totalsUse SUBTOTAL for the higher-level total too (nested SUBTOTAL formulas are automatically ignored)
#DIV/0!Denominator is zeroHandle the exception with IF

Wrap-Up and Related Articles

Calculating only the values you can see with SUBTOTAL makes your reports more reliable. Continue with the articles below to build on these skills.


Further reading (official documentation): SUBTOTAL function, AGGREGATE function, Subtotal command

Leave a Reply

Your email address will not be published. Required fields are marked *