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

Posted on 2011-04-18
Medium Priority
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

ID: 35420061
>>>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

ID: 35421063

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
ID: 35421353
Does it have to be VBA or can you do with a formula?
LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 35422191
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

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 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