SUMIFS continuously add and Subtract the value of an item in rows in an Inventory Table

[vppismail]

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")

[#2 · Accepted solution]

@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.

[#3]

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

[#4]

@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)

[#5]

@Riny_van_Eekelen It worked , thank you so much, appreciated. :D 

[#6]

@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.

[#7]

@vppismail 

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.

[#8]

@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 

[#9]

@vppismail I see. Didn't notice that before. I guess it's because we are not exactly following "best practices" here by violation against structured table references. Experimented a little and found that if the formula looks like below, the inconsistencies does not occur.

=SUMIF($G$7:@G42,[@[ITEM NO]],$M$7:@M42)

Don't really know how to explain why the @-sign makes this happen. Perhaps others out here who can explain.

[#10]

@Riny_van_Eekelen Thank you for your great support.

And the formula is not working.

Anyone can help me out.

[#11]

@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 +/-]])))

[#12]

@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!

[#13]

@Riny_van_Eekelen You are welcome, I also learnt a lot from you, thanks again.