SUBTOTAL(function_num, ref1 [,ref2])

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


function_numThe number 1 to 11 that specifies which function to use in calculating subtotals within a list:
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.
* Rows are always excluded when they are hidden using AutoFilter
* Rows are always excluded when they are hidden using Hide Rows
* Rows are always excluded when they are hidden using Grouping
* If any of the references are 3-D references, then #VALUE! is returned.
* Nested functions are typically used in conjunction with Grouping.
* Rows are always excluded when there are Nested Subtotals
* You can have a maximum of 255 arguments.
* In Excel 2007 the function numbers 1 to 11 and 101 to 111 all do exactly the same thing.
* In Excel 2007 if you are using the numbers 101 to 111 these should be replaced with numbers 1 to 11.
* In Excel 2003 the function numbers 101 to 111 were added.
* For the Microsoft documentation refer to support.office.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! 


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