Home > Formulas > Sum > Subtotal SUBTOTAL Function Examples Use the Excel SUBTOTAL function to summarize amounts, and exclude filtered or hidden rows. For Excel 2010 and later, the Excel AGGREGATE function is also available, and has more options and functions. Note: Microsoft Excel also has a Subtotals feature, that groups the items in a list, and adds one or more subtotal rows. You can find the Subtotal command on the Excel Ribbon, Data tab, in the Outline group.

SUBTOTAL Function

Use the SUBTOTAL function to exclude filtered or hidden rows when calculating a total on the worksheet. You can choose any one of the 11 functions that SUBTOTAL can calculate, such as Sum, Average, Count or Max.

This video show how to use SUBTOTAL or the newer AGGREGATE function, to work with filtered data.

Use SUBTOTAL With Filtered Lists

To show the difference between SUBTOTAL and COUNT, in the screen shot below, there is a list of orders with totals above the list. The list has been filtered to show only the West region’s sales.

Above the list, there are total counts:

• In cell E1, the COUNT function is used, and even though there are only 6 orders visible, the order count is showing as 37.
• In cell E2, the SUBTOTAL function is used, and it shows a count of the visible rows only.

So, if you are working with lists that will be filtered, use SUBTOTAL, instead of the basic functions, such as SUM, COUNT, or AVERAGE. The instructions below show how to use the SUBTOTAL function.

Create a Quick SUBTOTAL

First, here is a quick way to create a SUBTOTAL formula to sum the amounts in a list:

1. Apply a filter to the list. In this example, the Region column is filtered for “West”.
2. In the row immediately below the list, select the cell where you want the grand total.
3. On Excel’s Home tab, click the AutoSum button
   • OR, on the keyboard, press the Alt key and tap the equal sign key (Alt + =).
   • OR, click the Arrow for the AutoSum command, and choose one of the other function names, such as COUNT NUMBERS

Because the list is filtered, Excel inserts a SUBTOTAL formula. It has 109 at the beginning, and that tells Excel to SUM the numbers in the visible cells.

=SUBTOTAL(109,tblProducts[Unit Cost])

NOTE: If the list is a formatted Excel table, it might expand automatically, and put the formula in a Total row. To undo that step, press Ctrl+Z immediately.

SUBTOTAL Syntax Arguments

Within the brackets for the SUBTOTAL function syntax, there are two arguments, separated by a comma (or a semi-colon, depending on your regional settings).

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

• function_num: (required) number that specifies which function to use for the subtotal. See a list of the number options in the next section
• ref1: (required) first range of cells that you want to subtotal
• ref2: (optional) additional ranges of cells to subtotal, from 2 to 254

SUBTOTAL Function Notes

Here are a few notes on using the SUBTOTAL function:

• If there are other SUBTOTAL formulas within the referenced ranges, those nested subtotals are ignored. This will prevent duplication of the amounts in the grand total.
• The SUBTOTAL function does not work with 3-D references, across multiple sheets. If 3-D references are included, the formula result is a #VALUE! error
• The SUBTOTAL function is designed to calculate totals in columns, where it will ignore hidden rows. If you use the SUBTOTAL function to calculate totals across rows, it will NOT ignore the amounts in hidden columns.

SUBTOTAL Arguments Example

As an example of the SUBTOTAL function syntax arguments, here is the formula created by the AutoSum example above:

=SUBTOTAL(109,tblProducts[Unit Cost])

1. function_num: The first argument is a number that tells Excel which summary function to use in the subtotal. The 109 in this examples tells Excel to use the SUM function. See the full list of summary function numbers in the next section
2. ref1: The second argument a reference to the range of cells that should be subtotaled. In this example, it will sum the numbers in the Unit Cost column of the table named tblProducts..

Subtotal Function Numbers

The first argument in the SUBTOTAL function is a function number which specifies how the total should be calculated. When you type the opening bracket for the SUBTOTAL function, a drop down list of 11 functions appears, listed alphabetically. See the full list in the next section.

• To select a function from the list, double-click on it.

11 Functions for Subtotal

The 11 functions are listed in alphabetical order, and each function has two numbers:

1. one in the range of 1 to 11
2. and another in the range of 101 to 111

Function	Numbers
AVERAGE	1 or 101
COUNT	2 or 102
COUNTA	3 or 103
MAX	4 or 104
MIN	5 or 105
PRODUCT	6 or 106
STDV	7 or 107
STDEVP	8 or 108
SUM	9 or 109
VAR	10 or 110
VARP	11 or 111

Function Numbers Ranges

There is one key difference between the two sets of numbers.

• If rows are hidden by a filter
  • the functions in both number ranges will exclude the filtered cells
• If rows are manually hidden:
  • functions in the lower number range (1-11) will include those cells
  • functions in the upper number range (101-111) will exclude those cells

Note: Rows that you format with zero height WILL NOT be included in either type of subtotal.

Flexible SUBTOTAL Formula

Instead of typing a function number into the SUBTOTAL formula, the following example shows how to select a function name from a drop down list.

This makes it easy to see different results quickly, such as Average, Sum, Min or Max, without trying to remember all the function numbers.

Watch this video to see how this technique works, and download the sample file (below), to try it yourself.

The written instructions are on my Contextures blog: Change Excel Function with SUBTOTAL

Get the Sample File

1. To see the SUBTOTAL function examples, download the sample workbook. The zipped file is in xlsx format, and does not contain any macros.
2. To see the example where you choose a function from a drop down list, download the Flexible Subtotal Function workbook. The zipped file is in xlsx format, and does not contain any macros.

More Tutorials

Go to the following pages for more SUBTOTAL examples:

• How to Sum Cells
• How to Count Cells

For Excel 2010 and later, the AGGREGATE function is also available. It is similar to SUBTOTAL, but has more options and functions.

Excel also has a Subtotals feature, that groups the items in a list, and adds one or more subtotal rows.