SUBTOTAL
Name
SUBTOTAL(functioncode, value1, value2,...)
Definition
This function calculates a subtotal of a list of numbers using a specified built-in function
Syntax
function code- required and type of subtotal to create.Function code can be a value ranging from 1-11
1
isAVERAGE
2
isCOUNT
3
isCOUNTA
4
isMAX
5
isMIN
6
isPRODUCT
7
isSTDEV
8
isSTDEVP
9
isSUM
10
isVAR
11
isVARP
Hidden values can be skipped for any of these codes by prepending
10
(to the single-digit codes) or1
(to the 2-digit codes). e.g. 102 forCOUNT
while skipping hidden cells, and110
forVAR
while doing so.
value 2- optional and the second number used to compare. Assumed zero by default.
- Cells that are hidden due to auto filter criteria are never included in
SUBTOTAL
, irrespective of thefunction_code
used. - Cells within any of the specified
range
arguments that containSUBTOTAL
calls are ignored to prevent double-counting.
- Cells that are hidden due to auto filter criteria are never included in
Step 1 :To begin to use the SUBTOTAL formula, start with the Excellentable you would like to edit.
Step 2 : Then type the SUBTOTAL Formula in the cell you have chosen to display the outcome:
Step 3 : Type in the complete SUBTOTAL formula for a cell as shown below:
Step 4 : Excellentable will generate the outcome when hitting enter.