Purpose of Excel SUBTOTAL function
The SUBTOTAL function in Excel is used to return the subtotal in terms of sum, average, count, max, min etc. for the specified list, named range or database.
The SUBTOTAL function in Excel is used to return the subtotal in terms of sum, average, count, max, min etc. for the specified list, named range or database.
In the SUBTOTAL function, you have to enter the value of the function that specifies what is required as the subtotal. For example, the value 1 is for returning average for the specified list or database and value 9 means return the SUM.
An example of SUBTOTAL to get the sum
Before explaining the syntax of SUBTOTAL, let us have a look at a simple example of using the SUBTOTAL function for getting the sum.
For that, we have an Excel sheet with three columns that contain Month, Number of Sales and Amount. We will get the sum of B2:B5 range by using the SUBTOTAL function.
The SUBTOTAL formula:
=SUBTOTAL(9,B2:B5)
The resultant sheet:
The value 9 means return the sum of the given range. On the basis of this example, let us have a look at the general syntax of the SUBTOTAL function.
Syntax of SUBTOTAL function
SUBTOTAL(function_num,ref1,[ref2],…)
Where:
- Function_num specifies the function to use for the given range in SUBTOTAL function. You may use 1-11 or 101-111 values in this argument. The value 1 is for AVERAGE function, 2 for COUNT, and 9 for SUM function. See the list below for learning which function each of these numbers represents. This is the required argument in the SUBTOTAL function.
- The ref1 argument represents the reference, range or named range etc. I used B2:B5 in above example, so SUBTOTAL returned the sum of that range only. This is also required argument.
- Optionally, you may also provide ref2 for the second reference or range. The SUBTOTAL allows up to 254 references that you want to get SUBTOTAL.
List of Function_num argument values with functions
Following are the numbers and respective functions that you may specify in the SUBTOTAL function:
Include hidden cell values
- 1 is for Average
- 2 – COUNT
- 3– COUNTA
- 4– MAX
- 5– MIN
- 6 – PRODUCT
- 7 – STDEV
- 8– STDEVP
- 9 – SUM
- 10 – VAR
- 11– VARP
List of values that do not include hidden cells in SUBTOTAL function:
- 101 is for Average
- 102 – COUNT
- 103– COUNTA
- 104– MAX
- 105– MIN
- 106 – PRODUCT
- 107 – STDEV
- 108- STDEVP
- 109 – SUM
- 110 – VAR
- 111– VARP
The example of getting average, max and min values
The following example uses the Amount column in our example sheet to get the average, count, max, and min values for a given range. Have a look at the result and formulas:
The Excel sheet:
You can see, the month of March record is kept hidden (compare it with above graphic). It contains the amount $20000.
The formula for getting average:
=SUBTOTAL(1,C2:C6)
For Count:
=SUBTOTAL(2,C2:C6)
For maximum value in the given range:
=SUBTOTAL(4,C2:C6)
For getting the minimum value:
=SUBTOTAL(5,C2:C6)
Play with various function values in interactive Excel sheet online
Below, you can see an interactive Excel sheet that contains the same data as used in above examples.
You may interact with it right here or download at your system. I have used the following generic formula in B16 cell:
=SUBTOTAL(B15,B2:B7)
As you enter a value in the B15 cell from 1-11 or 101 to 111, the respective formula will be applied to the B2:B7 range that contains the number of sales.
For seeing the difference between hidden and unhidden row values, I have hidden the third row that contains 150 value i.e. March record.
Enter various values within the limit in B15 cell and see how it is updated in the B16 cell.
Using multiple ranges example
In the following example, I have used two different ranges in the SUBTOTAL function. The first range is B2:B4 and the second range is B11:B13. The SUBTOTAL formulas are used to get the sum, average, count, min and max values as follows:
The following formulas are used:
Average:
=SUBTOTAL(1,B2:B4,B11:B13)
Count:
=SUBTOTAL(2,B2:B4,B11:B13)
Max:
=SUBTOTAL(4,B2:B4,B11:B13)
Min:
=SUBTOTAL(5,B2:B4,B11:B13)
Sum:
=SUBTOTAL(5,B2:B4,B11:B13)
The following example shows the difference between different values for hidden and unhidden state of the rows.
For that, I have hidden the March row again that contains 150 value. In the E column cells, I displayed the formulas used for hidden and unhidden e.g. 1 for getting average including hidden cells and 101 for excluding hidden cells.
The results are displayed in G column for the same range i.e. B2:B8 so you can see the difference.
