Excel SUBTOTAL function

Function              SUBTOTAL

Description        Excel SUBTOTAL function is used to get a subtotal in a list or database. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM VAR VARP. SUBTOTAL function has the feature to exclude or include values in hidden rows.

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

function_num  A number to specify which function to use when getting subtotals within a list. I have included a table below.

Function_num
(includes hidden values)		 Function_num
(ignores hidden values)		 Function
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

 ref1  A range or reference to subtotal.

ref2  [optional] A range or reference to subtotal.

Usage Notes:

  • The SUBTOTAL function ignores any rows that are not in the result of a filter.
  • The SUBTOTAL function works for columns of data, or vertical ranges. It don’t work for rows of data, or horizontal ranges.