An Overview of SUBTOTAL Function | Microsoft Excel

The SUBTOTAL function is one of the math and trigonometry functions in Microsoft Excel. It can be used as a standalone function as well as in collaboration with the other functions. This article will give you an overview of the SUBTOTAL function in Excel.

Objectives

The SUBTOTAL function can calculate summation, average, count, maximum, minimum, multiplication, etc. within a specified sub-range. Its characteristics are controlled by its first argument named function_num. The value of the function_num can be either 1-11 or 101-111.

Syntax

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

Arguments

• function_num: It accepts a number within 1-11 or 101-111. The number specifies one of the 11 sub-functions. To include the manually hidden rows, you have to insert a number from 1-11. To exclude the hidden rows, you have to insert a number from 101-111. One important thing is this function always ignores the filtered-out rows.
• ref1: The first sub-range within which you want to calculate the sub-total.
• ref2: Non-compulsory. The second sub-range is within which you want to calculate the sub-total. The SUBTOTAL function supports up to 255 sub-ranges to calculate the subtotal.

Function Numbers

Sub-Functions

Include Hidden Rows

Exclude Hidden Rows

AVERAGE

1

101

COUNT

2

102

COUNTA

3

103

MAX

4

104

MIN

5

105

PRODUCT

6

106

STDEV

7

107

STDEVP

8

108

SUM

9

109

VAR

10

110

VARP

11

111

Output

A numerical value.

Examples of SUBTOTAL Function

Suppose, you have a series of numbers in the range A2:A7. You also have another series of numbers in the range A8:A12. Now you want to count them all. In this particular case, you can use the SUBTOTAL function to count all the items from two distinct ranges.  Just use the following formula.

=SUBTOTAL(2,A2:A7,A8:A12)

This formula will count all the numbers in two distinct ranges.

Now here are more examples of the SUBTOTAL function. Take a look.

Usage Guide of SUBTOTAL Function

Step_1: Start with inserting an equal sign (=) in a blank cell.

Step_2: Then type SUBTOTAL.

Step_3: Type open parenthesis “(“.

As you type the open parenthesis, Excel shows the syntax of the SUBTOTAL function.

You will also see the list of all the sub-functions of the SUBTOTAL function.

Step_4: Insert a number to refer to one of the sub-functions.

For this instance, I’m inserting 2 to refer to the COUNT function.

Step_5: Then insert a comma (,).

After inserting the comma, Excel will ask you for the second argument which is ref1.

Step_6: Now insert the first range to count.

Here I’m inserting the range A2:A4.

Step_7: Then insert a comma (,) again.

Step_8: Now insert the second range as ref2 to count.

Here I’m inserting the range B2:B4.

In this way, you can insert a total of 255 distinct ranges.

Step_9: Finally, add a closing parenthesis “)” to complete the syntax.

Step_10: Now, press ENTER.

The SUBTOTAL function will return a numerical value (6) which is the count number of the non-blank cells of the given two ranges.

Availability

Remarks

• The SUBTOTAL function returns a #VALUE! Error, you if insert a function number other than 1-11 or 101-111.
• It includes all the manually hidden rows for the function_num 1-11.
• It ignores all the manually hidden rows for the function_num 101-111.
• The SUBTOTAL function always ignores the filtered out rows, no matter what the function_num
• For any 3-D references, the SUBTOTAL function returns a #VALUE!
• This function ignores all the nested SUBTOTAL

Conclusion

The SUBTOTAL function is one of the math and trigonometry functions in Excel. If you have any questions regarding the SUBTOTAL function, please comment below. Thanks!