Complicated Cell Formula to VBA Formula

[LHarting]

Explanation: There are 4 Sub-inventories an item can be found in*. Staged to load (STG), International (INTNL/FGINT), an over-flow warehouse (PARG), or in the racks (FG).
An Sales orders are labeled International (Open Orders 1 sheet Column E), and the the Status of the order can be found in one of two reports (Columns I and J).
*There are more, but I'm just concerned about 4.

Here's where it gets complicated. An order's status can be Staged AND International (Both), and FG/Parg ITEMS can help cover both International and Staged ORDERS, but Staged Items can ONLY fill orders that are Staged until all Staged Orders are filled - then they can only fill orders that are International, and International Items cannot cover Orders that aren't International (or both).

International - International
Staged - Staged until a) The item is used up or b) there are no more Staged orders and now can be used for International.
Parg/FG - International, Staged, Neither.

The question is - What do we have in-stock?

This is my formula. There HAS to be a way to do this in VBA that's either easier or at least far more clean. HELP?

=IF(SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,VALUE($C2),INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0))=0,0,IFERROR(IFS(AND($K2="Both",IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"STG","FGINT"})),0)>0),IFERROR(IFS(OR(COUNTIF($C$2:$C$6500,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Both")=1),IF(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG"})),0)>='Open Orders1'!$D2,IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG"})),0),IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0)),COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Both")>1,IF(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG"})),0)>=$D2,MINIFS($G$1:$G1,$C$1:$C2,$C2,$K$1:$K2,"Both"),SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)))),0),AND($K2="INTNL",SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"FGINT")>0),IFERROR(IFS(OR(COUNTIF($C$2:$C$6500,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"INTNL")=1),IF(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"FGINT")>='Open Orders1'!$D2,SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"FGINT"),IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","PARG","FG"})),0)),AND(COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"INTNL")>1,SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"FGINT"),-IFERROR(SUMIFS($D$1:$D8037,$C$1:$C1,$C2,$K$1:$K1,"Both"),0))>=$D2),MINIFS($G$1:$G1,$C$1:$C1,$C2,$K$1:$K1,"INTNL")),0),AND($K2="Staged",SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG"),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))>0),IFERROR(IFS(OR(COUNTIF($C$2:$C$6500,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Staged")=1),IF(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG")>='Open Orders1'!$D2,IFERROR(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG"),0),IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"STG","PARG","FG"})),0)),COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Staged")>1,IFERROR(IFS(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG")>=$D2,MINIFS($G$1:$G1,$C$1:$C1,$C2),SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG"),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))<$D2,SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))),0)),0),$K2=0,IF(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0)=IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"PARG","FG"})),0),IFERROR(IFS(OR(COUNTIF($C$2:$C$6500,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,0)=1),IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"PARG","FG"})),0),COUNTIFS($C$2:$C2,$C2,$K$2:$K2,0)>1,IF(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"PARG","FG"})),0)>=$D2,MINIFS($G$1:$G1,$C$1:$C1,$C2,$K$1:$K1,0),SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)))),0),IFERROR(IFS(COUNTIF($C$2:$C2,$C2)=1,SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)),AND(COUNTIF($C$2:$C2,$C2)>1,SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))>0),SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))),0))),IFERROR(IFS(AND(OR(AND($K2="INTNL",SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"FGINT")<=$D2),AND($K2="Staged",SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG")<=$D2),AND($K2="Both",IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG"})),0)<=$D2),OR(COUNTIF($C$2:$C$6500,$C2)=1,COUNTIFS($C$2:$C2,$C2)=1))),SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)),AND(OR(AND($K2="INTNL",SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"FGINT")<=$D2),AND($K2="Staged",SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,"STG"),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))<=$D2),AND($K2="Both",IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG"})),0)<=$D2)),COUNTIFS($C$2:$C2,$C2)>1),IF(SUM(COUNTIFS($C$1:$C1,$C2,$K$1:$K1,{"Staged","Both","INTNL"}))>0,SUM(IFERROR(SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)),MINIFS($G$1:$G1,$C$1:$C1,$C2))),"x")))

Better just to link to an example:

Example.xlsx

drive.google.com

 

[LHarting]

Here's a breakdown of what I'm trying to accomplish with the formula, if it helps. I've already gotten rid of the Value function. The bold lines are sections of the formula, I think those are what I can change to If-Then-Else sections in my macro rather than a have a huge formula.

Returns a 0 if the sum of FGINT, STG, PARG, and FG sub-inventories for the item are 0.
=IF(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0)=0,0,
IFERROR(
IFS(
BOTH
If the item is both INTNL and has an Oracle Status of Staged/Pick Confirmed and/or Released to Warehouse, and the item is in STG and/or FGINT sub-inventories
AND($K2="Both",IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"STG","FGINT"})),0)>0),
First
If this is the first occurrence of this item or this item only appears once
IFERROR(IFS(OR(COUNTIF($C$2:$C$6000,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Both")=1),
If the Qty of the item in FGINT and STG sub-inventories is greater than or equal to the order amount
IF(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG"})),0)>='Open Orders1'!$D2,
Return the Qty of FGINT/STG sub-inventories
IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG"})),0),
Else return the qty of the item from all 4 sub-inventories.
IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0)),
Second or More
If this is the second or greater occurrence of this item
COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Both")>1,
If the qty of the item in FGINT and STG sub-inventories greater than or equal to the order amount, find the smallest Bal After Order amount that has Both labeled in Column K
IF(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG"})),0)>=$D2,MINIFS($G$1:$G1,$C$1:$C2,$C2,$K$1:$K2,"Both"),
Else, subtract every past Ordered Qty of the item from the sum of all 4 sub-inventories.
SUM(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)))),0),
INTNL
If the item is only INTNL, and the item is in the INTNL sub-inventory
AND($K2="INTNL",SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"FGINT")>0),
First
If this is the first occurrence of this item or this item only appears once
IFERROR(IFS(OR(COUNTIF($C$2:$C$6000,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"INTNL")=1),
If the Qty of the item in FGINT is greater than or equal to the Ordered Quantity
IF(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"FGINT")>='Open Orders1'!$D2,
Return the qty of the FGINT sub-inventory
SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"FGINT"),
Else return the qty of the item from all 4 sub-inventories.
IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","PARG","FG"})),0)),
Second or More
If this is the second or greater occurrence of this item
AND(COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"INTNL")>1,
And the sum of every previous ordered qty labeled "both" subtracted from the sum of the FGINT Sub-inventory is greater than the Ordered Qty
SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"FGINT"),-IFERROR(SUMIFS($D$1:$D8037,$C$1:$C1,$C2,$K$1:$K1,"Both"),0))>=$D2),
Find the smallest Bal After Order amount that has INTNL labeled in Column K
MINIFS($G$1:$G1,$C$1:$C1,$C2,$K$1:$K1,"INTNL")),
If all of the above is an error, put 0
0),
Staged
If the item is both marked "Staged", and the sum of the item in STG minus every previous order is greater than 0
AND($K2="Staged",SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"STG"),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))>0),
First
If this is the first occurrence of this item or this item only appears once
IFERROR(IFS(OR(COUNTIF($C$2:$C$6000,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Staged")=1),
If the Qty of the item in STG is greater than the Order Qty
IF(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"STG")>='Open Orders1'!$D2,
Return the qty of the item in STG sub-inventory
IFERROR(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"STG"),0),
Else return the qty of the item from all 4 sub-inventories.
IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"STG","PARG","FG"})),0)),
Second or More
If this is the second or greater occurrence of this item
COUNTIFS($C$2:$C2,$C2,$K$2:$K2,"Staged")>1,
And the qty of the item in STG sub-inventory is greater than the ordered qty
IFERROR(IFS(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"STG")>=$D2,
Find the smallest Bal After Order amount that has Staged labeled in Column K
MINIFS($G$1:$G1,$C$1:$C1,$C2),SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"STG"),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))<$D2,
ELSE subtract every previous order from all 4 sub-inventories
SUM(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))),0)),0),
Neither
If the item is not either Staged/Pick Confirmed, Released to Warehouse, or INTNL
$K2=0,
Not in STG or FGINT
If PARG+FG of the item equals the sum of all 4 sub-inventories
IF(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0)=IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"PARG","FG"})),0),
First
If this is the first occurrence of this item or this item only appears once
IFERROR(IFS(OR(COUNTIF($C$2:$C$6000,$C2)=1,COUNTIFS($C$2:$C2,$C2,$K$2:$K2,0)=1),
Return the qty of the item in PARG and FG sub-inventories
IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"PARG","FG"})),0),
Second or More
If this is the second or greater occurrence of this item
COUNTIFS($C$2:$C2,$C2,$K$2:$K2,0)>1,
If the sum of the PARG and FG sub-inventories is greater than the Ordered Qty
IF(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"PARG","FG"})),0)>=$D2,
Find the smallest Bal After Order amount that has 0 labeled in Column K
MINIFS($G$1:$G1,$C$1:$C1,$C2,$K$1:$K1,0),
ELSE subtract every previous order from all 4 sub-inventories
SUM(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)))),0),
In STG/FGINT AND PARG/FG
First
If the sum of PARG and FG sub-inventories does not equal the sum of all 4 sub-inventories and this is the first occurrence of this item
IFERROR(IFS(COUNTIF($C$2:$C2,$C2)=1,
Subtract every previous order from all 4 sub-inventories
SUM(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)),
Second or More
If the sum of PARG and FG sub-inventories does not equal the sum of all 4 sub-inventories and this is the second or greater occurrence of this item
AND(COUNTIF($C$2:$C2,$C2)>1,
And the sum of every previous order subtracted from all 4 sub-inventories is greater than 0
SUM(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))>0),
Sum of every previous order subtracted from all 4 sub-inventories
SUM(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))),
Else put 0
0))),
ERROR
If all the above is an error
IFERROR(
First
If the item is INTNL and/or has an Oracle Status of Staged/Pick Confirmed and/or Released to Warehouse, AND the sum of that sub-inventory is less than or equal to the order amount
If INTNL
IFS(AND(OR(AND($K2="INTNL",SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"FGINT")<=$D2),
If Staged
AND($K2="Staged",SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"STG")<=$D2),
If Both
AND($K2="Both",IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG"})),0)<=$D2),
If this is the first occurrence of this item
OR(COUNTIF($C$2:$C$6000,$C2)=1,COUNTIFS($C$2:$C2,$C2)=1))),
Subtract every previous order from the sum of the PARG/FG sub-inventories
SUM(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)),
Second or More
If this is not the first occurrence of this item and the item has been marked Staged/Both/or INTNL previously,
AND(OR(
If INTNL
AND($K2="INTNL",SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"FGINT")<=$D2),
If Staged
AND($K2="Staged",SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,"STG"),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))<=$D2),
If Both
AND($K2="Both",IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG"})),0)<=$D2)),
If item is not the first occurrence
COUNTIFS($C$2:$C2,$C2)>1),
If Item has been labeled as Staged, Both, or INTNL previously
IF(SUM(COUNTIFS($C$1:$C1,$C2,$K$1:$K1,{"Staged","Both","INTNL"}))>0,
Sum all 4 sub-inventories and subtract the sum of every previous order
SUM(IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})),0),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)),
ELSE find the smallest Bal After Order amount, and if all the above is an error, put "x"
MINIFS($G$1:$G1,$C$1:$C1,$C2))),"x")))

 

[LHarting]

Following up on my comment above, I found something in the formula that would cause an error, but that is due to bad syntax rather than calculation or data. This part will always return an error so is not serving the intended purpose.

MINIFS($G$1:$G1,$C$1:$C2,$C2,$K$1:$K2,"Both")

With that part (and any other similar sections) should the row containing the formula be included or excluded in the calculation?
You can't include it in one part of the formula but not another, $G$1:$G1,$C$1:$C2 and $K$1:$K2 must all have the same number of rows.

edit:- found another one with a similar problem, what should be used here? Why D8037 in the formula in F2?

-SUMIFS($D$1:$D8037,$C$1:$C1,$C2,$K$1:$K1,"Both")

That's really weird. I'm using the formula that I broke down above, and those errors aren't on it. The real file/formula is much bigger, so I used the real file, deleted lines, and copied the formula over to the example, which may have that caused those errors. I fixed them in the Example file now. Those snippets shouldn't be like that.

The MINIFS and SUMIFS calculate everything above the row. They shouldn't be including the row itself. The COUNTIF and COUNTIFS formulas should include the row.

 

[jasonb75]

That helps a lot! I was starting to break it down but it was getting to the point where everything starts to look the same.

Could you check back to post 10 and check what those parts of the formula should actually be. There might be other similar problems, but I've only found those 2 so far.

With those 2 parts corrected, there is no need for any error handling within the formula, which should eliminate everything from 'If all of the above is an error'
If those parts are not corrected then the sections of the formula that they are in will serve no purpose as they will always be errors and never return a valid result.

edit:-

Replies overlapped. The errors are in the formula (and in the post above). The MINIFS error is 2 lines above the bold 'INTNL', the SUMIFS error is in the section above the bold 'Staged'

As I said though, there is nothing that should trigger an error of any kind (once the corrections have been made to the above) so the whole ERROR section is redundant.

I'll have another look at it tomorrow evening, but in reality probably not going to look it too much detail until the weekend.

 

[LHarting]

One quick question, you have a lot of IFERROR's in the formula, are there any possible errors in your proper sheet that are not in the example you attached?

There is nothing in the formula itself, or the data in the file that would cause an error so potentially none of those error traps are needed.

If you could have errors (#VALUE!, #N/A, #DIV/0!, etc) in the data that the formulas refer to then they might be needed, but other than that there is a big chunk that can be removed quite easily.

I'm using most of the IFERRORs to return 0's if the item isn't in the Sub-Inventory the formula is looking for. The other use is basically giving a If-False to IFS. If the item not being in the Sub-Inventory won't mess with the formula, I can take those off.

 

[LHarting]

That helps a lot! I was starting to break it down but it was getting to the point where everything starts to look the same.

Could you check back to post 10 and check what those parts of the formula should actually be. There might be other similar problems, but I've only found those 2 so far.

With those 2 parts corrected, there is no need for any error handling within the formula, which should eliminate everything from 'If all of the above is an error'
If those parts are not corrected then the sections of the formula that they are in will serve no purpose as they will always be errors and never return a valid result.

edit:-

Replies overlapped. The errors are in the formula (and in the post above). The MINIFS error is 2 lines above the bold 'INTNL', the SUMIFS error is in the section above the bold 'Staged'

As I said though, there is nothing that should trigger an error of any kind (once the corrections have been made to the above) so the whole ERROR section is redundant.

I'll have another look at it tomorrow evening, but in reality probably not going to look it too much detail until the weekend.

Went though and fixed them! Thank you for finding that! I went through and fixed it everywhere I have the formula. That's a big help!

 

[jasonb75]

I've been looking over the formula at ways to make it more efficient, I know you asked for a vba equivalent, but it is not something that I would personally attempt with such a complex formula. With the number of views that your thread has received I suspect that others are of the same opinion.

Would it be permissible to add 4 extra columns of formulas to the open orders sheet?

The first part of the formula, SUM(SUMIFS(INV!$C$2:$C$31500,INV!$A$2:$A$31500,$C2,INV!$B$2:$B$31500,{"FGINT","STG","PARG","FG"})) is effectively 4 sumifs formulas in one, with each of them being used multiple times throughout the entire formula.

If you had each of the 4 sumifs formulas in a separate column then they would only need to be calculated once per formula instead of multiple times.

Another thing that would help significantly would be to use dynamic named ranges in place of fixed ranges that are larger than currently needed.

Final point that I've noticed so far, changing the use of IF(AND(a=b,x=y),z) to IF(a=b,IF(x=y,z)) would help to reduce the number of unnecessary complex calculations, although with the additional columns that I've suggested above, this may be less of a necessity.

If you could confirm whether or not the use of additional columns would be acceptable then I'll start tuning the formula based on your response.

 

[LHarting]

Sorry for the late reply.
Yes, I can add 4 extra ranges*. I think I originally did that and started overthinking how to balance the sum of the inventory from the INV sheet with what has already been allocated on Open Orders1. I ended up adding another 4 columns to calculate the running Ordered Quantity of the item for STG, INTNL, Both, and 0 so I could subtract them from the INV sums.... and at that point I went "Do you even formula, Bro?" and created the monster you are now unfortunately familiar with.

Ooooh! How do you add dynamic ranges without turning it into a table? My VBA in the example below finds the dynamic last range, but I'm not sure how to put that into a formula.

    'Find Last Row
        Dim DelivRow As Long
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Set RowCount = Application.Selection
        DelivRow = RowCount.Rows.Count

-Working on the IF(AND) question but wanted to give you a reply.)

*The actual Open Orders1 sheet has data in columns A-AH. That's past my personal point of seeing more columns as anything more than drops in a bucket, but 8+ was getting rather ridiculous.)

 

[jasonb75]

This is something that I started while I was waiting on your reply, I haven't started on the IF's, AND's, and IFS' yet, but you can see that it is already a lot shorter. I haven't done any timing on it, but it does appear to calculate a fair bit faster as well.

There are 2 named ranges that don't show in the table below due to them not actually being references directly in the worksheet, they are
ORD_LAST_ROW which refers to =MATCH(1E+100,'Open Orders1'!$B:$B)
INV_LAST_ROW which refers to =MATCH(1E+100,INV!$C:$C)

These are used to find the last row of the Open Orders sheet and INV sheet respectively, these are then used in several of the other named ranges in order to ensure that the ranges are consistent in size.

One other point to note, with the added columns for the sumifs formulas, the status has now been move to column O

I'll have a look at some of the other bits over the weekend to see what I can do improve the calculation times.

Example2.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Key	Schedule Ship Date	Item Number	Ordered Quantity	INTNL	On Hand	Bal After Order	FGINT	STG	PARG	FG
2	1803842.42.1492633-169	17/06/2020	492633-169	1	0	0	0	0	0	0	0
Open Orders1

Cell Formulas
Range	Formula
F2	F2	=IF(SUM(FGINT,STG,PARG,FG)=0,0,IFERROR(IFS(AND($O2="Both",SUM(FGINT,STG)>0),IFERROR(IFS(OR(COUNTIF(NUM,$C2)=1,COUNTIFS($C$2:$C2,$C2,$O$2:$O2,"Both")=1),IF(SUM(FGINT,STG)>=$D2,SUM(FGINT,STG),SUM(FGINT,STG,PARG,FG)),COUNTIFS($C$2:$C2,$C2,$O$2:$O2,"Both")>1,IF(SUM(FGINT,STG)>=$D2,MINIFS($G$1:$G1,$C$1:$C1,$C2,$O$1:$O1,"Both"),SUM(SUM(FGINT,STG,PARG,FG),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)))),0),AND($O2="INTNL",FGINT>0),IFERROR(IFS(OR(COUNTIF(NUM,$C2)=1,COUNTIFS($C$2:$C2,$C2,$O$2:$O2,"INTNL")=1),IF(FGINT>=$D2,FGINT,SUM(FGINT,PARG,FG)),AND(COUNTIFS($C$2:$C2,$C2,$O$2:$O2,"INTNL")>1,SUM(FGINT,-SUMIFS($D$1:$D1,$C$1:$C1,$C2,$O$1:$O1,"Both"))>=$D2),MINIFS($G$1:$G1,$C$1:$C1,$C2,$O$1:$O1,"INTNL")),0),AND($O2="Staged",SUM(STG,-SUMIFS($D$1:$D1,$C$1:$C1,$C2))>0),IFERROR(IFS(OR(COUNTIF(NUM,$C2)=1,COUNTIFS($C$2:$C2,$C2,$O$2:$O2,"Staged")=1),IF(STG>=$D2,STG,SUM(STG,PARG,FG)),COUNTIFS($C$2:$C2,$C2,$O$2:$O2,"Staged")>1,IFERROR(IFS(STG>=$D2,MINIFS($G$1:$G1,$C$1:$C1,$C2),SUM(STG,-SUMIFS($D$1:$D1,$C$1:$C1,$C2))<$D2,SUM(SUM(FGINT,STG,PARG,FG),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))),0)),0),$O2=0,IF(SUM(FGINT,STG,PARG,FG)=SUM(PARG,FG),IFERROR(IFS(OR(COUNTIF(NUM,$C2)=1,COUNTIFS($C$2:$C2,$C2,$O$2:$O2,0)=1),SUM(PARG,FG),COUNTIFS($C$2:$C2,$C2,$O$2:$O2,0)>1,IF(SUM(PARG,FG)>=$D2,MINIFS($G$1:$G1,$C$1:$C1,$C2,$O$1:$O1,0),SUM(SUM(FGINT,STG,PARG,FG),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)))),0),IFERROR(IFS(COUNTIF($C$2:$C2,$C2)=1,SUM(SUM(FGINT,STG,PARG,FG),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)),AND(COUNTIF($C$2:$C2,$C2)>1,SUM(SUM(FGINT,STG,PARG,FG),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))>0),SUM(SUM(FGINT,STG,PARG,FG),-SUMIFS($D$1:$D1,$C$1:$C1,$C2))),0))),IFERROR(IFS(AND(OR(AND($O2="INTNL",FGINT<=$D2),AND($O2="Staged",STG<=$D2),AND($O2="Both",SUM(FGINT,STG)<=$D2),OR(COUNTIF(NUM,$C2)=1,COUNTIFS($C$2:$C2,$C2)=1))),SUM(SUM(PARG,FG),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)),AND(OR(AND($O2="INTNL",FGINT<=$D2),AND($O2="Staged",SUM(STG,-SUMIFS($D$1:$D1,$C$1:$C1,$C2))<=$D2),AND($O2="Both",SUM(FGINT,STG)<=$D2)),COUNTIFS($C$2:$C2,$C2)>1),IF(SUM(COUNTIFS($C$1:$C1,$C2,$O$1:$O1,{"Staged","Both","INTNL"}))>0,SUM(SUM(FGINT,STG,PARG,FG),-SUMIFS($D$1:$D1,$C$1:$C1,$C2)),MINIFS($G$1:$G1,$C$1:$C1,$C2))),"x")))
G2	G2	=IF($F2<=0,0,$F2-$D2)
H2:K2	H2	=SUMIFS(QTY,ITEM,$C2,SUB,H$1)

Named Ranges
Name	Refers To	Cells
FG	='Open Orders1'!$K2	K2
FGINT	='Open Orders1'!$H2	H2
ITEM	=INV!$A$2:INDEX(INV!$A:$A,INV_LAST_ROW)	H2:K2
NUM	='Open Orders1'!$C$2:INDEX('Open Orders1'!$C:$C,ORD_LAST_ROW)	H2:K2, F2
PARG	='Open Orders1'!$J2	F2
QTY	=INV!$C$2:INDEX(INV!$C:$C,INV_LAST_ROW)	H2:K2
STG	='Open Orders1'!$I2	F2
SUB	=INV!$B$2:INDEX(INV!$B:$B,INV_LAST_ROW)	H2:K2

 

[LHarting]

Thank you!

 

[jasonb75]

Else return the qty of the item from all 4 sub-inventories.
IFERROR(SUM(SUMIFS('INV'$C$2:$C$31500,'INV'!$A$2:$A$31500,$C2,'INV'!$B$2:$B$31500,{"FGINT","PARG","FG"})),0)),

I've just been going over your breakdown of the formula again and noticed this part under the sections INTNL - First

There is also a similar anomaly under the Staged - First section, but with FGINT missing from the formula instead of STG

Should it be all 4 sub-inventories as the description says, or just the 3 used in that part of the formula?