Contextures

SUBTOTAL Function Examples

Use the SUBTOTAL function to summarize amounts, and exclude filtered or hidden rows. For Excel 2010 and later AGGREGATE function is also available, and has more options and functions.

SUBTOTAL Function

Use the SUBTOTAL function to exclude filtered or hidden rows when calculating. This video shows how to choose different functions for the SUBTOTAL function, from a drop down list on the worksheet. There are details on the SUBTOTAL function, below the video.

Why Use SUBTOTAL?

In Excel, you might have a long list of orders with a grand total at the end. If you filter the Region column, so the list only shows one region’s sales, you’d like the total to include only those items.

If you used the SUM function in the grand total cell, the result won’t change if a filter is applied. This list is filtered to show orders from the West Region. At a glance, you can see that the Grand Total is much higher than the records listed. There are only 3 orders visible, but the order count is calculated as 49.

Instead of SUM or COUNT, use the SUBTOTAL function, and only the filtered rows will be included in the grand total.

Create a Quick SUBTOTAL

A quick way to create a SUBTOTAL formula is to:

  1. Apply a filter to the list. In the above example, the Region column is filtered for “West”.
  2. 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 + =).

AutoSum button on Home tab

Because the list is filtered, a SUBTOTAL formula is inserted, instead of a SUM formula.

AutoFilterSubtotal

Read a SUBTOTAL formula

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

  1. The first argument is a number that tells Excel which summary function to use in the subtotal. Most of the time you’ll use a 9, which is the SUM function. Read more about these numbers in the next section
  2. The second argument is the range of numbers that should be subtotaled. In this example, cells H2:H50 are the cells that we want to sum.

SUBTOTAL function arguments

Subtotal Function Numbers

The first argument in the SUBTOTAL function is a function number, that specifies how the numbers should be calculated. When you type the opening bracket for the SUBTOTAL function, the list of functions will appear.

There are 11 functions, listed alphabetically, with numbers 1-11, and again, with numbers 101-111.

sum visible filtered

11 Functions for Subtotal

The table below shows the 11 functions that you can use as the first argument in the SUBTOTAL function. Each function has two numbers:

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

Note that the list is alphabetical, so that might help you remember the number that you use most often.

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

The two number ranges contain the same functions, but there is one key difference between them.

  • 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.

More Information

There are more SUBTOTAL examples on the following pages:

For Excel 2010 and later 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.

More Tutorials

Functions List

30 Functions in 30 Days

AGGREGATE

Subtotals feature

SUM / SUMIF

VLOOKUP

Custom Search

 

 

30 Excel Functions in 30 Days

 

 

 

30 Excel Functions in 30 Days

 

 

 

30 Excel Functions in 30 Days

 

 

 

Fast Excel

 


Last updated: November 12, 2017 11:35 AM