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 at aspects like shortcuts and basic functions in Microsoft Excel 2007. We would continue with the same and gradually address some more text, math, and functions related to editing with examples.
In this post, we shall look at how to use the 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 the 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.
Now, to get the sum for the same set of values using the 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).
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:
| 1 | Average |
| 2 | Count |
| 3 | CountA |
| 4 | Max |
| 5 | Min |
| 6 | Product |
| 7 | STDEV |
| 8 | STDEVP |
| 9 | Sum |
| 10 | Var |
| 11 | Varp |
So, 9 means sum. In an 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.
