Smart Tips on MS Excel 2007 – How to use Subtotal function?

Microsoft Excel has numerous functions but the issue is about knowing which function to use in the shortest time We began with a few smart tips in the initial posts which looked aspects like shortcuts and basic functions in at Microsoft Excel 2007. We would continue with the same and gradually address some more text, math and functions related to editing with examples.

Smart Tips on MS Excel 2007
Smart Tips on MS Excel 2007

In this post we shall look how to use Subtotal function. Let’s say you have a set of numeric data like sales in a set of cities. You wish to find out the sum of the units sold. Normally, you might make a selection and click on sigma symbol. This is a time taking process particularly when there are many entries. So, the other way around is to type the sum formula for the selection in the following manner.

Microsoft Excel Sum

Now, to get the sum for the same set of values using Subtotal function, you would have to type the function in the following manner. The format for using the function is =Subtotal(function_num, ref1, [ref2]…). For the above example, the function took the following values= Subtotal(9,b2:b9).

Microsoft Excel SubTotal

You would find here that the function has an argument- function_num where 9 has been entered. What is 9? Subtotal has pre-defined a set of values for some functions. They are as follows:

1Average
2Count
3CountA
4Max
5Min
6Product
7STDEV
8STDEVP
9Sum
10Var
11Varp

So, 9 means sum. In earlier version of MS Excel, one might have had to remember what each value stood for but in Excel 2007, these values appear once the formula name Subtotal is entered.

We would continue bringing out such utility functions to unravel the power of MS Excel.