# SUBTOTAL

SUBTOTAL(function_num, ref1 [,ref2] [..])

Returns the subtotal of values in a list, table or cell range (renamed).

 function_num The numbers specify which function to use: 1 to 11 will include hidden rows, 101 to 111 will exclude hidden rows:1,101 = AVERAGE2,102 = COUNT3,103 = COUNTA4,104 = MAX5,105 = MIN6,106 = PRODUCT7,107 = STDEV8,108 = STDEVP9,109 = SUM10,110 = VAR11,111 = VARP ref1 The first reference you want to subtotal. ref2 (Optional) The second reference.

 REMARKS
 * AGGREGATE was added in Excel 2010 to replace this function.* For an illustrated example refer to the page under Advanced Functions* Using the function numbers 1 to 11 will include hidden rows.* Using the function numbers 101 to 111 will exclude hidden rows.* Rows can be hidden using AutoFilter, Hide Rows, Grouping or Nested Subtotals* Nested functions are typically used in conjunction with Grouping.* If any of the references are 3-D references, then #VALUE! is returned. * You can have a maximum of 255 arguments.* You can use the AVERAGE function to return the arithmetic mean of the numbers in a list, table or cell range.* You can use the COUNT function to return the number of numerical values in a list, table or cell range.* You can use the COUNTA function to return the number of values that are not empty cells.* You can use the SUM function to return the total value of the numbers in a list, table or cell range.* In Excel 2003 the function numbers 101 to 111 were added.* For the Microsoft documentation refer to support.microsoft.com* For the Google documentation refer to support.google.com

 A B 1 =SUBTOTAL(1, B1:B6) = 30 10 2 =SUBTOTAL(2, B1:B6) = 5 20 3 =SUBTOTAL(3, B1:B6) = 5 30 4 =SUBTOTAL(4, B1:B6) = 50 40 5 =SUBTOTAL(5, B1:B6) = 10 50 6 =SUBTOTAL(6, B1:B6) = 12000000 7 =SUBTOTAL(7, B1:B6) = 16 8 =SUBTOTAL(8, B1:B6) = 14 9 =SUBTOTAL(9, B1:B6) = 150 10 =SUBTOTAL(10, B1:B6) = 250 11 =SUBTOTAL(11, B1:B6) = 200 12 =SUBTOTAL(11, B1, B2, B3, B4, B5, B6) = 200 13 =SUBTOTAL(111, B1, B2, B3, B4, B5, B6) = 200 14 =SUBTOTAL(12, B1:B6) = #VALUE!