SUBTOTAL

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

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

function_numThe numbers specify which function to use: 1 to 11 will include hidden rows, 101 to 111 will exclude hidden rows:
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
ref1The 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 averge 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 numerical values in a list, table or cell range (including logical values and text).
* 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

 AB
1=SUBTOTAL(1,B1:B6) = 3010
2=SUBTOTAL(2,B1:B6) = 520
3=SUBTOTAL(3,B1:B6) = 530
4=SUBTOTAL(4,B1:B6) = 5040
5=SUBTOTAL(5,B1:B6) = 1050
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! 


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited Top