The SUBTOTAL function returns the subtotal of the numbers in a column in a list or database.
In simple words, SUBTOTAL Function performs a given calculation (e.g. the sum, product, average, etc.) for a defined range of values.
The SUBTOTAL function is an inbuilt function in Excel. It falls under the category of Math/Trig Functions. It is a worksheet function. You can enter the SUBTOTAL function as part of a formula in a cell of a worksheet.
This function is designed for columns of data or vertical ranges and not for rows of data or horizontal ranges.
Syntax for SUBTOTAL Function
=SUBTOTAL(function_num,ref1,[ref2],…)
Arguments
Function_num: An integer that denotes the calculation type like sum, average, etc. It should be the numbers 1-11 or 101-111 that specifies the function to use for the subtotal.
For the function_num arguments from 1 to 11, the SUBTOTAL function will include the values of rows hidden by the Hide Rows command in excel.
For arguments from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command.
The SUBTOTAL will ignores all cells that are hidden by filtering with Autofilter, regardless of the function_num that is used.
Values of Function_num
1 or 101 is used for AVERAGE.
2 or 102 is used for COUNT.
3 or 103 is used for COUNTA.
4 or 104 is used for MAX.
5 or 105 is used for MIN.
6 or 106 is used for PRODUCT.
7 or 107 is used for STDEV.
8 or 108 is used for STDEVP.
9 or 109 is used for SUM.
10 or 110 is used for VAR.
11 or 111 is used for VARP.
Ref1,[Ref2): One or more references to cells containing the values that the calculation is to be performed on.
The cells containing non-numeric values and blank cells are ignored in the calculation.
We have created the infographics for the SUBTOTAL Function. With the help of these infographics, you can easily learn step by step process for using this excel functions.
Furthermore, Excel consists of many inbuilt functions which are helpful in the analytical and statistical study of number.
Simply follow the instructions in the infographics below:
If ref1, ref2,… includes other subtotals within or nested subtotals, they are ignored to avoid double counting.
Errors
#VALUE! error occurs when the supplied function_num argument is not one of the permitted values. (1-11 or 101-111)
DIV/0! error occurs when required calculation involves a division by zero.
To learn other function like CUMPRINC, ACCRINTM and PRODUCT Functions, please click on the name of the function.
Additionally, you can download other accounting templates like Break Even Analysis Template, Salary Sheet Template and Invoice with GST Template from here.
We thank our readers for liking, sharing and following us on different social media platforms.
If you have any queries please share in the comment section below. I will be more than happy to assist you.
Leave a Reply