Best Microsoft Excel Bloggers

Monday, September 13, 2010

Subtotal Example with Filtering

Using the Subtotal Function
I wanted to spend a minute and show a subtotal example because of some emails I received. A lot of people seem to be only familiar with the Subtotal command on the Data tab or Data menu that automatically creates the subtotal for you . Obviously that is the fastest way but typically it requires sorting your data and sometimes you don't want to do that.

In those cases, you would want to use the SUBTOTAL function .
=SUBTOTAL(function_num, ref1, ref2, ...).

The beauty of the Subtotal feature is that it does not have to be at the bottom of the data- it can be anywhere on the sheet. In addition, you do not have to manipulate your data.
Below is a simple example, where I filtered sales by State and I wanted only the total for Massachusetts' sales. When I use =SUM(C6:C9) and select the filtered range, it added up all the sales of all states which was 1800.
When I used the Subtotal function on the filtered data =SUBTOTAL(109,c6:69), Excel returnd the total sales of the filtered data which was 900.
Enjoy the week. Fall may actually be here - crisp weather and the leaves are just starting to turn.

No comments:

Post a Comment

Ms. Excel- Resident Excel Geek