Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiline summing (Excel 2000)

    <img src=/S/help.gif border=0 alt=help width=23 height=15>
    I have a list of a group of transactions I need show a running total for each group on the same sheet. I know that I can do it with a pivot table, but how do I do it in a cell with a formula.

    Here is a simplified example.
    Grp Amt Tot
    1 1 1
    1 -2 -1
    1 2 1
    2 3 3
    2 -3 0


    My problem is there can be 2-5 transactions per group. The total is reset to zero between groups. I need a simple formula in the third column that shows the running total for that group only. I will be adding groups of transactions at a time and want the formula to automatically update. I am not opposed to a little VB code if necessary.

    Any and all help will be greatly appreciated!!! [please]

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Multiline summing (Excel 2000)

    How about this array formula in C2 and fill down: =SUM(($A$2:A2=A2)*$B$2:B2)

  3. #3
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiline summing (Excel 2000)

    It did not work for me. I put the formua in D2 and copied it down. The answers are the same as the values in column B. I will have to look up arrays again and see where they lead me.

    I have been playing with the sumif in this formula:

    =SUMIF($A$2:$A$99,A2,$b$2:$b$99)

    I think I am getting somewhere. It is not running, but the total for all the cells in the transaction group. I am trying to convince myself this is all I need. If I figure out how to modify it so it only shows on the last row in the group it may be OK.

    Thanks for the tip. <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Multiline summing (Excel 2000)

    Try this.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    St. George, Maine, USA
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiline summing (Excel 2000)

    Or try this in C2 and drag down:

    =SUMIF($A$2:$A2,A2,$B$2:$B2)

    Bob_D

  6. #6
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiline summing (Excel 2000)

    This works great. It doesn't matter if the rows are in order.

    I am still going to pull out the manual and review the arrays. The example left using arrays works as well, I just don't understand them. Learning is half the fun.

    Thanks everyone for the assistance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •