Use the SUBTOTAL function to summarize amounts, and exclude filtered or hidden rows. For Excel 2010 and later AGGREGATE function is also available, and has more options and functions.
Use the SUBTOTAL function to exclude filtered or hidden rows when calculating a total. You can choose any one of the 11 functions that SUBTOTAL can calculate, such as Sum, Average, Count or Max.
This video show how to use SUBTOTAL or the newer AGGREGATE function, to work with filtered data.
To show the difference between SUBTOTAL and COUNT, in the screen shot below, there is a list of orders with totals above the list. The list has been filtered to show only the West region’s sales.
Above the list, there are total counts:
So, if you are working with lists that will be filtered, use SUBTOTAL, instead of the basic functions, such as SUM, COUNT, or AVERAGE. The instructions below show how to use the SUBTOTAL function.
First, here is a quick way to create a SUBTOTAL formula to sum the amounts in a list:
Because the list is filtered, Excel inserts a SUBTOTAL formula. It has 109 at the beginning, and that tells Excel to SUM the numbers.
NOTE: If the list is a formatted Excel table, it might expand automatically, and put the formula in a Total row. To undo that step, press Ctrl+Z immediately.
Within the brackets for the SUBTOTAL function there are two arguments, separated by a comma (or a semi-colon, depending on your regional settings). Here is the formula created by the AutoSum example above:
The first argument in the SUBTOTAL function is a function number which specifies how the total should be calculated. When you type the opening bracket for the SUBTOTAL function, a drop down list of 11 functions appears, listed alphabetically. See the full list in the next section.
The 11 functions are listed in alphabetical order, and each function has two numbers:
|AVERAGE||1 or 101|
|COUNT||2 or 102|
|COUNTA||3 or 103|
|MAX||4 or 104|
|MIN||5 or 105|
|PRODUCT||6 or 106|
|STDV||7 or 107|
|STDEVP||8 or 108|
|SUM||9 or 109|
10 or 110
|VARP||11 or 111|
There is one key difference between the two sets of numbers.
Note: Rows that you format with zero height WILL NOT be included in either type of subtotal.
Instead of typing a function number into the SUBTOTAL formula, this example shows how to select a function name from a drop down list.
This makes it easy to see different results quickly, such as Average, Sum, Min or Max, without trying to remember all the function numbers.
Watch this video to see how this technique works, and download the sample file (below), to try it yourself.
The written instructions are on my Contextures blog: Change Excel Function with SUBTOTAL
Go to the following pages for more SUBTOTAL examples:
For Excel 2010 and later, the AGGREGATE function is also available. It is similar to SUBTOTAL, but has more options and functions.
Excel also has a Subtotals feature, that groups the items in a list, and adds one or more subtotal rows.
Last updated: March 3, 2021 4:14 PM