In Excel, you might have a long list of orders with a grand total at the end. If you filter the Region column, so the list only shows one region’s sales, you’d like the total to include only those items.

If you used the SUM function in the grand total cell, the result won’t change if a filter is applied. This list is filtered to show orders from the West Region. At a glance, you can see that the Grand Total is much higher than the records listed. There are only 3 orders visible, but the order count is calculated as 49.

Instead of SUM or COUNT, you can use the SUBTOTAL function, and only the filtered rows will be included in the grand total.

Create a SUBTOTAL formula

A quick way to create a SUBTOTAL formula is to:

1. Apply a filter to the list. In this example, the Region column is filtered for “West”.
2. Select the cell where you want the grand total.
3. On Excel’s Standard toolbar, click the AutoSum button, or on the keyboard, press the Alt key and tap the equal sign key (Alt + =).

Because the list is filtered, a SUBTOTAL formula is inserted, instead of a SUM formula.

Reading a SUBTOTAL formula

Within the brackets for the SUBTOTAL function there are two arguments, separated by a comma (or a semi-colon, depending on your regional settings).

The first argument is a number that tells Excel which summary function to use in the subtotal. Most of the time you’ll use a 9, which is the SUM function.

The second argument is the range of numbers that should be subtotaled. In this example, cells H2:H50 are the cells that we want to sum.

Changing a SUBTOTAL formula

In some cases, you might want a different function in the SUBTOTAL function. For example, it wouldn’t make sense to sum the Unit Cost column, but it might be useful to know the average unit cost.

Unfortunately, when the list is filtered, only the SUM function on the AutoSum button inserts a SUBTOTAL function. If you click Average, you’ll get an AVERAGE formula. (Note: This is improved in Excel 2007, and the other functions on the AutoSum button also insert a SUBTOTAL function.)

So, click Sum to create a SUBTOTAL function, then change the function number from 9 to 1, which will calculate an AVERAGE. Or, change the function number to 2, and you’ll calculate a COUNT of the numbers in a range.

There are 11 functions that you can use as the first argument in the SUBTOTAL function. The list is alphabetical, so that might help you remember some of the numbers, without going to Excel’s Help every time.

Ignore Manually Hidden Rows

In Excel 2003, and later versions, you can also use the numbers (101, 102…), as shown in the second column of the table below.

With these numbers, any rows that are hidden with the Hide command (Format►Row►Hide) WON”T be included in the subtotal.

If you use the numbers from the first column (1, 2…), any rows that are hidden with the Hide command WILL be included in the subtotal.

Note: Rows that you format to zero height WON’T be included in either type of subtotal.

More Information

There are other examples of formulas for summing and counting cells on my web site.

Filed under: Excel, Functions