Complicated Cell Formula to VBA Formula

LHarting

New Member
Joined
Nov 1, 2018
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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:
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 1, 2018
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Nov 1, 2018
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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:
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 1, 2018
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 1, 2018
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
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.

Excel Formula:
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?

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

Watch MrExcel Video

Forum statistics

Threads
1,129,917
Messages
5,638,981
Members
417,061
Latest member
thematulaak

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
Top