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.




4 - MAX

5 - MIN




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

  • Sort Pictures in an Excel List

    If you are using pictures in your Excel spreadsheet you may be having some difficulty with sorting the pictures along with the other data in the list. The good news is that you can sort pictures in an Excel list just...

  • Create an Interactive Excel Chart using Option Buttons

    Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...

  • Brock Lesnar Nutrition and Workouts

    Brock Lesnar is a phenomenon of a man and an athlete. Brock Lesnar weighs a lean 265 lbs and yet possesses extreme speed, agility and athleticism. How a man carrying so much muscle mass can maintain such high levels of...

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