cfo.com

Print this article | Return to Article | Return to CFO.com

Spreadsheets: Set Up a Subtotal-Filter Combo in a Flash

Use a clever and quick trick to combine the SUBTOTAL function with Filter dropdowns, and then use the combo to perform any one of 11 useful calculations.
Bill Jelen, CFO.com | US
May 25, 2011

Editor's Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com's Spreadsheet Tips Page.

Reader Alan B. wins an autographed copy of Learn Excel 97-2007 from CFO.com and MrExcel for his suggestion about using the =SUBTOTAL function in conjunction with the Filter command.

Most people run into the =SUBTOTAL function when it is added through the use of the Data Subtotals command in Excel. However, the SUBTOTAL function has another neat trick when used in combination with the Filter dropdowns in Excel 2007/2010 or the AutoFilter dropdowns in Excel 97-2003.

Easy Way to Apply a Filter
Normally, you would choose Data, Filter, AutoFilter in Excel 2003 - or Data, Filter in Excel 2007/2010. You would then have to select from the dropdown. There is a faster way to set up a filter starting in Excel 2007.

In Figure 1, you can filter to all of the Connecticut records by finding a cell that contains CT. Right-click that cell, choose Filter, Filter by Selected Cells Value.

Fig. 1

MrExcelMay25-Fig1

 

The Filter will be applied and you will end up with only the CT records.

Fig. 2

MrExcelMay25-Fig2

 

Adding the Subtotal Function
Select the first blank cell beneath one of the numeric columns, as shown in Figure 2.

Click the AutoSum button (Figure 3) or press Alt+Equals.

Fig. 3

MrExcelMay25-Fig3

Normally, the AutoSum button adds a =SUM() function. However, since this data has been filtered, the AutoSum button provides =SUBTOTAL(9,C2:C413). This formula adds only the visible cells from the filter (See Figure 4, below).

 

Fig. 4

MrExcelMay25-Fig4

 

Use the filter dropdown in B1 and change to a new state. The total will change to reflect only the visible records (See Fig. 5, below).

Fig. 5

MrExcelMay25-Fig5

Subtotal Counts, Averages, and More
While the AutoSum button provides the version of SUBTOTAL that will sum, you can change to any of these 11 calculations. Simply change the 9, to one of these values:
   1 - AVERAGE
   2 - COUNT (Numeric cells only)
   3 - COUNTA (All non-blank cells)
   4 - MAX
   5 - MIN
   6 - PRODUCT
   7 - STDDEV.S
   8 - STDDEV.P
   9 - SUM
   10 - VAR.S
11 - VAR.P

 

Using SUBTOTAL with Manually Hidden Rows
The subtotal examples above assume that you are working with a data set that has rows hidden due to a Filter. What if you've manually hidden rows? Then you need to add 100 to the first argument. In Figure 6, the =SUBTOTAL(9 becomes =SUBTOTAL(109. The arguments 101-111 will deal with a data set where you've used Format, Row, Hide to hide the rows.

Fig. 6

MrExcelMay25-Fig6

 

CFO Contributor Bill Jelen is the author of 32 books about Microsoft Excel including Pivot Table Data Crunching. You have the chance to win a copy of one of his books by posting a question to the Community Center at the right. If Bill selects your question as the topic of a future column, we'll send you a book as a thank you. Bill's next MrExcel webcast, "PowerExcel" is Tuesday, June 14, at 2:00 Eastern Time. Click here for more details as they become available.




CFO Publishing Corporation 2009. All rights reserved.