Cellwise Start free

Math function

Excel SUBTOTAL function

SUBTOTAL runs one of eleven calculations, such as SUM, AVERAGE or COUNT, on a range. Unlike SUM, it skips any cell that holds another SUBTOTAL, so a grand total over a list with group subtotals in it does not count them twice. It also ignores rows hidden by a filter, which makes it the standard total for filtered tables.

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

Arguments

function_num
Which calculation: 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 7 STDEV.S, 8 STDEV.P, 9 SUM, 10 VAR.S, 11 VAR.P. 101 to 111 do the same and also skip rows you hid by hand.
ref1
The first range to calculate.
ref2
Optional. More ranges, up to 254.

SUBTOTAL examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1RegionRepSales ($)
2EastAna4200
3EastBen3100
4EastCho2800
5East total10100
6WestDev5100
7WestEli3900
8West total9000

Download this sheet as an Excel practice file (.xlsx)

  1. =SUBTOTAL(9,C2:C8)

    Result: 19100

    The grand total is 19,100. The two subtotal cells in C5 and C8 are skipped, so nothing is counted twice.

  2. =SUM(C2:C8)-SUBTOTAL(9,C2:C8)

    Result: 19100

    SUM over the same range adds the subtotals too and comes out 19,100 too high: exactly the double count SUBTOTAL avoids.

  3. =SUBTOTAL(1,C2:C8)

    Result: 3820

    Function 1 averages the five reps: 3,820. AVERAGE would include the subtotals and give a meaningless number.

  4. =SUBTOTAL(2,C2:C8)

    Result: 5

    Function 2 counts the numbers, again without the subtotal cells: 5 reps.

How to use SUBTOTAL

  1. Click the cell for the total, usually below the list.
  2. Type =SUBTOTAL( and pick a function number, 9 for a sum.
  3. Type a comma and select the whole column of data, subtotals included.
  4. Press Enter. Data > Subtotal inserts SUBTOTAL formulas for every group for you.

Common SUBTOTAL mistakes

Using SUM for the grand total

SUM adds the subtotal rows as well, doubling the total. Use SUBTOTAL(9,...) for every total in a list that has subtotals.

Expecting 9 to skip hidden rows

Rows hidden by a filter are always skipped. Rows hidden by hand (right-click > Hide) are only skipped with 101 to 111, such as SUBTOTAL(109,...).

Wrong function number

SUBTOTAL accepts 1 to 11 and 101 to 111 only; anything else returns #VALUE!. For MEDIAN, LARGE or ignoring errors, use AGGREGATE.

SUBTOTAL FAQ

What is the difference between SUBTOTAL 9 and 109?

Both sum and skip rows hidden by a filter. 109 also skips rows you hid manually; 9 still includes them.

How do I sum only visible cells in Excel?

Use =SUBTOTAL(109,range). It ignores filtered-out rows and rows you hid by hand.

Does SUBTOTAL ignore errors?

No. An error in the range makes SUBTOTAL return that error. AGGREGATE with option 6 ignores errors.

How do I count visible rows after filtering?

Use =SUBTOTAL(103,A2:A100). Function 3 is COUNTA, so it counts filled cells in the visible rows.

Guides that use SUBTOTAL

Related functions

Updated