How to add and subtract based on multiple conditions?

Question

This question is for an Inventory tracking system I am making in Google Sheets.

Just to be clear:

• Any incoming Items have a positive quantity
• Any outgoing Items have a negative quantity

The requirement is, when the closing stock is counted, the On-Hand quantity is put, which is a positive number. This positive number is what is currently available in stock.

Inversely, if the closing stock is put as a negative number, it means it is the amount consumed (negative number). Here, it will be subtracted from total previous stock. The challenge is therefore to calculate this:

Stock for Item X = [Difference between its total qty & the closing (on-hand) qty] - [previous stock]

I Used SUMIFS to get the stock for each item. This doesn't take into account the date, as we need the current stock to be calculated, regardless of days.

Problem: This works only till I add new incoming stock, after which the whole calculation goes for a toss. For example, if I add 10,000 on the 3rd day, the Inventory comes to -4300, when it should have been 15,700 See images for better understanding.

Before: No new stock added on day 3 --> The sumifs seems fine.

After: 10,000 qty added on day 3 --> breaks the sumifs.

Let me know if you need any other information to understand.

Would appreciate any help.. Spent too much time already on this. Thanks.

P.S. - I hope I am posting on the right stack. Should I post this on webapps too?

Answer 1

Jeet, welcome. Here are a few principles for Inventory management in Excel (and bank-account balance!) and debugging this question.

Separate your transactions from your running balance.

Use separate columns, not only for readability, but to help keep your formulas bug-free. In the example, it appears you're using SUMIFS to look for text cues ("Opening", "Closing", "Going") but this makes it impossible to validate a printout or screen dump. Use a separate column, like a ledger.

Never allow a negative number of Inventory.

Your inventory column should never go below zero, if your inventory is physical objects. Instead, prevent your transaction column from allowing more items to be subtracted than are available in inventory -- and require that more inventory be added before it is withdrawn.

There are a couple of ways to do this, but one way is to create an additional column for "backorder", "over-demanded", or "unfulfilled" inventory requests which are addressed before additional transactions.

You should do this even if your "inventory" consists of items that are easily available outside of your company, no matter how simple they are to get. (Even AA batteries available at a grocery-store next door which is open 24 hours per day).

Since you're modeling a physical process, you should not allow your spreadsheet to model something that's not possible. Model what's required to happen (in the example above, acquire the batteries next door, then send them out to a customer).

For debugging, build your "big formulas" from formulas from pieces that work.

There's a temptation to create big formulas on the first go. This makes them very hard to debug. It's far better to create small formulas (one clause at a time) that work -- test different cases on them, for example, positive transactions, negative transactions, transactions with zero balance, etc. -- and once you have everything working, consolidate them into big mega-formulas.

Your question did not provide the actual formulas you're using, so it's not possible to debug more specifically at this time. Finally, there is no Stack specific to spreadsheets, so you're right, it's not clear if this is a Super-user or Web-applications question. There should be a Spreadsheets stack.