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.
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.
I notices an inconsistency on row 41. The last element in the formula is M42. It should be M41. Can't tell what you did to cause this. I copied the formula from the row above to the rows below it and all works fine. Then, I added a row to the table and the formula is automatically included in that new row.
Riny_van_Eekelen May be you haven't noticed by adding row by pressing TAB. When I solve by clicking "Restore to calculated column formula" then it is corrected by itself. When I add new row by pressing TAB then the just above row will be inconsistent by itself changing the value to M42 instead of M41
Riny_van_Eekelen It worked , thank you so much, appreciated. 😄