How to Use Subtotal Function in Excel + Tips

Hello everyone! Today i will show you how to use SUBTOTAL function in Excel step by step.

What is SUBTOTAL Function in Excel?

The SUBTOTAL function is a very useful function in Excel that can perform various calculations (like SUM, COUNT, AVERAGE, etc.) on a range of data. What makes it special is that it can ignore hidden rows (when you filter your data) or include them, depending on how you use it.

📚 Related Tutorial: Want to calculate totals based on conditions? Check out How to Use SUMIF Function in Excel!

🛠️ Formula Syntax

=SUBTOTAL(function_num, ref1, [ref2], ...)

• function_num: This number tells Excel which calculation to perform:
  • 1 = AVERAGE
  • 2 = COUNT
  • 3 = COUNTA
  • 4 = MAX
  • 5 = MIN
  • 6 = PRODUCT
  • 7 = STDEV
  • 8 = STDEVP
  • 9 = SUM
  • 10 = VAR
  • 11 = VARP
  • 101 = AVERAGE (ignores hidden rows)
  • 102 = COUNT (ignores hidden rows)
  • 103 = COUNTA (ignores hidden rows)
  • 104 = MAX (ignores hidden rows)
  • 105 = MIN (ignores hidden rows)
  • 106 = PRODUCT (ignores hidden rows)
  • 107 = STDEV (ignores hidden rows)
  • 108 = STDEVP (ignores hidden rows)
  • 109 = SUM (ignores hidden rows)
  • 110 = VAR (ignores hidden rows)
  • 111 = VARP (ignores hidden rows)
• ref1, [ref2], ...: The range of cells you want to calculate (example: C2:C5).

📋 Sample Data Table (Follow Along)

To test all examples easily, create this simple sales table in cell range A1:C5:

📊 Easy Examples to Follow

Example 1: Sum Filtered Rows (Auto-ignore hidden rows)

If you use Excel Data Filter to show North region ONLY (hiding Dara & Sam):

• Normal =SUM(C2:C5) will still calculate all 4 rows = $2,600.
• But using SUBTOTAL:

=SUBTOTAL(9, C2:C5)

👉 Result when filtered to North: $1,300 (Sok $500 + Lina $800). It automatically ignores filtered out rows!

Example 2: Calculate Average of Visible Filtered Rows

To get average sales for visible rows only after filtering:

=SUBTOTAL(1, C2:C5)

👉 Result when filtered to North: $650 (Average of $500 and $800).

Example 3: Count Number of Visible People (COUNTA)

=SUBTOTAL(3, A2:A5)

👉 Result when filtered to North: 2 (Sok & Lina).

Example 4: Difference Between Function 9 and 109

=SUBTOTAL(109, C2:C5)

If you right click row 3 and click Hide, function 109 will ignore row 3 completely!

💡 Quick Tips for SUBTOTAL Function:

1. Prevents Double Counting: SUBTOTAL automatically ignores other nested SUBTOTAL formulas inside your range! You can calculate sub-totals and grand total safely without double counting numbers.
2. Cheat Sheet Rule: Use 9 for standard table filtering, or use 109 if you manually hide rows.

📚 Related Resources & Tutorials

• SUMIF vs SUMIFS in Excel: Easy Guide
• How to Use FILTER Function in Excel
• How to Use SUM Function in Excel
• Top 15 Basic Excel Formulas for Beginners ↗