Excel functions / Math

SUBTOTAL function

Totals a range, leaving out rows that a filter hides and other subtotals inside it.

Syntax

=SUBTOTAL(function_num, ref1, [ref2], …)

Arguments

When to use it

SUBTOTAL totals a list the way SUM does, with two differences that make it the right choice under a filtered table. It leaves out the rows a filter hides, and it leaves out other SUBTOTAL formulas in its range, so subtotals and a grand total can sit in the same column.

The first argument is a number that picks the calculation: 9 adds up, 1 averages, 2 counts numbers, 3 counts cells that are not empty, 4 is the largest and 5 the smallest. Add 100 (109, 101…) to also leave out rows you hid by hand.

Examples

  1. A total that follows the filter

    ABCDE
    1RegionRepSales
    2NorthAna5002420
    3SouthBen1200
    4NorthChloe80
    5WestDev640

    In E2: =SUBTOTAL(9, C2:C5) → 2420

    With no filter on, this is the same as SUM. Filter the Region column to North and it shows 580, where =SUM(C2:C5) would still show 2420.

    Try this example

  2. Count the rows that show

    ABCDE
    1RegionRepSales
    2NorthAna5004
    3SouthBen1200
    4NorthChloe80
    5WestDev640

    In E2: =SUBTOTAL(3, A2:A5) → 4

    3 counts cells that are not empty, like COUNTA. Under a filter it becomes "how many rows match".

    Try this example

  3. Subtotals and a grand total in one column

    AB
    1ItemAmount
    2Pens10
    3Paper20
    4Stationery=SUBTOTAL(9, B2:B3)
    5Ink5
    6Toner15
    7Printing=SUBTOTAL(9, B5:B6)
    850

    In B8: =SUBTOTAL(9, B2:B7) → 50

    =SUM(B2:B7) would give 100, because it would add the two subtotals as well as the items.

    Try this example

Common mistakes

In Excel, the Total Row of a Table and the Alt+= shortcut under a filtered list both write a SUBTOTAL formula for you.

Common questions

What does 9 mean in SUBTOTAL?

The first argument chooses the calculation, and 9 means sum. So =SUBTOTAL(9, C2:C100) adds up C2:C100, leaving out rows that a filter hides. 1 is average, 2 is count, 3 is count of non-empty cells, 4 is max and 5 is min.

How do I sum only the visible cells in Excel?

Use =SUBTOTAL(109, C2:C100). It adds only the rows that show, whether the others were hidden by a filter or by hand. =SUBTOTAL(9, …) does the same for filtered rows only.

What is the difference between SUBTOTAL and SUM?

SUM adds every cell in the range. SUBTOTAL leaves out rows hidden by a filter and any other SUBTOTAL formulas in the range, and it can also average, count, or find the largest and smallest.

SUBTOTAL in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Try SUBTOTAL in the playground

Practise it

Related functions