Explanation
SUBTOTAL performs calculations on a list or database and is especially useful when working with filtered data.
Syntax
=SUBTOTAL(function_num, ref1, [ref2], …)
Common Function Numbers
|
Function |
Calculation |
|
1 |
AVERAGE |
|
2 |
COUNT |
|
3 |
COUNTA |
|
9 |
SUM |
|
4 |
MAX |
|
5 |
MIN |
Example
=SUBTOTAL(9,B2:B20)
Calculates the SUM of the range.
If the data is filtered, SUBTOTAL can calculate the visible records appropriately.
Real-World Use
A sales manager filters:
City = Sirsa
Then:
=SUBTOTAL(9,G2:G100)
can calculate the sales total for the displayed records.
Practice
Create a sales table with:
Order ID | City | Product | Sales
Apply a filter and use SUBTOTAL to calculate:
- Visible Sales
- Visible Count
- Visible Average