# Counter formula that subtracts with each transaction

This must exist, but I can not figure it out with Numbers. I have an inventory of products I sell. I want to automatically subtract from the total original inventory each time an item sells. And to do this, I fill out a re-useable packing slip in Numbers. The item and quantity is listed on the packing slip, which in turn automatically deducts from the inventory- a simple sum formula.

For example, I have 200 of an item and in Numbers it is listed as such in a certain cell in a table that keeps track of inventory. If I sell 10 of that item to a new client, I fill out a packing slip. My formula in the packing slip automatically subtracts 10 of that item from inventory. Now the inventory states 190 left. Great so far.

I save a copy of that packing slip for the client as a pdf. And then later that same standard packing slip is zeroed out and used for the next client.

So say the next order is a dozen for that same item. Now a problem. The inventory formula only recognizes the latest entry, so instead of now having 178 items left in inventory (200-10-12=178), the inventory states 188 items left because it 'forgets' about the ten already sold.

I don't want to manually fill myriads of entries in cells that compute each and every transaction just to make this work. I just want to formulate a running inventory count so each time a packing slip entry is made, the corresponding amount is further subtracted from the ongoing inventory count, just like a barcode reader might subtract from the total as a counter.

• For us to help you it'll be good to know what formula you're currently using and what others you've tried? Also, a rough idea of how you've set it out may be good too (e.g. What cell the formula is in and any other cells/formulas that are relevant to the calculation). You can edit your question via the edit link below it. May 6 '17 at 5:33
• Zeroing out the packing slip removes the previous amount. Therefore your inventory returns to it's original amount. As @Monomeeth says, it would be helpful to know how your formulas are structured. Please edit your question with more details and what you have tried to do to save the original result. Cheers
– bjbk
May 8 '17 at 14:27
• Well thanks. The only formula I know that would have a chance is the SUM. I looked thru them all, and it appears nothing would work from what I can understand. That's because formulas only compute existing data input, whereas I am looking for a performance that would serve like an action. That is, a one time command that performs a SUM formula, but retains the new result (of the inventory count), as the source cell is zeroed out.
– MMM
May 8 '17 at 19:19
• Oops, I pressed
– MMM
May 8 '17 at 20:19
• What you want to do will be possible, but we really need to know the formula you're using and the cells involved in the calculation. Otherwise anything we offer may not apply to your spreadsheet. For starters, explaining your 2nd paragraph in detail would help. Or perhaps you could share your file somehow for us to look at? Otherwise the only hint we can give you is that the problem, as @bjbk says, is that you're zeroing out the current total. So, you need to store this value or account for it somehow. May 8 '17 at 20:35