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:
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
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),
In addition to the points in my earlier post above, is this part correct?

Obviously, you will know the requirement far better than I will, but the absence of the status in the first part looks wrong to me.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

LHarting

New Member
Joined
Nov 1, 2018
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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?
Just those three. If the order is International, then I want to pull International and regular inventory, Not inventory that may be Staged for a different Order.
Same but reversed with Staged. I want to pull Staged and regular inventory, but not inventory specific to International.
 

LHarting

New Member
Joined
Nov 1, 2018
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
In addition to the points in my earlier post above, is this part correct?

Obviously, you will know the requirement far better than I will, but the absence of the status in the first part looks wrong to me.
What I'm looking for here is if it's the only time the Item appears on the report, or if it's the first time the item is labeled as "Both."
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
This is what I've come up with using formulas and named ranges, it is not instant, but significantly faster than the original method. I have used a few more helper columns than I suggested originally, but these are a necessary evil to improve the performance.

I've made an assumption that "YARD" is the fifth and final possible status in the SUBINVENTORY_CODE column (no empty cells), if this is not the case then it will need another slight change which will slow it down a little.

In summary, the 'All' column calculates the non 'Yard' inventory, which I've assumed will be the same as SUM{FGINT,STG,PARG,FG}. By doing it this way, there is only one sumifs formula to calculate per row where there is a zero inventory instead of 4 or more. With the example file, this reduces the number of sumifs formulas to calculate from ~22000 to ~17000, which is quite a difference.

Several of the helper columns use simple logical tests to eliminate unnecessary complex calculations as much as possible, as does the main formula in column F. I've cut it down as much as possible without compromising the functionality, the sumifs and countifs functions that I've left in the main formula are only used once each so splitting them into helper columns would actually make them less efficient. The others are all used multiple times, so by using the helper columns they only need to be calculated once each.

I did have more named ranges originally, but I've reduced that for simplicity and only used them where the number of rows needs to be variable.

There are 2 still 2 ranges not showing in the list, those are:-

INV_LR which refers to =MATCH(1E+100,INV!$C:$C)
ORD_LR which refers to =MATCH(1E+100,'Open Orders1'!$B:$B)

These are both use in other named ranges, but not in the actual sheet so the XL2BB add-in does not pick them up. @smozgur tagged for reference.

Hopefully this is of some use

Latest.xlsx
BCDEFGHIJKLMNOPQRSTUV
1Schedule Ship DateItem NumberOrdered QuantityINTNLOn HandBal After OrderFGINTSTGPARGFGAllOnlyFirst statFirstMin StatMinPrev OrdReport 1 Update StatusReport 2 StatusStatus
217/06/2020492633-1691000000000000000
306/10/2020NPT4000000000000000
412/10/2020453634-000200000000000000Backordered0
526/10/2020T063DEW2364013H2403363120336013361672611000Released to WarehouseStaged
Open Orders1
Cell Formulas
RangeFormula
F2:F5F2=IF(L2=0,0,IF(IF(V2="Both",(H2+I2)>0),IF(OR(M2:N2=1),IF((H2+I2)>=D2,H2+I2,L2),IF(N2>1,IF((H2+I2)>=D2,P2,L2-R2),0)),IF(IF(V2="INTNL",H2>0),IF(OR(M2:N2=1),IF(H2>=D2,H2,SUM(H2,J2,K2)),IF(IF(N2>1,(H2-SUMIFS($D$1:$D1,$C$1:$C1,C2,$V$1:$V1,"Both"))>=D2),P2,0)),IF(IF(V2="Staged",(I2-R2)>0),IF(OR(M2:N2=1),IF(I2>=D2,I2,SUM(I2:K2)),IF(N2>1,IF(I2>=D2,Q2,IF((I2-R2)<D2,L2-R2,0)),0)),IF(V2=0,IF(L2=(J2+K2),IF(OR(M2:N2=1),J2+K2,IF(N2>1,IF((J2+K2)>=D2,P2,L2-R2),0)),IF(O2=1,L2-R2,IF(IF(O2>1,(L2-R2)>0),L2-R2,0))),IF(OR(IF(V2="INTNL",H2<=D2),IF(V2="Staged",I2<=D2),IF(V2="Both",(H2+I2)<=D2),OR(M2=1,O2=1)),J2+K2-R2,IF(IF(O2>1,OR(IF(V2="INTNL",H2<=D2),IF(V2="Staged",(I2-R2)<=D2),IF(V2="Both",(H2+I2)<=D2))),IF(SUM(COUNTIFS($C$1:$C1,C2,$V$1:$V1,{"Staged","Both","INTNL"}))>0,L2-R2,Q2),"x")))))))
G2:G5G2=IF(F2<=0,0,F2-D2)
H2:H5H2=IF(L2=0,0,SUMIFS(INVqty,INVItem,C2,INVsub,"FGINT"))
I2:I5I2=IF(L2=0,0,SUMIFS(INVqty,INVItem,C2,INVsub,"STG"))
J2:J5J2=IF(L2=0,0,SUMIFS(INVqty,INVItem,C2,INVsub,"PARG"))
K2:K5K2=IF(L2=0,0,L2-SUM(H2:J2))
L2:L5L2=SUMIFS(INVqty,INVItem,C2,INVsub,"<>YARD")
M2:M5M2=IF(L2=0,0,IF(O2>1,O2,COUNTIFS(ItemAll,C2)))
N2:N5N2=IF(L2=0,0,COUNTIFS($C$2:$C2,C2,$V$2:$V2,V2))
O2:O5O2=IF(L2=0,0,IF(N2>1,N2,COUNTIFS($C$2:$C2,C2)))
P2:P5P2=IF(OR(N2:O2>1),MINIFS($G$1:$G1,$C$1:$C1,C2,$V$1:$V1,V2),0)
Q2:Q5Q2=IF(OR(N2:O2>1),MINIFS($G$1:$G1,$C$1:$C1,C2),0)
R2:R5R2=IF(OR(N2:O2>1),SUMIFS($D$1:$D1,$C$1:$C1,C2),0)
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
INVItem=INV!$A$2:INDEX(INV!$A:$A,INV_LR)L2:L5, H2:J5
INVqty=INV!$C$2:INDEX(INV!$C:$C,INV_LR)L2:L5, H2:J5
INVsub=INV!$B$2:INDEX(INV!$B:$B,INV_LR)L2:L5, H2:J5
ItemAll='Open Orders1'!$C$2:INDEX('Open Orders1'!$C:$C,ORD_LR)L2, H2:J2, F2:F5, M2:R5
 
Solution

LHarting

New Member
Joined
Nov 1, 2018
Messages
17
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

This is what I've come up with using formulas and named ranges, it is not instant, but significantly faster than the original method. I have used a few more helper columns than I suggested originally, but these are a necessary evil to improve the performance.

I've made an assumption that "YARD" is the fifth and final possible status in the SUBINVENTORY_CODE column (no empty cells), if this is not the case then it will need another slight change which will slow it down a little.

In summary, the 'All' column calculates the non 'Yard' inventory, which I've assumed will be the same as SUM{FGINT,STG,PARG,FG}. By doing it this way, there is only one sumifs formula to calculate per row where there is a zero inventory instead of 4 or more. With the example file, this reduces the number of sumifs formulas to calculate from ~22000 to ~17000, which is quite a difference.

Several of the helper columns use simple logical tests to eliminate unnecessary complex calculations as much as possible, as does the main formula in column F. I've cut it down as much as possible without compromising the functionality, the sumifs and countifs functions that I've left in the main formula are only used once each so splitting them into helper columns would actually make them less efficient. The others are all used multiple times, so by using the helper columns they only need to be calculated once each.

I did have more named ranges originally, but I've reduced that for simplicity and only used them where the number of rows needs to be variable.

There are 2 still 2 ranges not showing in the list, those are:-

INV_LR which refers to =MATCH(1E+100,INV!$C:$C)
ORD_LR which refers to =MATCH(1E+100,'Open Orders1'!$B:$B)

These are both use in other named ranges, but not in the actual sheet so the XL2BB add-in does not pick them up. @smozgur tagged for reference.

Hopefully this is of some use

Latest.xlsx
BCDEFGHIJKLMNOPQRSTUV
1Schedule Ship DateItem NumberOrdered QuantityINTNLOn HandBal After OrderFGINTSTGPARGFGAllOnlyFirst statFirstMin StatMinPrev OrdReport 1 Update StatusReport 2 StatusStatus
217/06/2020492633-1691000000000000000
306/10/2020NPT4000000000000000
412/10/2020453634-000200000000000000Backordered0
526/10/2020T063DEW2364013H2403363120336013361672611000Released to WarehouseStaged
Open Orders1
Cell Formulas
RangeFormula
F2:F5F2=IF(L2=0,0,IF(IF(V2="Both",(H2+I2)>0),IF(OR(M2:N2=1),IF((H2+I2)>=D2,H2+I2,L2),IF(N2>1,IF((H2+I2)>=D2,P2,L2-R2),0)),IF(IF(V2="INTNL",H2>0),IF(OR(M2:N2=1),IF(H2>=D2,H2,SUM(H2,J2,K2)),IF(IF(N2>1,(H2-SUMIFS($D$1:$D1,$C$1:$C1,C2,$V$1:$V1,"Both"))>=D2),P2,0)),IF(IF(V2="Staged",(I2-R2)>0),IF(OR(M2:N2=1),IF(I2>=D2,I2,SUM(I2:K2)),IF(N2>1,IF(I2>=D2,Q2,IF((I2-R2)<D2,L2-R2,0)),0)),IF(V2=0,IF(L2=(J2+K2),IF(OR(M2:N2=1),J2+K2,IF(N2>1,IF((J2+K2)>=D2,P2,L2-R2),0)),IF(O2=1,L2-R2,IF(IF(O2>1,(L2-R2)>0),L2-R2,0))),IF(OR(IF(V2="INTNL",H2<=D2),IF(V2="Staged",I2<=D2),IF(V2="Both",(H2+I2)<=D2),OR(M2=1,O2=1)),J2+K2-R2,IF(IF(O2>1,OR(IF(V2="INTNL",H2<=D2),IF(V2="Staged",(I2-R2)<=D2),IF(V2="Both",(H2+I2)<=D2))),IF(SUM(COUNTIFS($C$1:$C1,C2,$V$1:$V1,{"Staged","Both","INTNL"}))>0,L2-R2,Q2),"x")))))))
G2:G5G2=IF(F2<=0,0,F2-D2)
H2:H5H2=IF(L2=0,0,SUMIFS(INVqty,INVItem,C2,INVsub,"FGINT"))
I2:I5I2=IF(L2=0,0,SUMIFS(INVqty,INVItem,C2,INVsub,"STG"))
J2:J5J2=IF(L2=0,0,SUMIFS(INVqty,INVItem,C2,INVsub,"PARG"))
K2:K5K2=IF(L2=0,0,L2-SUM(H2:J2))
L2:L5L2=SUMIFS(INVqty,INVItem,C2,INVsub,"<>YARD")
M2:M5M2=IF(L2=0,0,IF(O2>1,O2,COUNTIFS(ItemAll,C2)))
N2:N5N2=IF(L2=0,0,COUNTIFS($C$2:$C2,C2,$V$2:$V2,V2))
O2:O5O2=IF(L2=0,0,IF(N2>1,N2,COUNTIFS($C$2:$C2,C2)))
P2:P5P2=IF(OR(N2:O2>1),MINIFS($G$1:$G1,$C$1:$C1,C2,$V$1:$V1,V2),0)
Q2:Q5Q2=IF(OR(N2:O2>1),MINIFS($G$1:$G1,$C$1:$C1,C2),0)
R2:R5R2=IF(OR(N2:O2>1),SUMIFS($D$1:$D1,$C$1:$C1,C2),0)
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
INVItem=INV!$A$2:INDEX(INV!$A:$A,INV_LR)L2:L5, H2:J5
INVqty=INV!$C$2:INDEX(INV!$C:$C,INV_LR)L2:L5, H2:J5
INVsub=INV!$B$2:INDEX(INV!$B:$B,INV_LR)L2:L5, H2:J5
ItemAll='Open Orders1'!$C$2:INDEX('Open Orders1'!$C:$C,ORD_LR)L2, H2:J2, F2:F5, M2:R5
Sub inventory Possibilities include, but aren't limited to: FG, REPAIR, STG, RP, QC, FGINT, PARG, YARD, LEAN, MODOUT, FGFPK, TRNST, CHINA, MOD
 

LHarting

New Member
Joined
Nov 1, 2018
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
Why are you training IF statements and not using IFS?

I can't Ctrl+Shift to enter the array formulas, because this is mid-macro. How to I do array formulas in VBA?
 

jasonb75

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

ADVERTISEMENT

IFS is less efficient than IF(IF(

I've reposted the formula below as a non-array version, there were only a couple of small parts that needed it, but they are easily changed.

Excel Formula:
=IF(L2=0,0,IF(IF(V2="Both",(H2+I2)>0),IF(OR(M2=1,N2=1),IF((H2+I2)>=D2,H2+I2,L2),IF(N2>1,IF((H2+I2)>=D2,P2,L2-R2),0)),IF(IF(V2="INTNL",H2>0),IF(OR(M2=1,N2=1),IF(H2>=D2,H2,SUM(H2,J2,K2)),IF(IF(N2>1,(H2-SUMIFS($D$1:$D1,$C$1:$C1,C2,$V$1:$V1,"Both"))>=D2),P2,0)),IF(IF(V2="Staged",(I2-R2)>0),IF(OR(M2=1,N2=1),IF(I2>=D2,I2,SUM(I2:K2)),IF(N2>1,IF(I2>=D2,Q2,IF((I2-R2)<D2,L2-R2,0)),0)),IF(V2=0,IF(L2=(J2+K2),IF(OR(M2=1,N2=1),J2+K2,IF(N2>1,IF((J2+K2)>=D2,P2,L2-R2),0)),IF(O2=1,L2-R2,IF(IF(O2>1,(L2-R2)>0),L2-R2,0))),IF(OR(IF(V2="INTNL",H2<=D2),IF(V2="Staged",I2<=D2),IF(V2="Both",(H2+I2)<=D2),OR(M2=1,O2=1)),J2+K2-R2,IF(IF(O2>1,OR(IF(V2="INTNL",H2<=D2),IF(V2="Staged",(I2-R2)<=D2),IF(V2="Both",(H2+I2)<=D2))),IF(SUM(COUNTIFS($C$1:$C1,C2,$V$1:$V1,{"Staged","Both","INTNL"}))>0,L2-R2,Q2),"x")))))))

With the additional possibilities for sub inventory, the formulas in H2:K2 will need changing to
Excel Formula:
=SUMIFS(INVqty,INVItem,C2,INVsub,"FGINT")
with the criteria matching the column heading for each of the 4 columns.

L2 will need to be changed to
Excel Formula:
=SUM(H2:K2)
 

LHarting

New Member
Joined
Nov 1, 2018
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
Thank you for all your help! I'll mark the above answer as solved. Again, thank you!
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,412
These are both use in other named ranges, but not in the actual sheet so the XL2BB add-in does not pick them up. @smozgur tagged for reference.
@jasonb75 - Thanks for tagging.
That's correct. XL2BB currently doesn't pick the named ranges used in other defined names. It's been on the development list since the initial version, however, I am not quite sure when/if it will be implemented in the current major version.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
Thanks for your feedback, @smozgur. I did wonder if it might have been a deliberate omission of something that might be rarely used but exceptionally difficult to code. I guess there has to be a limit as to how deep the code can drill into the core of the sheet. Names used in the range, names used in names used in the range, names used in names, used in names u... 🤯
 

Watch MrExcel Video

Forum statistics

Threads
1,129,916
Messages
5,638,972
Members
417,063
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