SUBTOTAL Function in Excel

Excel Subtotal function is an inbuilt function which is used to give us the subtotal of a given data table or data sets, this formula takes two arguments one is the function number and the second is the range, range can be multiple, excel has preset of function number which we need to remember before using this function as 1 represents average and 2 represents count and so on.

Syntax

The SUBTOTAL formula accepts the following parameters and arguments:

function_num – This is the number that specifies whicfunction should be used for the calculations of the subtotals within a list.
ref1– This is a named range or a reference to the subtotal.
ref2 – This is optional. This is the named range or reference to the subtotal.
Return Value: The return value will be a numeric value, which represents a specific kind of subtotal.

Usage Notes

The SUBTOTAL function is used to get a subtotal in a list or a database.
The SUBTOTAL can use different functions when subtotaling, which includes AVERAGE, COUNT, MAX, and others (all the functions are listed below).
The SUBTOTAL Function excludes values in the row, which is hidden by a filter, and it is the default.
The SUBTOTAL function is a very useful function in Microsoft Excel.

Available Functions for SUBTOTAL

As stated above, the SUBTOTAL function can use different functions when subtotaling; here is the list of available functions with including or excluding values in the hidden rows. The behavior of the SUBTOTAL function is controlled by the argument “function_num.” See the table below –

Function	function_num Includes hidden values	function_num Excludes hidden values
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

Manually Hidden Values in SUBTOTAL Function

You must have noticed that the values which are used for function_num argument are paired in the above table, for example, 1-101, 2-102, 3-103, and so on. This is in relation to how the SUBTOTAL function deals with manually hidden rows.

Let’s make it clearer.

When the value of function_num is between 1-11, the SUBTOTAL function will include cells which are manually hidden.
When the value of function_num is between 101-111, the SUBTOTAL function will exclude or ignore the values in rows that are manually hidden.

Always remember that the SUBTOTAL function excludes the values in the cells, which are hidden with a filter. Regardless of function_num, the values which are present in the ‘filtered out’ rows are excluded.

How to Open the SUBTOTAL Function in Excel?

You can download this SUBTOTAL Function Excel Template here – SUBTOTAL Function Excel Template

You can simply enter the desired SUBTOTAL formula in the required cell to attain a return value on the argument.
You can manually open the SUBTOTAL formula dialogue box in the spreadsheet and enter the logical values to attain a return value.
Consider the screenshot below to see the SUBTOTAL option under the Math & Trig Function menu.
Click on the SUBTOTAL option. The SUBTOTAL formula dialogue box will open where you can put the argument values to obtain a return value.

How to use the SUBTOTAL Function in Excel? (with Examples)

Let’s look below at some of the examples of the SUBTOTAL function. These examples will help you in exploring the use of the SUBTOTAL function.

Based on the above Excel spreadsheet, let’s consider three examples and see the SUBTOTAL function return based on the SUBTOTAL Formula in Excel.

Consider the below screenshots of the above examples for clear understanding.

Example #1

Example #2

Example #3

Example #4

Example #5

Example #6

Example #7

Example #8

Example #9

Example #10

Example #11

Errors in SubTotal Function

If you get any kind of error from the SUBTOTAL Function in excel, then it can be any one of the following –

#VALUE! – This error occurs if the given function_num argument is not one of the permitted values (i.e., between 1 -11 or 101-111).
#DIV/0! – This error occurs of the required calculation is involving a division by numerical 0. The required calculation can be anything, for example, finding the average, standard deviation, or variance for a range of cells which are not containing any numeric value.

Things to Remember

The SUBTOTAL Function returns the subtotal of the numbers which are present in a column of a list or a database.
The SUBTOTAL function has the ability to return a SUM, AVERAGE, COUNT, MAX, and others with either including or excluding values in hidden rows.
When the value of function_num is between 1-11, the SUBTOTAL function will include the hidden values.
When the value of function_num is between 101-111, the SUBTOTAL function will exclude or ignore the hidden values.
In filtered lists, the SUBTOTAL will always ignore the values which are present in the hidden rows, which is regardless of function_num.
The SUBTOTAL Function ignores other subtotal value which already exists in references. It is done to prevent the error of double counting.
The Excel SUBTOTAL Function is designed for the calculation of vertical data value, which is arranged vertically in the excel sheet. In horizontal ranges, the values in the hidden columns will always be included.

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST

SUBTOTAL Excel Function