How can I create running totals by row and group in Excel?

Posted on 2011-04-18
Last Modified: 2012-05-11
Please reference attached spreadsheet.

My goal with this spreadsheet is to create a "running total" or "running sum" of requirements by part in order to determine which records are fulfilled with the available current quantity onhand, making it possible to determine which records will require additional quantities.

Data in columns A,B,C,E and F are record specific.  Data in column D is the same for each unique value in column A.  It will be the same value on every row that contains the same value in column A.

Balance after Allocation is simply the running total of quantities left after each row is allocated from the Qty OH column.  Naturally, the first record for each part will be the Qty OH - Qty Required.  Subsequent rows for the same part would be the previous record's value for Balance after Allocation - the current record's value for Qty Required.

I'm struggling with the VBA routine(s) required for such a function.  If I were able to generate column E (Balance after Allocation), it then becomes straightforward.  How can I generate the date in column E?


Question by:emiller1680
    LVL 30

    Expert Comment

    >>>How can I generate the date in column E?

    Not sure I understand.

    Can you show me a sample of the result that you are expecting?


    Author Comment


    Sure thing; I'm not always the best at explaining things.  Columns A-D will be the only data I have to work with.  Column E is the result I'm expecting.  From Column E, I can generate column F with a fairly simple if statement.


    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Does it have to be VBA or can you do with a formula?
    LVL 85

    Accepted Solution

    Based on your sample here's a formula to create column E:

    in E2: =INDEX(D:D,MATCH(A2,A:A,0))-SUMIF(A$2:A2,A2,C$2:C2)
    and copy down. See attached sample.

    Author Closing Comment

    This formula works fantastic.  I was struggling to find the answer.  Thank you!

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now