SUBTOTAL

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

Returns the choice of 11 different operations for numerical values.

function_numThe type of function to use:
1,101 = AVERAGE
2,102 = COUNT
3,103 = COUNTA
4,104 = MAX
5,105 = MIN
6,106 = PRODUCT
7,107 = STDEV.S
8,108 = STDEV.P
9,109 = SUM
10,110 = VAR.S
11,111 = VAR.P
ref1The first reference you want to subtotal.
ref2(Optional) The second reference.

REMARKS
* AGGREGATE was added in Excel 2010 to replace this function.
* This function is only available for backwards compatibility and should not be used.
* For an illustrated example refer to the page under Summing Functions
* Rows can be hidden Manually or by using AutoFilter, Grouping or Nested Subtotals
* All the numbers will exclude rows hidden by AutoFilter.
* The numbers 1 to 11 will include rows hidden by Manual Formatting and Grouping.
* The numbers 101 to 111 will not include rows hidden by Manual Formatting and Grouping.
* If any of the references are 3D references, then #VALUE! is returned.
* You can have a maximum of 255 arguments.
* For the Microsoft documentation refer to support.microsoft.com

 AB
1=SUBTOTAL(1, B1:B8) = 85
2=SUBTOTAL(2, B1:B8) = 410
3=SUBTOTAL(3, B1:B8) = 715
4=SUBTOTAL(4, B1:B8) = 15 
5=SUBTOTAL(5, B1:B8) = 00
6=SUBTOTAL(6, B1:B8) = 0text
7=SUBTOTAL(9, B1:B8) = 30True
8=SUBTOTAL(9, B1, B2, B3, B4) = 30False
9=SUBTOTAL(9, B9) = 001 Jan 2025
10=SUBTOTAL(12, B1:B8) = #VALUE!=10/0 = #DIV/0!
11=SUBTOTAL(9, B1:B10) = #DIV/0! 

1 - What is the average of the values in the range "B1:B8". ((30 / 4) = 7.5)
2 - What is the count of the values in the range "B1:B8". The empty cell, text and boolean values are ignored.
3 - What is the counta of the values in the range "B1:B8". The empty cell is ignored but the text and logical values are included.
4 - What is the max of the values in the range "B1:B8".
5 - What is the min of the values in the range "B1:B8".
6 - What is the product of the values in the range "B1:B8". ((5 x 10 x 15 x 0) = 0)
7 - What is the sum of the values in the range "B1:B8". ((5 + 10 + 15 + 0) = 30)
8 - What is the sum of the values using individual cell references.
9 - When dates are referenced, the date serial number is used.
10 - When the function_name is not valid.
11 - What is the sum of the values including a cell with an error value.

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