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.
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.
Dear Riny_van_Eekelen
Thank you for your quick reply..
It is working in normal cell ranges but in a table it is not working, it showing the same qty in all respective item's row (as highlighted in the excel).
I have attached the excel file for your reference
vppismail In this case, I believe you have to sacrifice the structured table references and manually enter the first and last range in the following in N21:
=SUMIF($I$6:I7,[@[ITEM NAME]],$M$6:M7)Riny_van_Eekelen Greetings!!
Sorry to bother you, you helped me a lot and I am very glad to be with you.
This formula is consistent, not updating accordingly when add new rows in this table, so if it is possible you may peruse the attached excel last two rows and also please try to add new rows with data then you can understand the issue.
