Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Inventory Running Total

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

i have a workbook that we enter daily purchases in by weight, price we paid and it has an avgerage cost field also. i need one that takes all these categories and produces a running total . is this possible? im trying to take as much human error out of my system as possible. is there a way to make a sheet that clears itself each time, so its an imput only sheet? any help on any of this will be greatly appreciated

View Answers     

Similar Excel Tutorials

Calculate the Total Time Worked Minus Lunch Breaks in Excel
Ill show you how to use Excel to calculate the total time worked in a day minus lunch time or any breaks. This is ...
Excel Time - Summing Time Greater than 24 Hours
I'll show you the easy and simple way to sum time past 24 hours in Excel.  All we really have to do is to change th ...
Loop Through an Array in Excel VBA Macros
I'll show you how to loop through an array in VBA and macros in Excel.  This is a fairly simple concept but it can ...
Calculate the Difference Between Two Times in Excel
Here, youll learn how to get the difference between two times in Excel. A common example of this is for when someo ...

Helpful Excel Macros

Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Bubble Sort
- This macro will perform a bubble sort in excel. You use it simply by selecting one column to sort and then running the
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai
Delete Hidden Worksheets
- This macro will delete all hidden worksheets within a workbook. When you run this macro a warning window will pop up for

Similar Topics

Please See Attached
I am designing an Inventory and Sales tracking spreadsheet to track multiple things such as inventory count, Purchases (On Order) count, unit cost, revenue, and profits. It is a work in progress but basically there is a master inventory list with all the inventory items listed. Then purchases/sales are entered on a month by month sheet basis.

Here is the problem I am having: I would like to have the excel spreadsheet automatically calculate an average unit cost for the Current inventory.

For Example:

I have an item, Thing 1. My beginning inventory on the master inventory sheet is listed as 0, so in the January purchases/sales sheet when I enter Thing 1 in the id field it automatically identifies the item and that there are 0 in current inventory. I purchase 1000 units at a price of $2/unit = $2000 Total Cost. I then also purchase another 2000 units of Thing 1, at $1.50/unit = $3000 Total Cost.
The master sheet is reading that there are now 3000 units. I want it so that these units do not appear on current inventory until they are marked as arrived on the january sheet.

Now the hard part, once these units are marked as arrived I want them to them to display in the Master Inventory and show a Weighted Average Unit Cost. So:
1000 units @ $2.00 = $2000
2000 units @ $1.50 = $3000
$5000 Total Cost / 3000 units = $1.67 per unit. So the Master Inventory should now report 3000 units at $1.67/unit.

The January sheet should now show the same unit cost when selling units, but not when purchasing them.

Hope this is clear enough, I'm having lots of troubles trying different formulas!


I'm going to try and explain what happens and I hope we can come up with a formula that works.

We make purchases in bulk of 10, but now we can make purchase in bulk of 25 and 50 and 100.

The price goes up 10% each time we make a purchase.

We buy 10 at 1,000, the next 10 the price goes up 100, to 1,100.

So, if we make a purchase of 50, we basically just made 5 10 bulk purchases.

So the price increases would be like this if we bought these as separate purchases:
1. 1,000
2. 1,100
3. 1,210
4. 1,331
5. 1,464
Total: 6,105

However, if we just bought 50 the first time, the price would still be the same, but one purchase instead of five separate purchases.

I'm trying to devise a formula that would take the quantity (provided by a drop down selection) we want to purchase.

We already have the various cells setup for the next purchase price and the percentage increase.

Here's the formula that I've tried to work with, but the outcome is still off.

Total Cost = ((2*A+(ROUND(B/10-1,0)*C))/2-IF(B=25,C/5,0))*B
Whe A = Current Cost, B = Amount Owned, C = Original Cost

I'm hoping that someone on here can help figure this one out.

I have a spreadsheet that I am tracking daily equipment cost on, for example


K15 will change everyday but I would like to make L15 keep a running total, so if today K15 is $5000 and tomorrow it is $3000 I would like L15 to
show $8000

Any help would be greatly appreciated.


I am trying to write a formula to keep a running total of the weight of exlposives we find on a daily to monthly to yearly basis.

The way we have it now is only for a daily basis and want to add the running total to it.
looks like this

number items found(A1) x known weight of explosives(B1)= Net Explosive Weight NEW(C1)
now we want to take that NEW and have it keep adding in another column till at the end of the month we can look at it and say, "*** number".

I tried writing it as a circular number only increasing by 1 multiple per time but each time I click a different cell to update numbers in each column it continues to change the running total in the NEW with it by 1 multiple. Or once I have closed it and reopen it to start a new day it starts adding itself up
C1 is NEW and D1 as our running total

Does anyone know how I can get each days total to add to the previous day total without this expotential growth problem?

so far the only think I can think of is by running with column D1 as my previous days balance with no formula in it and just a value and E1 as my running total with a formula of =sum(C1+D1) then save the file and then the next day if I have something to update on that row I have to take the value in E1 and type it into D1, ignoring the new value in E1 and then change A1 to its new value.

Any better ideas out there or a better formula?
Thanks Jacob

I have a spreadsheet that is used to enter sales, guest count, and calculates avg guest sales. This is done daily. But what i want is another sheet that will be a running total of the data. So i would enter in the daily sales, and that would be added to the running total. Each days sales would not need to be saved. I think this would best be done by using a macro, and having a button to add the sales to the running total, then clear the first sheet for the next day.

I'm not sure if this is a formula issue or VBA issue so I posted here. I have a workbook that tracks daily inventory. There are 120 sheets within the workbook each named 1,2,3 and so on. On each sheet a count of the inventory is noted in one cell and then the cell to the right is formulated to calculate the difference of the count from yesterday and the count today. The next cell over is a Running Total of this difference. This is where I am stuck. I need the formula/code to recognize the sheet prior and do the calculation. This is the last bit I need to figure out before I copy and paste the sheet to the other 100+ sheets in the workbook. Any help will be greatly appreciated !!

Hello All;

My apologies if this has been posted before, but I could not find an answer(yet).
What I have is a 1 sheet workbook I use as an invoice. It calculates the totals, adds tax, then a total in the bottom cell ( cell M(29)). I then click a button, it saves a copy using the name typed in and the date, prints a copy and then clears certain cells in the form for use again. All works well.
What I would like to do now is add a running total on the side of this sheet (say cell O10) that adds the cell M(29) value (the sale total) so I can at a glance see how much has been sold in dollars on all the saved sheets.
All I have tried has given me a circular reference error, or it just clears the cell each time the form is cleared.

Thank you for any help

I am new at this and need a bit of help please.

I have an inventory sheet and I get price change updates from time to time..
The inventory is well over 800 products but the price changes are much less.

I was wondering if its possible to match the part numbers from the price change sheet and to the full inventory and change the prices for COST and LIST on the full inventory sheet only affecting the price changed parts.

Any help with this would be greatly appreciated .

The price changes sheet is called PriceChange , the part number is in col A, cost in B, List in C

The full inventory I'd like to change
The sheet is called BulkProductData the part number is in col B, the cost is in col H, list in col I

there are header rows on each sheet

Again thank you for your time!

Hi guys i posted up for the first time the other day regarding a file i created for my father in law:
The file consists of the following columns: Date, Desctiption, Paid In, Paid Out, Total (please see attached image)
He has asked if the column for the total can include a running total all the way down instead of just being a grand total at the bottom.
The problem is as you can see form the attached image is that the data entered is different every time, what calculation can i use to create a running total whilst still keeping the grand total at the bottom?
When using a simple Paid in minus Paid Out formula, it just calculates what ever is in that row, and not what was entered in previous rows.
Apologies if anything doesnt make sense, it doesnt make sense to me :D


Newbie needing help!!!

I am trying to create a basic spread sheet with an inventory. What i would like to do is be able to keep an idividual running total on a large number of different items.

so when i remove 1 of item1 say it adjusts the original total.

The problem i have is as follows.

=Sum(A1-A2) =A3

A2 and A1 stay i place!? so next time i remove 1 it is not a real running inventory as im basicly re-typing everytime. I would really like to just enter a number into A2 with A1 adjusting itself according to the value of A2.

Is this possible or am i talking jibberish!?????

I am trying to make a spreadsheet to keep track of my daily food intake. I am basing this on the weight watchers point system. I get a daily number of points i can use and a weekly amount of "bonus" points i can use. If I go over my daily points it comes off of my weekly bonus point totals. how do i make a daily point total column and subtract my weekly bonus points as a running total for lack of better terminology.

Here is what i have so far

Daily Allowed Daily Used Daily Remaining Weekly Remaining Weekly Bonus Points Over
44 0 44 35 35 0

These columns start in "g". Column c is my daily point totals. I can only come up with a running total for daily use by then my daily used column is off. I hope this makes sense.

Thanks for any help anyone can give

I have a spread sheet where extra time worked in entered in to two columns, start extra time and finish extra time (column A 17:30 Column B 18:15). This then is totaled in a running total in hours and minutes in another cell. On the same spreadsheet, the employee enters time in two columns when finishing early and time rostered to finish when leaving early to use the time owed. This is then calculated as another column as a running total. I need the running total of time taken subtracted from the running total of time accrued. I cannot figure out how to do this.
I have attached the work book
any and all help will be greatly appreciated


ive got confused.

If i have two worksheets (w1 and w2) and on w1 i have a database of products on ws1 i.e.
column A = product name
column B = cost per product
column C = weight

then in worksheet 2 i want to basically make an order form that will calculate the total cost of an order. I want to write the name of the product and the number ordred and have the rest of the table be autofilled with the total cost and total weight for that product. I dont want to copy and paste from one sheet to the other. Also i dont want to show unecessary data from ws1 on ws2 i.e. cost per product and weight are not needed, just the name and the total cost and the total weight. this rules out my earlier idea of copying and pasting from one ws to the other. There must be a more elegant way. Does anyone know of one?

Any help appreciated.

I'm running excel for mac 08. I run a wholesale bakery and am trying to build a spreadsheet to keep track of what my clients order. I'd like to do these things:
-keep a running weekly total (currently it's on the 'invoicing tab')
-be able to zero out the 6 individual-day invoices at the end of the week

I currently have a total for each individual day, but what i would like to do is have one input tab that has the day's orders. at the end of the day it gets zeroed out but is recorded on the invoicing tab, which keeps summing for the whole week. then i make the invoices and zero it out.

i'd also like to translate this to my inventory. basically, i would solve all my problems if i could figure out how to keep a running total, and how i can zero out the inputs for that total when i need to. my wrkbk is attached, please ignore the first and second sheet and start on the sheet marked 'tuesday'. thanks!!

Hi all.

I'm relatively new to Excel and especially to macros. The spreadsheet I'm working on deals with inventory levels, and I want to write a macro to connect two worksheets. The first sheet is updated weekly with new purchases, and the first sheet is supposed to be a cumulative total of inventory. So, I am trying to write a macro that would add the quantity on sheet one to the quantity on sheet two, and then clear sheet one. This will be done every week to keep a running tally of inventory. Any idea how this could be done?

Thank you for any help, and I'm sorry if a similar question has already been asked.

First off I want to say thanks if anyone can help me. Thank!

I have an inventory sheet that has always been in word but I put it in Excel and have one problem.
The last three columns are price per unit, numbered ordered and total cost. All I want to do is set it up so that when I enter a number ordered it will automatically calculate the total cost. I know how to do each row individually but not how to set it up so that all you have to do is enter the amount ordered and automatically get the total cost.

I hope someone understands what I am trying to do and can help because I know it has to be something pretty simple to do.

Thanks so much,

Using 2003 for awhile now, but running into a brick wall. Have a client with an inventory workbook with 156+ worksheets. Each sheet represents an artist with columns of: title & medium, size, received, date, price, date sold, invoice, paid, comments, balance, type. There is a summary sheet we created to show how much is in inventory for each artist. That was easy enough. Just ran a total on each worksheet, then had the summary sheet read that worksheet and cell.

Now we need to figure out year to date sales for each artist. I have been searching the Internet and my Excel 2003 for dummies book, but no luck on how to pull up that info. The summary sheet shows only the artist name, amount in inventory, and year to date sales. I want to be able to tell the summary sheet to read the artist sheet for a date sold range, then pick up the paid amount. One problem is that inventory that came in two or three years ago might not sell until this year, so there are varying dates in the database. I think I need to SUMIF or COUNTIF my PAID column (which is a dollar amount) and the IF would be if the range of DATE SOLD column is between 1/1/06 and 12/31/06, but I can't quite make it over the hurdle. If there is anyone out there who can help, I would greatly appreciate it.


I am having issues creating a workbook that will be used for time/code tracking.

I created one sheet that has all the codes that I will be using and then created another sheet which is were the time data will be imput. I would like to type the code in on Sheet 1 and have the description pulled from the cost codes sheet. Lastly I would like to have the last page Total the total amount of time charged to each code at the end of the week.

I have included a copy. Any help would be greatly appreciated.


I am putting together a medication inventory sheet. The fields I am trying to
calculate are as follows:

C Units Issued
G Units Added
H Total Units in Stock
L Yearly Total

What I would like to do is keep a running inventory total. I want to have
the Total Units in Stock to be the sum of the Units Added minus the Units
Issued which I can do. However,I need to then have the Total Units in Stock
remain constant and be continually updated after each calculation is
performed. As of right now every time I change Units Added or Units Issued
the Total in Stock changes only to reflect the two new number I entered. I
need to have the Total in Stock reflect the sum of the current Units Added -
Units Issued as well as the sum of the previous Total in Stock calculation.
Any help is greatly appreciated.

I am using the sample spreadsheet that user highbarger provided on April 4, 2011. I added unit costs to the spreadsheet along with other columns. The cost for some products changes with the supplier with new purchases. Is there a formula I can use determine which unit cost to use? I know the number of each product in the master inventory prior to the purchase, so when the total count left in inventory gets to that number, I can change the unit cost to the new price.

Hello, I am fairly new to VBA and have learned quit a bit lately, but no I am stuck. I have a workbook with 2 worksheets. Sheet 1 is a list of many parts and where I wish to have current inventory levels posted. Sheet 2 keeps a running log of transactions. I have created a user form to look up parts and edit transactions. transactions are posting to sheet 2 as they should however, I cannot figure out how to calculate a running "ON HAND" total.

I have formulas in sheet 1 to pull information from sheet 2 as they change, but think I have created a mess. I am sure there is a much more efficient and reliable way to accomplish this task. I am attaching my work and any help would be greatly appreciated.



I have a spreadsheet that allows employees to enter time off taken (hours & minutes) and record the time paid back. The total paid back time is deducted from the total time taken. I have added a column that includes a running balance of time owing to the company. There are several blank rows below for the employee's data entry, and these rows include the running balance on every row.

My formula in M2 is =ROUND((K2-L2)*1440,0)/1440 and in M3, etc, my formula is

Total Taken Total Repaid Running Balance
3:00 0:00 3:00
0:00 2:00 1:00
0:20 0:00 1:20
0:00 0:00 1:20 *should display 0:00
0:00 0:00 1:20 *should display 0:00
0:00 0:00 1:20 *should display 0:00

My question is, how can I modify the formula so I can eliminate the repeating of the running balance in the unused rows? I want the running balance column to display 0:00 on rows that do not yet have data entered into them. I think I need to add an =if formula to my existing formula. Am I right? and what is the format?

I appreciate any help I can get with this.


inventory example.xlsx I have a sheet that I cannot get to work correctly
My excel sheet is actually an inventory listing for several items including item description, price, # of items on hand, # of items sold, and the remaining total. What I am trying to do it create a field and formula that will take the "remaining total" and put it in it's own column, prior to the "new" remaining total field change. An example is shown below:

PLU # Product Price # On Hand # Sold Remaining Total Previous Total Difference
0001 Soda 1.50 100 59 41
0002 Chips 1.50 100 54 46
0003 Cookies 1.50 100 51 49
0004 Plates 1.50 100 4 96
0005 Cups 1.25 100 14 86

I want to be able to take the Remaining Total #'s and create a formula so that it goes to the Previous Total PRIOR to the # Sold changing at the next inventory. Meaning that right now, as the example shows, I have 41 remaining Sodas. In two weeks, when I do the audit again, I want to be able to open this report and 41 be in the Previous Total field. I will perform the next inventory audit then as well. I now have 64 sold Sodas, which will then take my remaining total to 36. I still want my previous total field to show 41, as it was the last total prior to changing it at this audit. Mind you, the Remaining Total field has a simple formula in it (# On Hand-# Sold).

every time the sheet opens, I would like the data in F to move over to G!

I really really hope this makes sense, because I'm having a hard time finding anything on Google :/ HELP!!

Thanks in advance


I am by no means an expert in excel, but can navigate through it pretty well. I have two workbooks for inventory. The first work book is my daily sales report which gets cleared out and started over everyday. It links to my inventory list workbook by item numbers. So when I enter a product dold for the day it finds it in my inventory workbook and subtracts it from the inventory. However when I clear the daily sheet, the inventory reverts back to zero also. I have used vlookup and sumif functions throughout. I need to find a way to keep the numbers running consecutivley after clearing daily sheet. If marcos can be avoided that would be great. Is there a function I can add to my sumif that links to my daily sales? Thank you for any help. May seem trivial but I can not figure it out.

Hi Folks,

I am working on creating a worksheet for my personal finances and stuck trying to do the following task (see below).

I have tried conditional formatting and if statements within the cells with no success, i am not to clevere on if statements!!

This is what i am trying to do:


Column A represents Payee
Column B represents Payment Date
Column C represents Amount to pay per month
Column D to O represents each month January to December etc

The rows start from row 3 to 15

Row 17 represents the total for each month so puts a total in the cell underneath each column.

What i want to be able to do is when i enter the word PAID into a particular month in any row i would like to add the amount as running total.

For example:

If i enter PAID in row 3 Column D (January) then i what the calculation to take the monthly payment figure in row 3 column C and add it as a running total in row 17 column D.

This means that i wil have a running total in row 17 column D of everything i paid in January.

I hope that all makes sense.

I would really appreciate some help and guidance with this.