Math functions

SUBTOTAL function in Excel

SUBTOTAL totals, counts or averages a list while ignoring rows hidden by a filter.

Filter a list to show only one region, and the SUM at the bottom still shows the total for every region. SUBTOTAL is the cure. It works out its answer from the rows you can see, so the total follows the filter.

The examples above were run without a filter, where SUBTOTAL gives the same answers as the ordinary functions. Its value shows when you filter the list.

Syntax

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

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

• =SUBTOTAL(9,F2:F13)Copy
  Result: 18700

  Code 9 is a sum. With no filter applied it equals SUM.

• =SUBTOTAL(1,F2:F13)Copy
  Result: 1558.333333

  Code 1 is an average.

• =SUBTOTAL(3,B2:B13)Copy
  Result: 12

  Code 3 counts filled cells: the number of sales.

• =SUBTOTAL(4,F2:F13)Copy
  Result: 3750

  Code 4 is the largest value.

• =SUBTOTAL(5,F2:F13)Copy
  Result: 300

  Code 5 is the smallest.

• =SUBTOTAL(109,F2:F13)Copy
  Result: 18700

  Code 109 is also a sum, and it leaves out rows hidden by hand as well as filtered rows.

The function codes

The codes 7, 8, 10 and 11 give standard deviations and variances.

Filtered rows and hidden rows

There are two ways a row can disappear from view, and the two sets of codes treat them differently.

• Rows removed by a filter are ignored by both sets of codes.
• Rows hidden by hand (right-click, Hide) are ignored only by the codes from 101 upwards.

If you are not sure which you need, use the 100 series. =SUBTOTAL(109,F2:F13) is the safe "total of what is visible".

It ignores other subtotals

A list often has a subtotal after each group and a grand total at the bottom. If the grand total were a SUM, it would count every group twice: once in the rows and again in the group subtotals. SUBTOTAL skips any other SUBTOTAL formulas inside its range. So you can write one SUBTOTAL per group and one over the whole column, and the grand total is right.

The Subtotal command on the Data tab writes these formulas for you after you sort the list by the grouping column.

In an Excel table

When a range is formatted as a table (Ctrl and T) and the Total Row is switched on, the total cell contains a SUBTOTAL formula. That is why totals in tables follow the filter buttons in the headings. The drop-down arrow in the total cell changes the code between sum, average, count and the rest.

Numbering visible rows

=SUBTOTAL(3,$B$2:B2) in the first data row, copied down, numbers the visible rows 1, 2, 3 without gaps, however the list is filtered. Code 3 counts filled cells, and the range grows by one row with each copy.

AGGREGATE

A newer function, AGGREGATE, does everything SUBTOTAL does and can also skip error values, which SUBTOTAL cannot. If a column contains #N/A here and there, =AGGREGATE(9,6,F2:F13) adds the rest.

Common mistakes

• Using codes 1 to 11 and expecting hand-hidden rows to be left out.
• Using SUBTOTAL on data laid out across a row. It is designed for columns, and hiding columns does not affect it.
• Mixing up the codes. 9 is sum and 3 is count; it is worth checking against the table.

In Google Sheets

SUBTOTAL exists in Google Sheets with the same codes.

Related functions

• SUMMath
• AVERAGE and MEDIANStatistical
• COUNT, COUNTA and COUNTBLANKStatistical
• FILTERDynamic arrays

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.