SUBTOTAL

Database Functions

(4.8/5)

Returns a subtotal in a list or database. Can perform 11 different aggregate functions and optionally ignore hidden rows.

Interactive Formula Tester

Values:

Formula:

=SUBTOTAL("10,20,30,40,50")

Complete Theory & Understanding

Master the fundamentals of Excel SUBTOTAL function

Core Concept

SUBTOTAL performs 11 aggregation operations (SUM, AVERAGE, COUNT, etc.) on ranges of data, with optional capability to ignore hidden rows. Uses function numbers 1-11 to include hidden rows or 101-111 to exclude hidden rows.

Why Use SUBTOTAL?

Analyze visible filtered data
Cascading subtotal reports
Toggle between visible/all data

Key Characteristics

11 Functions

SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, VARP

SUBTOTAL(9, range) = SUM

Hidden Row Control

1-11 include hidden, 101-111 exclude hidden

SUBTOTAL(109, range) excludes hidden

Ignores Nested SUBTOTAL

Doesn't double-count nested subtotals

Multiple SUBTOTALs work correctly

Filter-Friendly

Perfect for filtered data analysis

Use with AutoFilter

Function Anatomy

=SUBTOTAL(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Filtered Lists

Analyze visible filtered data

Dynamic Reporting

Cascading subtotal reports

Data Analysis

Toggle between visible/all data

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=SUBTOTAL(function_num, ref1, ref2, ref3, ...)

Required

function_num:

Number 1-11 or 101-111 specifying function (1-11 include hidden rows, 101-111 exclude hidden rows)

Required

ref1:

First range or reference for subtotal calculation

Optional

ref2, ref3, ...:

Additional ranges (up to 254) to include in subtotal

Returns

Return Value:

Result from specified aggregation function

Description: Calculates subtotals with optional hidden row filtering

Interactive Examples

Sum Including Hidden

Sum values including hidden rows

Input Text:

"Values: {10, 20, 30, 40}"

Formula:

=SUBTOTAL(9, A1:A4)

Result:

100

Uses SUM (9), includes hidden rows

VBA Implementation & Automation

Basic VBA

Simple VBA usage

Sub UseSubtotal()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Sum including hidden rows (function 9)
    ws.Range("C1").Value = Application.WorksheetFunction.Subtotal(9, ws.Range("A1:A100"))
    
    ' Sum excluding hidden rows (function 109)
    ws.Range("C2").Value = Application.Subtotal(109, ws.Range("A1:A100"))
End Sub