The SUBTOTAL function in Excel allows us to work easily with lists and databases which have been filtered, or perhaps contain hidden rows.
In the following example we’re using a spreadsheet called ‘Car Sales database – Subtotals’ which you can download here.
You can see that we have already totalled the number of sales in cell B1, which comes to 166. However, if we then filter the data to show only Claire’s sales, we still get a value of 166.
We need to be able to calculate the total for only the visible data. This is where the SUBTOTAL function comes in!
SUBTOTAL is actually a number of functions in one. For instance, we can sum, average, count and find minimum and maximum values, all with the same basic function.
The function itself contains two fields (or arguments): Function Num and Ref. The function number determines what you want the function to do and Ref is simply the range of cells you wish to apply it to.
Here is a list of the most popular uses for SUBTOTAL and the corresponding function numbers:
- SUM: 109
- AVERAGE: 101
- MIN: 105
- MAX: 104
- COUNT: 102
- COUNTA: 103
Let’s look at an example of how this works, using the SUM function:
Step 1: click in the cell where you want to place the formula (in this case cell B2), then click the Insert Function tool button (fx)
Step 2: Now choose the SUBTOTAL function (you might have to use the search box) and click the OK button
Step 3: We are totalling data in this example, so we need to type 109 in the Function Num field
Step 4: Next we select the full range of sales in the database (C5:C160) and click OK.
Initially our subtotal sales will be the same as the total sales (166). However, if we now filter for Claire’s sales, we can see that she sold 23 cars.
Using the various Function Number options allows us to analyse our data in different ways, however the above steps are exactly the same.
One final point: as we’ve seen, SUBTOTAL automatically omits data that has been filtered. However, we have a choice to omit or include data which has been manually hidden. Using the previous Function Number table, manually hidden data is excluded. If you wish to include manually hidden rows, just use the following Function Number table instead:
- SUM: 9
- AVERAGE: 1
- MIN: 5
- MAX: 4
- COUNT: 2
- COUNTA: 3
I hope you’ve found this short tutorial useful and if so, a share would be really appreciated!