Forum Discussion
Formula - Inventory Level
Morning lovely people, i am struggling to find the right formula
I have a lot of stock in my warehouse split by cost codes what I would like is if items are purchased for stock code (in this example 4000) there is a rolling qty that shows in Cell G5 & when it is consumed there is a rolling qty shown is cell G6
Hope this makes sense !
Any help is greatly appreciated
Thanks
1 Reply
To track the rolling quantity of items purchased and consumed for a specific stock code, you can use the SUMIF function. This function allows you to sum the values in a range of cells based on a specific condition.
Here’s an example of how you could set up formulas to track the rolling quantity of items purchased and consumed for stock code 4000:
In cell G5, enter a SUMIF formula to sum the quantity of items purchased for stock code 4000. The formula would look something like this: =SUMIF(B:B,4000,D:D), where B:B is the column containing the stock codes, 4000 is the stock code you want to track, and D:D is the column containing the quantity of items purchased.
In cell G6, enter a SUMIF formula to sum the quantity of items consumed for stock code 4000.
The formula would be similar to the one above but would reference column E instead of column 😧 =SUMIF(B:B,4000,E:E), where E:E is the column containing the quantity of items consumed.
These formulas will automatically update as you add new data to columns B, D, and E.
You can also change the stock code in the formulas to track different stock codes.
I hope this helps!
