Hi,

In this session I am going to cover, How to use sub total feature in openoffice calc and Excel.

Let's take an example- I have a data of Employees Name, month of joining and salary. I want to get Grand total and subtotal. Grand total will be the total amount for all the months and sub total will be amount of any particular month I select.

In below screenshot I have 6 employees joined in month of June and July, having different salaries.

So to get grand total, we have the sum for the range of column 2 to column 6 to get this I used the below formula

=SUM(C2:C6)

If you are using MS Excel the formula will be =SUM(C2:C6)

This means it is providing me the sum of Row 2 to Row 6 from column C - see the screenshot below.

Now to get the subtotal, for any particular month- Go to or navigate to the cell where you want the sub total result in my case I want the sub total amount corresponding to Column 8 of Row C.

Here I put the formula-

=SUBTOTAL(9;C2:C6)

If you are using MS Excel the formula will be =SUBTOTAL(9, C2:C6)

Here, 9 stand for sum and C2 to C6 is the range for which the formula is applied.

Seeing the above screen-shot you might be confused as the amount in grand total and sub total is equal, this is because right now it's showing the amount for all the months, so I applied filter on Date of Joining, and when users select any month .i.e June, it will show the subtotal amount of that particular month. See the below screenshot for help-

SUBTOTAL(function; range) function is a number that specifies the function to calculate.

The above SUBTOTAL(function: range) - function is also available in MS Excel.