SUBTOTAL(functioncode, value1, value2,...)


This function calculates a subtotal of a list of numbers using a specified built-in function 


  • function code- required and type of subtotal to create.Function code can be a value ranging from 1-11

    • 1 is AVERAGE
    • 2 is COUNT
    • 3 is COUNTA
    • 4 is MAX
    • 5 is MIN
    • 6 is PRODUCT
    • 7 is STDEV
    • 8 is STDEVP
    • 9 is SUM
    • 10 is VAR
    • 11 is VARP
    • Hidden values can be skipped for any of these codes by prepending 10 (to the single-digit codes) or 1 (to the 2-digit codes). e.g. 102 for COUNT while skipping hidden cells, and 110 for VAR 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 the function_code used.
    • Cells within any of the specified range arguments that contain SUBTOTAL calls are ignored to prevent double-counting.

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. 



User does not have sufficient privileges to access this Content
Learn More