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

 

[jasonb75]

You could probably do it much cleaner with a formula. I notice that you have used IFS so that suggests a relatively up to date version of excel, do you have the LET function available? (one of the most recent additions in office 365 only).

It's going to take a while for anyone to break that formula down into sections to see what can be eliminated without compromising functionality but it should be possible.

 

[LHarting]

You could probably do it much cleaner with a formula. I notice that you have used IFS so that suggests a relatively up to date version of excel, do you have the LET function available? (one of the most recent additions in office 365 only).

It's going to take a while for anyone to break that formula down into sections to see what can be eliminated without compromising functionality but it should be possible.

I'm running Windows 10. Office 365 won't let me build/edit Macros. And this is going in a macro. That's part of why I'm trying to figure out how to change the block of a formula to a Loop-If that won't hog all of my memory.

Would a broken down formula help? I can at least start the process of breaking it down.

 

[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?
(lots of stuff)

Better just to link to an example:

Example.xlsx

drive.google.com

I guess my question is, if I change my massive formula to a Loop-IF-Then-Else, how can I calculate Sub-Inventory-specific Sumifs amount of the item the loop is on in the VBA's IF-Statement?

 

[jasonb75]

I'm not going to get chance to look at this properly until late afternoon UK time. Could you confirm if you have the LET function in your version of office 365 please. Not everyone has the update yet.

 

[LHarting]

I'm not going to get chance to look at this properly until late afternoon UK time. Could you confirm if you have the LET function in your version of office 365 please. Not everyone has the update yet.

I don't have Office 365 or the LET function.

 

[jasonb75]

I've started going over the formula and notice a lot of superfluous functions being used, it's going to take me a while to clean it up carefully so as not to compromise the results, but I just wanted to let you know that I haven't abandoned your thread.

First thing that I noticed in your formula was the parallel use of sumifs with and without VALUE checking the item number. With sumifs, you don't need the value function, unlike other formulas, proper numbers and numbers formatted as text will be comparable to each other.

 

[LHarting]

I've started going over the formula and notice a lot of superfluous functions being used, it's going to take me a while to clean it up carefully so as not to compromise the results, but I just wanted to let you know that I haven't abandoned your thread.

First thing that I noticed in your formula was the parallel use of sumifs with and without VALUE checking the item number. With sumifs, you don't need the value function, unlike other formulas, proper numbers and numbers formatted as text will be comparable to each other.

Oooh. That's good to know about Sumif. Thank you! And thank you for not abandoning me!

 

[jasonb75]

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.

 

[jasonb75]

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