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.

Examples of SUBTOTAL Function in Excel

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.

Usage Guide of SUBTOTAL Function in Excel

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.

Usage Guide of SUBTOTAL Function in Excel

Step_6: Now insert the first range to count.

Here I’m inserting the range A2:A4.

Step_7: Then insert a comma (,) again.

Usage Guide of SUBTOTAL Function in Excel

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

  • Excel for Microsoft 365
  • Excel 2016
  • Excel for Microsoft 365 for Mac
  • Excel 2016 for Mac
  • Excel for the web
  • Excel 2013
  • Excel 2021
  • Excel 2010
  • Excel 2021 for Mac
  • Excel 2007
  • Excel 2019
  • Excel for Mac 2011
  • Excel 2019 for Mac
  • Excel Starter 2010

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!

(Visited 31 times, 1 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *