SUBTOTAL Function in Excel Tutorial

Excel Tutorial

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 NumberCalculationFunction NumberCalculation
1AVERAGE101AVERAGE
2COUNT102COUNT
3COUNTA103COUNTA
4MAX104MAX
5MIN105MIN
6PRODUCT106PRODUCT
7STDEV.S107STDEV.S
8STDEV.P108STDEV.P
9SUM109SUM
10VAR.S110VAR.S
11VAR.P111VAR.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:

EmployeeSales (₹)
Amit12,000
Priya18,000
Rahul15,000
Neha25,000
Karan20,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:

EmployeeRegionSales (₹)
AmitNorth10,000
PriyaSouth20,000
RahulNorth30,000
NehaEast40,000
KaranNorth50,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:

EmployeeSales (₹)
Amit12,000
Priya18,000
Rahul(Blank)
Neha25,000
Karan20,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:

EmployeeDepartment
AmitSales
PriyaHR
RahulIT
NehaSales
(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:

RegionSales (₹)
North15,000
South25,000
North35,000
East45,000
North30,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:

ProductCategoryPrice (₹)
KeyboardAccessories1,200
MouseAccessories500
MonitorElectronics8,000
HeadphonesAccessories1,800
PrinterElectronics5,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:

ItemValue
Item A2
Item B3
Item C4
Item D5

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:

EmployeeSales (₹)
Amit10,000
Priya20,000
Rahul30,000
Neha40,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:

EmployeeSales (₹)
Amit15,000
Priya20,000
Rahul25,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:

RegionSales (₹)
North25,000
South30,000
East15,000
North35,000
South20,000
East40,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 RegionVisible 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:

DescriptionAmount (₹)
Product A10,000
Product B15,000
Product C20,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:

EmployeeRegionSales (₹)
AmitNorth25,000
PriyaSouth30,000
RahulNorth35,000
NehaEast20,000
KaranSouth40,000
RiyaEast15,000
MohanNorth45,000

Your Tasks

  1. Calculate total sales for all employees.
  2. Calculate the average sales for all employees.
  3. Count the numeric sales records.
  4. Find the highest sales value.
  5. Find the lowest sales value.
  6. Filter the Region column to show North only and calculate the visible sales total.
  7. Filter the Region column to show South only and calculate the visible average sales.
  8. Manually hide the row for Mohan and compare the results of SUBTOTAL(9,...) and SUBTOTAL(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.