Complete Guide to Filtered Sums, Averages, and Counts with the Excel SUBTOTAL Function (101 vs. 109)

When creating a report, you may want to sum only the rows visible after filtering, but a regular SUM includes hidden values and returns an incorrect result. This guide shows you how to use the Excel SUBTOTAL function to calculate filtered sums, averages, and counts all at once. At the end, you will also learn how to extend this to conditional sums for visible cells only.

Quick Fix

1. Select the data range and turn on Data > Filter. (Shortcut: Ctrl+Shift+L)
2. Filter by the criteria you want.
3. To exclude hidden rows (manually hidden rows), use function numbers 101–111.
   Sum: =SUBTOTAL(109, D2:D999)
   Average: =SUBTOTAL(101, D2:D999)
   Count (numbers): =SUBTOTAL(102, D2:D999)
4. Convert the range to an Excel table (Ctrl+T) for cleaner structured references:
   =SUBTOTAL(109, [Sales])
5. SUBTOTAL automatically excludes rows hidden by filtering. To also exclude manually hidden rows, use 101–111.

SUBTOTAL Basics: Filtered Rows and Hidden Rows

Filtered rows: SUBTOTAL excludes rows hidden by AutoFilter from calculations.
Hidden rows (manually hidden): Numbers 1–11 include manually hidden rows, while 101–111 exclude them.
Double-count prevention: SUBTOTAL automatically ignores the results of other SUBTOTAL formulas.

Function Number Table (1–11 vs. 101–111)

Function	Include Hidden Rows	Exclude Hidden Rows
AVERAGE	1	101
COUNT	2	102
COUNTA	3	103
MAX	4	104
MIN	5	105
PRODUCT	6	106
STDEV.S	7	107
STDEV.P	8	108
SUM	9	109
VAR.S	10	110
VAR.P	11	111

Why SUBTOTAL Ignores Other SUBTOTAL Results

When you calculate subtotals for report sections and then calculate a grand total, adding the subtotals again would double-count the values. SUBTOTAL automatically excludes the results of other SUBTOTAL formulas to avoid this problem.

Practical Example: Filtered Sales Sums, Averages, and Counts by Category

Sample Data (Copy and Use Directly)

Date	Category	Product	Quantity	Unit Price	Sales
2025-10-01	Outer	Jumper A	5	49000	=D2*E2
2025-10-01	Top	T-Shirt B	12	19000	=D3*E3
2025-10-02	Bottom	Pants C	8	39000	=D4*E4
2025-10-02	Outer	Jumper D	3	69000	=D5*E5
2025-10-03	Top	T-Shirt E	7	22000	=D6*E6
2025-10-03	Outer	Jumper F	2	129000	=D7*E7

After applying AutoFilter to show only Category = Outer:

• Sum: =SUBTOTAL(109, F2:F999)
• Average: =SUBTOTAL(101, F2:F999)
• Count (numbers): =SUBTOTAL(102, F2:F999)

Excel Table (Ctrl+T) + Structured References

When you convert the range to a table (named 매출표), formulas expand automatically.

• Sum: =SUBTOTAL(109, 매출표[매출])
• Average: =SUBTOTAL(101, 매출표[매출])
• Count (numbers): =SUBTOTAL(102, 매출표[매출])

Advanced: Conditional Sums for Visible Cells Only

SUMPRODUCT × SUBTOTAL × OFFSET

=SUMPRODUCT(
  SUBTOTAL(109, OFFSET(F2, ROW(F2:F100)-ROW(F2), 0, 1)),
  --(C2:C100="Outer")
)

OFFSET splits column F into individual rows and passes them to SUBTOTAL. Values are added only for visible rows, then multiplied by the criteria so that only the intersection is totaled.

Dynamic Array (FILTER) Alternative

=SUM(
  FILTER(F2:F100, (SUBTOTAL(103, OFFSET(F2, ROW(F2:F100)-ROW(F2), 0, 1))=1) * (C2:C100="Outer"))
)

Alternatives, Notes, and Checklist

• Use AGGREGATE for expanded options to ignore errors and handle hidden rows.
• Choose accurately between 1–11 and 101–111.
• Be careful when using COUNTA (3/103) with text values.
• Structured references in Excel tables are recommended.
• OFFSET is volatile: consider performance and evaluate a PivotTable alternative.
• Vertical aggregation is recommended; for horizontal aggregation, consider PivotTables or a combination of functions.

Troubleshooting

Issue	Cause	Solution
The total is incorrect after filtering	Using SUM	Replace it with SUBTOTAL(109)
Manually hidden rows are included	Using function numbers 1–11	Switch to function numbers 101–111
Values are added twice	Nested SUBTOTAL formulas	Keep only the top-level total
Cannot create a visible-cells-only conditional sum	Trying to use SUMIFS	Use SUMPRODUCT×SUBTOTAL×OFFSET
Calculation is slow	OFFSET is volatile	Consider a PivotTable/Power Pivot

Wrap-Up & Related Articles

Now that you know the Excel SUBTOTAL function, filtered sums, averages, and counts are easy to calculate. Explore these related articles next:

• Conditional Logic Basics: The IF Function
• Conditional Sums: Complete SUMIFS Guide
• Multiple-Criteria Lookups: VLOOKUP/XLOOKUP

---

Additional Resources (Official Documentation) — Microsoft: SUBTOTAL Function, Microsoft: AGGREGATE Function

엑셀 중복값 제거 3가지: Remove Duplicates·UNIQUE·Power Query