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

vppismail

New Member
Joined
May 11, 2020
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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 +/-]])))
 
Upvote 0
Solution
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(y):)
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top