Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to use Sub Total Function in MS Excel and Open Office Calc

    • 1
    • 2
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 661
    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.

    alt text


    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.


    alt text

    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-

    alt text


    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)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

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