SUBTOTAL in Excel

 What is the SUBTOTAL Function in Excel?

The SUBTOTAL function in Excel is a versatile tool that performs calculations (like sum, average, count, etc.) on filtered or visible data while ignoring hidden rows. Unlike other functions, SUBTOTAL can dynamically adjust when you apply filters, making it ideal for working with large datasets.

SUBTOTAL IN EXCEL

Key Features of SUBTOTAL

✅ Works with Filtered Data – Only calculates visible cells, excluding manually hidden rows.
✅ Multiple Operations – Supports 11 different functions (sum, average, count, max, min, etc.).
✅ Nestable – Can be used within other functions for advanced calculations.
✅ Ignores Other SUBTOTALS – Prevents double-counting in nested subtotals

SUBTOTAL COUNT IN EXCEL

Syntax of SUBTOTAL

=SUBTOTAL(function_num, range1, [range2], ...)

function_num – A number (1-11 or 101-111) that specifies the operation (e.g., 9 for SUM, 1 for AVERAGE).
1-11 → Includes hidden rows.
101-111 → Excludes hidden rows (even manually hidden ones).
range1, range2, ... – The cells or ranges to calculate.
Common Function Numbers
Function
Num (Include Hidden)
Num (Exclude Hidden)
AVERAGE
1
101
COUNT
  
  
  

  
  
  
2
102
MAX
4
104
MIN
5
105
SUM
9
109
Practical Examples of SUBTOTAL
1. Summing Filtered Data
excel
Copy
Download
=SUBTOTAL(9, B2:B100)  // Sums visible cells in B2:B100 (ignores hidden rows)
2. Counting Visible Rows
=SUBTOTAL(103, A2:A100)  // Counts non-hidden, non-empty cells in A2:A100
3. Dynamic Averages with Filters
=SUBTOTAL(101, C2:C50)  // Averages only visible cells in C2:C50
Why Use SUBTOTAL Instead of SUM, AVERAGE, etc.?
Automatic Updates – Adjusts when you apply or remove filters.
Better Data Integrity – Avoids errors from hidden rows in manual calculations.
Flexibility – One function replaces multiple others (SUM, COUNT, AVERAGE, etc.).