How to Use the SUBTOTAL Function in Excel

This guide explains how to use SUBTOTAL Function in Excel, including its syntax, required arguments, practical examples, and common errors.

SUBTOTAL function in Excel

The SUBTOTAL function has the syntax = SUBTOTAL (function_num, ref1, [ref2]. ) . Inside:

Function_num: The numbers 1 through 11 and 101 through 111 specify which function will be used for calculation in SUBTOTAL.
Ref1, Ref2..: 1 or more cells, or range of cells for subtotaling, up to 254.

Note:

The SUBTOTAL function is designed to calculate vertical columns of data.
If the arguments ref1, ref2.. contain the SUBTOTAL function, they will be ignored to avoid double duplication.
If function_num is between 1 and 11, the SUBTOTAL function calculates including hidden values in the dataset because the row containing the range is hidden. If function_num is 101 through 111, then SUBTOTAL calculates only values that are not hidden in the data set (hidden row values are excluded).
For the data hidden by Filter, SUBTOTAL will ignore it.

SUBTOTAL function in Excel screenshot

The example uses the SUBTOTAL function

1. Sum the filtered rows

To understand this type of sum, refer to the article Calculate a filtered list value in Excel.

2. Count filtered cells that are not blank

We will use SUBTOTAL 3 or SUBTOTAL 103. However, if there is a hidden row, you must use SUBTOTAL 103 to count the cells that are not blank.

The example uses the SUBTOTAL function screenshot 2

For example, the above data table will hide 2 rows, 4 and 5. Using SUBTOTAL 3 or SUBTOTAL 103 will produce 2 different results.

The example uses the SUBTOTAL function screenshot 3

We enter formulas and then Excel will automatically display the set of functions for you to choose from without having to remember.

The example uses the SUBTOTAL function screenshot 4

The result using SUBTOTAL 3 will yield 3, including hidden cells in the row.

The example uses the SUBTOTAL function screenshot 5

The SUBTOTAL 103 will only show the nonblank cells we see, ignoring the hidden row.

The example uses the SUBTOTAL function screenshot 6

3. Ignore values in nested Subtotal formulas

For example, we will calculate the average total kg of fabric of warehouses A1 and A2.

The average formula for the warehouse A2 = SUBTOTAL (1, C2: C4) and gives the result 19.

The example uses the SUBTOTAL function screenshot 7

The average formula for the warehouse A1 = SUBTOTAL (1, C5: C7) will yield 38.

The example uses the SUBTOTAL function screenshot 8

However, when averaging the total number of fabrics in 2 warehouses, the previous average result will be ignored. We have the formula = SUBTOTAL (1, C2: C9) and the result will automatically remove the average result. Binh calculated earlier.

The example uses the SUBTOTAL function screenshot 9

How to error when executing SUBTOTAL function in Excel

When you calculate the SUBTOTAL function in Excel, some basic errors are encountered:

#VALUE! Function identification numbers are not in the range 1-11 or 101-111 or have reference (ref) that is a 3D reference.
# DIV / 0! Occurs when a specific sum must be divided by 0 (for example, the arithmetic mean or the standard deviation of a range of cells with no numeric values)
#NAME? The SUBTOTAL function name is misspelled.

How to error when executing SUBTOTAL function in Excel screenshot 10

FAQ

What does the SUBTOTAL function do in Excel?

The SUBTOTAL function performs the calculation described in this guide. Use the correct arguments and compatible data types for reliable results.

How do I enter the SUBTOTAL function correctly?

Start with an equals sign, enter SUBTOTAL, add the required arguments inside parentheses, and confirm that cell references and separators are correct.