How to use the Excel SUBTOTAL function

Microsoft defines Excel SUBTOTAL as the function that returns a subtotal in a list or database. In this context, “subtotal” is not just totaling numbers in a defined range of cells. Unlike other Excel functions that are designed to do only one specific thing, SUBTOTAL is amazingly versatile – it can perform different arithmetic and logical operations such as counting cells, calculating average, finding the minimum or maximum value, and more.

Syntax: =SUBTOTAL(function_num,ref1,[ref2],…)

The SUBTOTAL function syntax has the following arguments:

• Function_num – a number that specifies which function to use for the subtotal.
• Ref1, Ref2, … – one or more cells or ranges to subtotal. The first ref argument is required, others (up to 254) are optional.

The function_num argument can belong to one of the following sets:

• 1 – 11 ignore filtered-out cells, but include manually hidden rows.
• 101 – 111 ignore all hidden cells – filtered out and hidden manually.

Function_num		Function	Description
1	101	AVERAGE	Returns the average of numbers.
2	102	COUNT	Counts cells that contain numeric values.
3	103	COUNTA	Counts non-empty cells.
4	104	MAX	Returns the largest value.
5	105	MIN	Returns the smallest value.
6	106	PRODUCT	Calculates the product of cells.
7	107	STDEV	Returns the standard deviation of a population based on a sample of numbers.
8	108	STDEVP	Returns the standard deviation based on an entire population of numbers.
9	109	SUM	Adds up the numbers.
10	110	VAR	Estimates the variance of a population based on a sample of numbers.
11	111	VARP	Estimates the variance of a population based on an entire population of numbers.

Example: Let’s look at some Excel SUBTOTAL function examples and explore how to use the SUBTOTAL function as a worksheet function in Microsoft Excel:

Syntax:  =SUBTOTAL(1,D2:D12)

Result:

Based on the Excel spreadsheet above, the following SUBTOTAL examples would return:

Note:

• 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.