# 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 **SUBTOTAL**s 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.