Introduction
The SUBTOTAL function in Excel Tutorial calculates totals, averages, counts, minimums, and maximums for a dataset. It is especially useful when working with filtered company reports because it can automatically exclude rows hidden by a filter.
For example, a sales manager can calculate total sales for the North region after filtering the data, without manually selecting individual records. In this tutorial, you will learn SUBTOTAL function through practical business examples, from basic calculations to filtered reports, hidden rows, Excel Tables, and business analysis.
SUBTOTAL Function Syntax
=SUBTOTAL(function_num, ref1, [ref2], ...)
- function_num: A number that tells Excel which calculation to perform.
- ref1: The first cell range to calculate.
- ref2: Additional ranges, if required.
SUBTOTAL Function Numbers
| Function Number | Calculation | Function Number | Calculation |
|---|---|---|---|
| 1 | AVERAGE | 101 | AVERAGE |
| 2 | COUNT | 102 | COUNT |
| 3 | COUNTA | 103 | COUNTA |
| 4 | MAX | 104 | MAX |
| 5 | MIN | 105 | MIN |
| 6 | PRODUCT | 106 | PRODUCT |
| 7 | STDEV.S | 107 | STDEV.S |
| 8 | STDEV.P | 108 | STDEV.P |
| 9 | SUM | 109 | SUM |
| 10 | VAR.S | 110 | VAR.S |
| 11 | VAR.P | 111 | VAR.P |
Important difference:
- Function numbers 1–11 include manually hidden rows but exclude rows hidden by a filter.
- Function numbers 101–111 exclude both manually hidden rows and rows hidden by a filter.
For example:
=SUBTOTAL(9,B2:B10)calculates the sum and includes manually hidden rows.=SUBTOTAL(109,B2:B10)calculates the sum and excludes manually hidden rows.
Both formulas exclude rows hidden by a filter.
Question 1: Calculate Total Sales Using SUBTOTAL
Problem Statement:
A company wants to calculate total sales using a function that will also work correctly when the sales report is filtered.
Data:
| Employee | Sales (₹) |
|---|---|
| Amit | 12,000 |
| Priya | 18,000 |
| Rahul | 15,000 |
| Neha | 25,000 |
| Karan | 20,000 |
Assume the sales values are in cells B2.
Excel Formula:
=SUBTOTAL(9,B2:B6)
Expected Result: ₹90,000
The function number 9 tells Excel to calculate the sum.
Student Task:
Add Riya with sales of ₹30,000 in cell B7. Update the formula to include the new employee.
Expected Formula:
=SUBTOTAL(9,B2:B7)
Expected Result: ₹120,000
Challenge: Apply a filter to show only Amit and Neha. What total does SUBTOTAL return?
Expected Result: ₹37,000
Question 2: Calculate Average Sales for Visible Employees
Problem Statement:
A sales manager wants to calculate the average sales of employees who remain visible after filtering the report.
Data:
| Employee | Region | Sales (₹) |
|---|---|---|
| Amit | North | 10,000 |
| Priya | South | 20,000 |
| Rahul | North | 30,000 |
| Neha | East | 40,000 |
| Karan | North | 50,000 |
Assume sales are in cells C2.
Excel Formula:
=SUBTOTAL(1,C2:C6)
Expected Result: ₹30,000
Student Task:
Filter the Region column to show only North. Calculate the average sales of the visible North-region employees.
Expected Formula:
=SUBTOTAL(1,C2:C6)
Expected Result: ₹30,000
The visible North sales are ₹10,000, ₹30,000, and ₹50,000. Their average is ₹30,000.
Business Use: This approach is useful when managers want the average for a selected region, department, or product category.
Question 3: Count Numeric Sales Records
Problem Statement:
A company wants to count how many employees have numeric sales values entered in the report.
Data:
| Employee | Sales (₹) |
|---|---|
| Amit | 12,000 |
| Priya | 18,000 |
| Rahul | (Blank) |
| Neha | 25,000 |
| Karan | 20,000 |
Assume sales are in cells B2.
Excel Formula:
=SUBTOTAL(2,B2:B6)
Expected Result: 4
The function number 2 counts numeric cells. The blank cell is not counted.
Student Task:
Enter ₹15,000 in Rahul’s sales cell. Calculate the updated count.
Expected Result: 5
Remember: COUNT counts numeric values, not all non-empty cells.
Question 4: Count All Non-Empty Employee Names
Problem Statement:
The HR department wants to count how many employee names are visible in a filtered list.
Data:
| Employee | Department |
|---|---|
| Amit | Sales |
| Priya | HR |
| Rahul | IT |
| Neha | Sales |
| (Blank) | IT |
Assume employee names are in cells A2.
Excel Formula:
=SUBTOTAL(3,A2:A6)
Expected Result: 4
The function number 3 counts non-empty cells, including cells containing text.
Student Task:
Enter Karan in the blank employee cell. Calculate the updated count.
Expected Result: 5
Business Challenge: Filter the Department column to show only Sales. Use the same formula to count the visible employee names.
Expected Result: 2
Question 5: Find the Highest Sales Among Visible Records
Problem Statement:
A sales manager wants to identify the highest sales value for the region currently displayed in the report.
Data:
| Region | Sales (₹) |
|---|---|
| North | 15,000 |
| South | 25,000 |
| North | 35,000 |
| East | 45,000 |
| North | 30,000 |
Assume sales are in cells B2.
Excel Formula:
=SUBTOTAL(4,B2:B6)
Expected Result: ₹45,000
The function number 4 calculates the maximum value.
Student Task:
Filter the Region column to show only North. Calculate the maximum visible sales.
Expected Result: ₹35,000
Business Use: Managers can quickly identify the highest transaction within a selected region or product category.
Question 6: Find the Lowest Price After Filtering Products
Problem Statement:
An inventory manager wants to find the lowest product price among the products currently displayed.
Data:
| Product | Category | Price (₹) |
|---|---|---|
| Keyboard | Accessories | 1,200 |
| Mouse | Accessories | 500 |
| Monitor | Electronics | 8,000 |
| Headphones | Accessories | 1,800 |
| Printer | Electronics | 5,000 |
Assume prices are in cells C2.
Excel Formula:
=SUBTOTAL(5,C2:C6)
Expected Result: ₹500
The function number 5 calculates the minimum value.
Student Task:
Filter the Category column to show only Electronics. Find the minimum visible price.
Expected Result: ₹5,000
Challenge: Change the filter to Accessories. What is the lowest visible price?
Expected Result: ₹500
Question 7: Calculate the Product of Visible Values
Problem Statement:
A company wants to multiply a selected set of numeric values. The report must ignore rows hidden by a filter.
Data:
| Item | Value |
|---|---|
| Item A | 2 |
| Item B | 3 |
| Item C | 4 |
| Item D | 5 |
Assume values are in cells B2.
Excel Formula:
=SUBTOTAL(6,B2:B5)
Expected Result: 120
The function number 6 calculates the product:
2 × 3 × 4 × 5 = 120
Student Task:
Filter the table to show only Item A, Item B, and Item C. Calculate the product of the visible values.
Expected Result: 24
Important: PRODUCT through SUBTOTAL is less common in everyday reports than SUM or AVERAGE, but it demonstrates that SUBTOTAL function supports more than addition.
Question 8: Understand the Difference Between Function Numbers 9 and 109
Problem Statement:
A manager manually hides one employee’s row and wants to understand how the two SUBTOTAL sum options behave.
Data:
| Employee | Sales (₹) |
|---|---|
| Amit | 10,000 |
| Priya | 20,000 |
| Rahul | 30,000 |
| Neha | 40,000 |
Assume sales are in cells B2.
Formula 1:
=SUBTOTAL(9,B2:B5)
Expected Result: ₹100,000
Formula 2:
=SUBTOTAL(109,B2:B5)
Expected Result: ₹100,000
Both formulas return the same result when no rows are hidden.
Student Task:
Manually hide Rahul’s row. Recalculate both formulas.
Expected Results:
=SUBTOTAL(9,B2:B5)→ ₹100,000=SUBTOTAL(109,B2:B5)→ ₹70,000
Key Lesson: Function number 9 includes manually hidden rows, while 109 excludes them. Both ignore rows hidden by filters.
Question 9: Create a Dynamic Sales Total in an Excel Table
Problem Statement:
A company regularly adds new sales records. The manager wants a subtotal formula that expands as the Excel Table grows.
Data:
| Employee | Sales (₹) |
|---|---|
| Amit | 15,000 |
| Priya | 20,000 |
| Rahul | 25,000 |
Convert the data into an Excel Table and name it SalesTable. Ensure the sales column is named Sales.
Excel Formula:
=SUBTOTAL(9,SalesTable[Sales])
Expected Result: ₹60,000
Student Task:
Add Neha with sales of ₹30,000 as a new row in the table. Check the subtotal.
Expected Result: ₹90,000
Why use an Excel Table? Structured references can automatically include new rows, making the formula easier to maintain as the dataset grows.
Question 10: Calculate Visible Sales by Region Without Changing the Formula
Problem Statement:
A company prepares a regional sales report. The manager wants to use one formula and change the displayed region using the filter.
Data:
| Region | Sales (₹) |
|---|---|
| North | 25,000 |
| South | 30,000 |
| East | 15,000 |
| North | 35,000 |
| South | 20,000 |
| East | 40,000 |
Assume sales are in cells B2.
Excel Formula:
=SUBTOTAL(9,B2:B7)
Expected Result Before Filtering: ₹165,000
Now apply a filter to the Region column.
Expected Results by Region:
| Selected Region | Visible Sales Total |
|---|---|
| North | ₹60,000 |
| South | ₹50,000 |
| East | ₹55,000 |
Student Task:
Filter the table to show North and East together.
Expected Result: ₹115,000
Business Challenge: Use the same formula to review sales by region without creating a separate formula for each region.
Question 11: Understand Why SUBTOTAL function Does Not Double-Count Nested Subtotals
Problem Statement:
A report contains sales data with an existing subtotal row. The manager wants to calculate a grand total without counting the subtotal row twice.
Data:
| Description | Amount (₹) |
|---|---|
| Product A | 10,000 |
| Product B | 15,000 |
| Product C | 20,000 |
| Department Subtotal | =SUBTOTAL(9,B2:B4) |
The subtotal formula is in cell B5 and returns ₹45,000.
Grand Total Formula:
=SUBTOTAL(9,B2:B5)
Expected Result: ₹45,000
SUBTOTAL ignores other SUBTOTAL formulas within a referenced range, preventing the subtotal row from being counted twice.
Student Task:
Add Product D with sales of ₹12,000 in cell B6. Update the department subtotal and grand total formulas appropriately.
Expected Result: ₹57,000
Important: Ensure the department subtotal function formula covers the product rows only, and the grand total references the product rows and subtotal row. SUBTOTAL function will ignore the nested subtotal formula.
Student Challenge: Build a Filterable Company Sales Report
A company wants to analyze sales by employee and region.
Data:
| Employee | Region | Sales (₹) |
|---|---|---|
| Amit | North | 25,000 |
| Priya | South | 30,000 |
| Rahul | North | 35,000 |
| Neha | East | 20,000 |
| Karan | South | 40,000 |
| Riya | East | 15,000 |
| Mohan | North | 45,000 |
Your Tasks
- Calculate total sales for all employees.
- Calculate the average sales for all employees.
- Count the numeric sales records.
- Find the highest sales value.
- Find the lowest sales value.
- Filter the Region column to show North only and calculate the visible sales total.
- Filter the Region column to show South only and calculate the visible average sales.
- Manually hide the row for Mohan and compare the results of
SUBTOTAL(9,...)andSUBTOTAL(109,...).
Answer Key
1. Total sales
=SUBTOTAL(9,C2:C8)
Expected result: ₹210,000
2. Average sales
=SUBTOTAL(1,C2:C8)
Expected result: ₹30,000
3. Count numeric sales records
=SUBTOTAL(2,C2:C8)
Expected result: 7
4. Highest sales
=SUBTOTAL(4,C2:C8)
Expected result: ₹45,000
5. Lowest sales
=SUBTOTAL(5,C2:C8)
Expected result: ₹15,000
6. Total sales for North
After filtering the Region column to North:
=SUBTOTAL(9,C2:C8)
Expected result: ₹105,000
7. Average sales for South
After filtering the Region column to South:
=SUBTOTAL(1,C2:C8)
Expected result: ₹35,000
8. Compare manually hidden rows
With Mohan’s row manually hidden and no filter applied:
=SUBTOTAL(9,C2:C8)→ ₹210,000=SUBTOTAL(109,C2:C8)→ ₹165,000
Key Takeaways
- SUBTOTAL in excel performs calculations such as SUM, AVERAGE, COUNT, COUNTA, MIN, and MAX.
- The first argument specifies the calculation to perform.
SUBTOTAL(9,range)calculates the sum and includes manually hidden rows.SUBTOTAL(109,range)calculates the sum while excluding manually hidden rows.- Both options exclude rows hidden by a filter.
- Use
SUBTOTAL(1,range)for the average of visible records. - Use
SUBTOTAL(2,range)to count visible numeric cells. - Use
SUBTOTAL(3,range)to count visible non-empty cells. - SUBTOTAL ignores nested SUBTOTAL formulas in a referenced range.
- Structured references help SUBTOTAL formulas expand with Excel Tables.
Frequently Asked Questions (FAQs)
1. What is the SUBTOTAL function in Excel?
The SUBTOTAL function performs calculations such as sum, average, count, minimum, and maximum on a range of data.
2. What is the syntax of SUBTOTAL?
The syntax is =SUBTOTAL(function_num, ref1, [ref2], ...). The function number determines the calculation.
3. What is the difference between SUM and SUBTOTAL?
SUM adds numeric values in a range, including values in filtered-out rows. SUBTOTAL can ignore rows hidden by filters and supports several calculations beyond addition.
4. What does function number 9 mean in SUBTOTAL?
Function number 9 calculates the sum. It excludes rows hidden by filters but includes manually hidden rows.
5. What does function number 109 mean?
Function number 109 also calculates the sum, but it excludes both filtered-out rows and manually hidden rows.
6. Can SUBTOTAL calculate an average?
Yes. Use =SUBTOTAL(1,B2:B10) to calculate the average of the visible numeric values in a range.
7. Can SUBTOTAL count visible records?
Yes. Use function number 2 to count visible numeric cells or function number 3 to count visible non-empty cells.
8. Does SUBTOTAL ignore filtered rows?
Yes. Rows hidden by an AutoFilter are excluded from SUBTOTAL calculations.
9. Does SUBTOTAL ignore manually hidden rows?
It depends on the function number. Numbers 1–11 include manually hidden rows, while 101–111 exclude them.
10. Why does SUBTOTAL not count another SUBTOTAL formula?
SUBTOTAL ignores nested SUBTOTAL formulas in a referenced range. This helps prevent double-counting when reports contain subtotal rows.

