Subtotal Function
Subtotal function should not be mixed up with subtotal formula. Both
are achieving simlar objectives but are different in nature. As a function,
it is able to insert subtotal into different groups of data. As a formula,
it can help you derive subtotal in filtered records. Subtotal formula
works within a cell while the other works within a set of data. For
example, subtotal function can calculate the total sales turnover for
each industry group. Assuming you have a set of data records as shown
below:
And you want to insert the total sales turnover, total employees below each
industry class (Comm/Tpt/Storage, Construction, Finance, Holdings). For
those who don’t know about this function, what they would have done is
to insert rows manually after each category. Then use the sum formula
to sum up the sales turnover and the total employees into the inserted rows.
There is an easier method and that is the subtotal function.
Here is what you do.
1. Select a cell within the data set.
2. Go to Menu, select data -> Subtotal.
3. The following dialog box will appear.
a. At each change in, select “Industry Class”. This is to indicate where
should Excel insert the subtotal formula.
b. For Use function, choose “Sum”. You can use a number of functions such as
i. Sum
ii. Count
iii. Average
iv. Max
v. Min
vi. Product
vii. Count Num
viii. StdDev
ix. StdDevp
x. Var
xi. Varp
c.In the Add Subtotal to section, check the box on Sls Turnover ($’000)
and No of Employees. These are the items that would be sum up at the end
of the subtotal function.
d. Replace current subtotal as the description implies will replace
any previous subtotals. In our case, this is the first subtotal and
therefore, it has no effect in the function.
e. The subtotal function will also insert a page break after
every category, namely Comm/Tpt/Storage, Construction, Finance, Holdings.
f. Summary below data will place the subtotal below the data.
If the option is unchecked. The grand total and the subtotal will
appear at the top of the data.
4. Click OK and you will get result given below. (PS: I have highlighted
the subtotals so that you can see the effect of the subtotal)
Back to the
top of subtotal function
•
|
New! Comments
Have your say about what you just read! Leave me a comment in the box below.