Forum Discussion
SUMIFS continuously add and Subtract the value of an item in rows in an Inventory Table
Hi All!
Can anyone help me out?
I wanted to increase or decrease the quantity of a same item according to the transaction type IN or OUT from an Inventory Table. It means when I stock in (IN) the item qty will add up and when stock out (OUT) the qty will less it. But in my case as shown in the attached screenshot the quantity is showing same in all rows of respective items. I needed to be in history like first row qty 20 nos, then the next row qty will show 12 nos if it is OUT transaction.
Thank you in advance for your help.
The formula I used for "Stock Quantity" column is here
=SUMIFS([QTY IN/OUT],[TRANSACTION DATE],"<="&EOMONTH([@[TRANSACTION DATE]],0),[ITEM NO],"="&[@[ITEM NO]],[IN / OUT],"IN")-SUMIFS([QTY IN/OUT],[TRANSACTION DATE],"<="&EOMONTH([@[TRANSACTION DATE]],0),[ITEM NO],"="&[@[ITEM NO]],[IN / OUT],"OUT")
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.
12 Replies
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)
