Forum Discussion
SUMIFS continuously add and Subtract the value of an item in rows in an Inventory Table
vppismail I would like to recommend you to insert an extra column that sets the quantity to plus or minus based on IN/OUT. Then you can use what you could call and auto-expanding SUMIF to calculate the closing inventory after each transaction for a particular item. Easiest to demonstrate in a working example. Please see attached.
@Riny_van_Eekelen I got it from another excel forum website and it solved by using this formula for structure table
=SUMIF(Table14[[#Headers],[ITEM NO]]:INDEX(G:G,ROW([@[ITEM NO]])),[@[ITEM NO]],Table14[[#Headers],[QTY +/-]]:INDEX(M:M,ROW([@[QTY +/-]])))
vppismail Thanks for sharing. Took the formula to test it in a schedule of my own. I learned something today. But, on my system (Excel for Mac and Excel for PC on a virtual machine), the formula I provided earlier returns the same outcome. Thanks again!
Riny_van_Eekelen You are welcome, I also learnt a lot from you, thanks again.