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.
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.
- The Subtotal tool in Excel
Group and summarise a list of data using Excel's Subtotal feature. The Sum, Average, Count, Max and more functions can be used for Subtotals.
- Microsoft Excel functions
Listing of the most useful Microsoft Excel functions. Explanations on how to write each function in Excel and examples of its use are provided.
- Copy Filter Results in Excel 2003
If you have filtered your data in Excel 2003 before, I am sure you have also tried copy and pasting the filtered results to another worksheet or workbook. By default, Excel 2003 will copy all the...
More by this Author
If you are using Microsoft Project to schedule and track your projects, at some point you will probably want to export your project data into Excel. Excel can then be used to analyse and summarise your project data...
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...
Thinking of buying a basset hound puppy? Wondering what equipment you need in the house ready for the arrival of your new pet. This article will identify the 10 items you cannot be without to welcome home and care for a...