Use the Subtotal Function in Excel

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.

Using the Subtotal function on results from a filter
Using the Subtotal function on results from a filter

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.

More by this Author


1 comment

gregoriom profile image

gregoriom 4 years ago from East Elmhurst, NY

This is a good sample of the pivot table. Which I'm currently learning about. Awesome

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working