Join the social network of Tech Nerds, increase skill rank, get work, manage projects...

prev 1
• # How to use Sub Total Function in MS Excel and Open Office Calc

• 1
• 2
• 1
• 1
• 0
• 0
• 0
• 0
• 872
Comment on it

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.

 function Function 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP

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

## 0 Comment(s)

OR
OR
Register

OR
Fill out the form below and instructions to reset your password will be emailed to you:

• Hire
• Post Projects

### Post Projects

• All at 0 Cost ....
• Post Tech Job
• Select Best Bidder
• Track the Project
• Approve Work and Pay safely
• Browse Nerds
• Work
• Find Projects Find Projects
• UI Design and UX
• Software Engineering
View more...
View less...
• Marketing
• General
• Manage
• Company Company

### Manage Company

• All at 0 Cost ....
• Manage Company and Employee Profiles
• Company wide Employee Productivity Reports
• Knowledge Sharing and Collaboration Tools
• Get Sales Lead and Bid for Tech Projects
• Send Invoices and Receive Payment Safely
• Learn
• Nerd Digest Nerd Digest
• UI Design and UX
• Software Engineering
View more...
View less...
• Marketing
• General
• Tech Q & A Tech Q & A
• UI Design and UX
• Software Engineering
View more...
View less...
• Marketing
• General