How to use the SUBTOTAL function in Excel

The following article will guide you to read how to use the SUBTOTAL function in Excel.

SUBTOTAL function in Excel

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

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

Note:

1. The SUBTOTAL function is designed to calculate vertical columns of data.
2. If the arguments ref1, ref2,… contain the SUBTOTAL function, they will be ignored to avoid double duplication.
3. 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).
4. For the data hidden by Filter, SUBTOTAL will ignore it.

How to use the SUBTOTAL function in Excel Picture 1

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.

How to use the SUBTOTAL function in Excel Picture 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.

How to use the SUBTOTAL function in Excel Picture 3

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

How to use the SUBTOTAL function in Excel Picture 4

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

How to use the SUBTOTAL function in Excel Picture 5

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

How to use the SUBTOTAL function in Excel Picture 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.

How to use the SUBTOTAL function in Excel Picture 7

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

How to use the SUBTOTAL function in Excel Picture 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.

How to use the SUBTOTAL function in Excel Picture 9

How to error when executing SUBTOTAL function in Excel

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

1. #VALUE! Function identification numbers are not in the range 1-11 or 101-111 or have reference (ref) that is a 3D reference.
2. # 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)
3. #NAME? The SUBTOTAL function name is misspelled.

How to use the SUBTOTAL function in Excel Picture 10

Marvin Fry

Update 11 September 2020