SUMIF - Stock item quantity to plus or minus based on IN/OUT

[vppismail]

Hi All!
Can anyone help me out?

I wanted to auto-expanding SUMIF to calculate the "Closing Stock" column after each transaction for a particular item when I insert new row by pressing TAB key to add new record in a structured table.
Increase or decrease the quantity of a same item based on the transaction type IN or OUT . It means when I stock in (IN) the item qty will plus it and when stock out (OUT) the qty will minus it. But in my case, in the attached excel sheet, 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

Easiest to demonstrate in a working example. Please see this link.

The formula I used for the column of "Closing Stock" is here =SUMIF($G$6:G7,[@[ITEM NO]],$M$6:M7)

Your quick response is highly appreciated and thankful.

 

[jasonb75]

Certain types of formula are never as simple as they should be when you use them is structured tables.

There may be other ways to get the desired result, but this is the method that I use.

=SUMIF(Table14[[#Headers],[ITEM NO]]:INDEX(G:G,ROW([@[ITEM NO]])),[@[ITEM NO]],Table14[[#Headers],[QTY +/-]]:INDEX(M:M,ROW([@[QTY +/-]])))

 

[vppismail]

Certain types of formula are never as simple as they should be when you use them is structured tables.

There may be other ways to get the desired result, but this is the method that I use.

=SUMIF(Table14[[#Headers],[ITEM NO]]:INDEX(G:G,ROW([@[ITEM NO]])),[@[ITEM NO]],Table14[[#Headers],[QTY +/-]]:INDEX(M:M,ROW([@[QTY +/-]])))

Wow it really helped, thank you so much and appreciated