Quantity deduction workaround
I want to deduct the total "quantity requested" in the "final stock of inventory" after selected the "collected" check box
and also wanted to show the available stock before confirming the order, Please help me with the workaround/ formula to achieve this
Currently I use this formula, =[Initial Stock]@row  IF(Collected@row, 0, SUMIF([Item Name]$1:[Item Name]@row, [Item Name]@row, [Total Quantity Requested]$1:[Total Quantity Requested]@row)) but its not working as expected
Answers

I want to minus the total qunatity requested from final stock of inventory if the collected checkbox is checked, if the checkbox is not selected then it should show the remaining quantity but here final stock of inventory and available stockbefore calculation is not right, i have been trying hard to solve this issue. please help me with the right formula.
using this formula in "final stock of inventory" : =[Initial Stock]@row  IF(Collected@row, SUMIF([Item Name]$1:[Item Name]@row, [Item Name]@row, [Total Quantity Requested]$1:[Total Quantity Requested]@row))
this formula in  available stockBefore : =IF(Collected@row, [Final Stock of Inventory]@row + [Total Quantity Requested]@row, [Final Stock of Inventory]@row)
@Paul Newcome @Genevieve P. @SoS  Dan Palenchar PLEASE HELP!

For the Final Stock Inventory Column, if I am understanding correctly you are wanting to subtract the total requested inventory from the row above that is checked from the initial stock and if the collected box is not check you want it to show the quantity that was available on the last row where the checked box occured.
If I am understanding correctly the below formula should work  @Domnic Victor edited formula if you saw before forgot to include the item name criteria.
=[Initial Stock]@row  SUMIFS([Total Quantity Requested]$1:[Total Quantity Requested]@row, Collected$1:Collected@row, 1, [Item Name]$1:[Item Name]@row, [Item Name]@row)

For the Available Stock  Before, if I am understanding correctly and you want it to add the Total Quantity requested and the Final Stock inventory if the box is checked and if the box is not checked then put the Final Stock Inventory then the below formula should work.
=IF(Collected@row = 1, [Total Quantity Requested]@row + [Final Stock Inventory]@row, [Final Stock Inventory]@row)

@Hollie Green Thank you so much. First formula works very well.
But second formula which will show available stock before is not working properly, Its adding the qty if the check box is unselected
=IF(Collected@row = "1", [Final Stock of Inventory]@row, [Total Quantity Requested]@row + [Final Stock of Inventory]@row)
Could you please check and advise

You have it backwards If you look at my formula above I have the addition part of the formula right after the collected@row=1 and you have it at the end. The order matters in an If formula.

yeah, that's working. Thanks a lot
Another one thing is I want to bring the final stock of inventory to inventory master sheet to show the remaining qty
=IFERROR(INDEX(COLLECT({eStore: Request Sheet  Final Stock of Inventory}, {EStore: Request Sheet  Item Name}, @cell = [Item Name]@row), COUNTIFS({EStore: Request Sheet  Item Name}, @cell = [Item Name]@row)), [Total Stock Qty]@row)
I used the above formula to get it but I get #INVALID COLUMN VALUE error if the items are not available in the request sheet

Hi, I would like to subtract quantity from final stock of inventory if the collected checkbox is checked, =IF(Collected@row = "1", [Final Stock of Inventory]@row, [Total Quantity Requested]@row + [Final Stock of Inventory]@row) I used this formula to calculate but the problem here is if I select the check box for the last order(ORD0285) the previous quantity is not showing correct, can you please help with the right formula,

@Hollie Green please help! the previous order is not showing the right quantity.
The idea is to subtract the quantity if the collected checkbox is checked, I want to show what was available before and after the order, please help me with the workaround

What should it be showing? It looks accurate to me as before ORD0284 there were 5 available and ORD0284 has not been collected so the amount available after that order should still be 5

after checking the collected checkbox the final stock of inventory is "2" but ord262,ord263,ord264,ord283 its still showing "5" correct me if i'm wrong

It depends on what you are wanting the final stock column to say.
From my understanding you wanted it be what the final stock was after that particular order with them going in order. If you want the final stock number to drop based on all collected orders regardless if the Order was placed before or after that order then it would be a different formula and all of the final stock number would be the same number which in your example would be 2 including on rows ORDO260 and ORD0261
The formula for that would be =[Initial Stock]@row  SUMIFS([Total Quantity Requested]:[Total Quantity Requested], [Item Name]:[Item Name], =[Item Name]@row, Collected:Collected, =1)
Help Article Resources
Categories
Check out the Formula Handbook template!