# 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

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

• 0

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...

• 6

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...

• 2

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 4 years ago from East Elmhurst, NY

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

0 of 8192 characters used

Alan Murray (almurray)57 Followers
71 Articles