Excel SUBTOTAL Function
The SUBTOTAL function can be used with a variety of calculations, such as SUM, AVERAGE, COUNT and many more. We will look at the most common used calculation SUM in this example.
The SUBTOTAL function is useful when you have a list where there are multiple sum totals included in it.
By using the SUBTOTAL function you can add up the individual items and exclude the totals so that they are not double counted.
Why Use SUBTOTAL
It allows you to return the subtotal of a list, including when there are subtotals in the list itself.
Values Calculated
A number depending on the type of SUBTOTAL function requested.
How to type the formula (Syntax)
=SUBTOTAL (function_num, ref1, [ref2], ...)
Arguments
function_num - A number that specifies which function to use in calculating subtotals within a list. See table below for full list.
ref1 - A named range or reference to subtotal.
ref2 - [optional] A named range or reference to subtotal.
In the example below details for three customers are being analysed by product and amounts sold to them. The SUBTOTAL function has been used to ensure amounts are not double counted.
The Grand Total could have been derived with a simple SUM as follows: =E6+E10+E14 or =SUM(E6,E10,E14).
There is nothing wrong with either of these solutions when there are only a few rows included in the formula. However, when the list becomes much longer the SUBTOTAL is much more manageable to change the SUBTOTAL range to be =SUBTOTAL(9,E3:E140) rather than having to add the individual cells in to the individual formulas.
The SUBTOTAL formula can be used with the following functions:
Notes:
(1) AVERAGE will calculate the average of selected cells.
(2) COUNT will only count numbers. Blank cells and cells with text are excluded.
(3) COUNTA will count both numbers and text. Blank cells are excluded as with COUNT.
(4) MAX will return the maximum number input, but not the total.
(5) Min will return the minimum number input.
(6) PRODUCT will multiply the numbers within the specified range and ignores blanks and text.
(9) SUM returns the value of items included within a list.
(7), (8), (10) and (11) relating to standard deviations and variances are not discussed in this ‘How to’ guide.
The following table shows the values returned when using the most popular SUBTOTAL function numbers.
If you are using a file that includes hidden rows and you do not want them to be included in the SUBTOTALs then you should use function numbers 101 to 111. We would not recommend setting up a file in this way; we would suggest using the FILTER function. This is because when filters are applied SUBTOTAL functions 1 to 11 exclude the rows hidden as a result of the filter.
