# Use the Subtotal Function in Excel

Updated on January 26, 2011

The Subtotal function in Excel performs a specified function (Sum, Average, Max) on a range of cells and excludes any hidden rows. This makes it perfect for use with the results of a filter and sheets containing hidden rows.

The syntax for the Subtotal function is:

=SUBTOTAL(function_num, ref1, …)

function_num – Specifies the function to be used. It is entered as a number that corresponds to the correct function. The list below shows the functions that Subtotal can use and its corresponding number.

1 - AVERAGE

2 - COUNT

3 - COUNTA

4 - MAX

5 - MIN

6 - PRODUCT

7 - STDEV

8 – STDEVP

9 – SUM

10 – VAR

11 – VARP

ref1 – The range of cells that you want to subtotal. It can be entered as a reference or a range name. More than one reference can be used and should be separated by commas. As many as 29 references can be used (however likely that is).

## Using Subtotal to Sum Results of a Filter

Possibly the most popular use of the Subtotal function in Excel is to calculate the results of a filter. The example below sums the values in column E. Each time the filter is run, the Subtotal displays the required answer.

=SUBTOTAL(9, E4:E73)

The image below shows the filter applied to show only results for the salesperson Lucy Pinder.

This makes the Subtotal function extremely versatile and incredibly useful. You can create more formulas to extract the average, max and count of the results as well, or simply change the function being used in the existing Subtotal.

2

4

2

0

5

1

4

7