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:

SUBTOTAL function - How to use the Excel SUBTOTAL function

Syntax:  =SUBTOTAL(1,D2:D12)

Result:

Excel SUBTOTAL function - How to use the Excel SUBTOTAL function

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

Syntax: =SUBTOTAL(2,D2:D12)
Result: 11

Syntax: =SUBTOTAL(3,D2:D12)
Result: 11

Syntax: =SUBTOTAL(4,D2:D12)
Result: 180

Syntax: =SUBTOTAL(5,D2:D12)
Result: 30

Syntax: =SUBTOTAL(6,D2:D12)
Result: 1.0538E+21

Syntax: =SUBTOTAL(7,D2:D12)
Result: 48.10877826

Syntax: =SUBTOTAL(8,D2:D12)
Result: 45.86992028

Syntax: =SUBTOTAL(9,D2:D12)
Result: 1027

Syntax: =SUBTOTAL(10,D2:D12)
Result: 2314.454545

Syntax: =SUBTOTAL(11,D2:D12)
Result: 2104.049587

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.

Add a Comment

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