# Complicated Cell Formula to VBA Formula

#### LHarting

##### New Member
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:

#### smozgur

##### BatCoder
Names used in the range, names used in names used in the range, names used in names, used in names u...
Exactly However, we are surely trying to implement anything doable that would make it a better tool. Thanks again for all your help to make it a better tool.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### jasonb75

##### Well-known Member
Why are you training IF statements and not using IFS?
Elaborating on why I said that IFS was less efficient.

If you look at the formula
Excel Formula:
``=IFERROR(IFS(A1=1,"result1",A1=2,"result2",A1=3,"result3"),0)``
even if the first result, A1=1 is true, the other sections are all still calculated even though they are not needed.

Using
Excel Formula:
``=IF(A1=1,"result1",IF(A1=2,"result2",IF(A1=3,"result3",0)))``
with the first result, A1=1 being true, the other sections are bypasses without being calculated.

The amount of effort wasted in calculating the unnecessary steps using IFS will be variable depending on the complexity of the calculations used, but it will always be less efficient than the equivalent nested IF's formula.

IFS is good for simplicity with very basic logical tests (as in the example I've used in this post) but that is about all.

You will also see that I took the unusual step of using
Excel Formula:
``=IF(IF(A1=1,A2=1),"Result1","Result2")``
Excel Formula:
``=IF(AND(A1=1,A2=1),"Result1","Result2")``
similar to the IFS method, with AND, all of the logical tests are carried out regardless of the individual results. With the double IF method, if the first is false, the second is irrelevant and is skipped.

You can do something similar with OR as well, but I was going into meltdown by the time I got to that point.

Excel Formula:
``=IF(OR(A1=1,A2=1),"Result1","Result2")``
Excel Formula:
``=IF(IF(A1=1,1,IF(A2=1)),"Result1","Result2")``
with this, if the first is true then that is good enough so the second one is skipped.
In the case of the AND / OR methods, it is best to put the simplest logical test first, so that the more complex one is eliminated in the cases where it is not needed.

#### jasonb75

##### Well-known Member
Thanks again for all your help to make it a better tool.
Anything that saves me a bit of typing in a future post, if it happens to help someone else as well...

#### LHarting

##### New Member
@jasonb75 I don't get why is this not calculating correctly? I get why it isn't calculating on the INTNL lines, but it should have 550 for the On Hand amount in the second line. I pulled the information from the Orc INV sheet (INV in my previous example.) and pasted it below the data to show there IS inventory.
Of course there is hidden named ranges INV_LAST_ROW which refers to =MATCH(1E+100,INV!\$C:\$C) and ORD_LAST_ROW which refers to =MATCH(1E+100,'Open Orders1'!\$B:\$B)

Orders to Release Work Macro3.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1KeySchedule Ship DateItem NumberOrdered QuantityTotal \$INTNLOn HandBal After OrderShipDel. Report Update StatusOracle StatusFGINTSTGFG & PARGInv. On HandOnlyFirst Stat.FirstMin Stat.MinAllocatedBoth / INTNL / Staged
310527732.1CHZ4/9/2021CHZ550\$20,784.50INTNL00INTNL ShipStaged/Pick ConfirmedStaged/Pick Confirmed0550055021200600Both
5
6Orc INV SHEET
7ITEMSUBINVENTORY_CODEQTY
8CHZSTG120
9CHZSTG120
10CHZSTG70
11CHZSTG120
12CHZSTG120
Open Orders1
Cell Formulas
RangeFormula
G2:G4G2=IF(OR(\$O2=0,(\$O2-\$U2)<0),0,IF(IF(\$V2="Both",(\$L2+\$M2)>0),IF(OR(\$P2=1,\$Q2=1),IF((\$L2+\$M2)>=\$D2,\$L2+\$M2,\$O2),IF(\$Q2>1,IF((\$L2+\$M2)>=\$D2,\$S2,\$O2-\$U2),0)),IF(IF(\$V2="INTNL",\$L2>0),IF(OR(\$P2=1,\$Q2=1),IF(\$L2>=\$D2,\$L2,SUM(\$L2,\$N2)),IF(IF(\$Q2>1,(\$L2-SUMIFS(\$D\$1:\$D1,\$C\$1:\$C1,\$C2,\$V\$1:\$V1,"Both"))>=\$D2),\$S2,0)),IF(IF(\$V2="Staged",(\$M2-\$U2)>0),IF(OR(\$P2=1,\$Q2=1),IF(\$M2>=\$D2,\$M2,SUM(\$M2:\$N2)),IF(\$Q2>1,IF(\$M2>=\$D2,\$T2,IF((\$M2-\$U2)<\$D2,\$O2-\$U2,0)),0)),IF(\$V2=0,IF(\$O2=\$N2,IF(OR(\$P2=1,\$Q2=1),\$N2,IF(\$Q2>1,IF(\$N2>=\$D2,\$S2,\$O2-\$U2),0)),IF(\$R2=1,\$O2-\$U2,IF(IF(\$R2>1,(\$O2-\$U2)>0),\$O2-\$U2,0))),IF(OR(IF(\$V2="INTNL",\$L2<=\$D2),IF(\$V2="Staged",\$M2<=\$D2),IF(\$V2="Both",(\$L2+\$M2)<=\$D2),OR(\$P2=1,\$R2=1)),\$N2-\$U2,IF(IF(\$R2>1,OR(IF(\$V2="INTNL",\$L2<=\$D2),IF(\$V2="Staged",(\$M2-\$U2)<=\$D2),IF(\$V2="Both",(\$L2+\$M2)<=\$D2))),IF(SUM(COUNTIFS(\$C\$1:\$C1,\$C2,\$V\$1:\$V1,{"Staged","Both","INTNL"}))>0,\$O2-\$U2,\$T2),"x")))))))
H2:H4H2=IF(\$G2<=0,0,\$G2-\$D2)
L2:M4L2=SUMIFS(InvQty,InvItem,\$C2,SUB,L\$1)
N2:N4N2=SUMIFS(InvQty,InvItem,\$C2,SUB,"FG")+SUMIFS(InvQty,InvItem,\$C2,SUB,"PARG")
O2:O4O2=SUM(\$L2:\$N2)
P2:P4P2=IF(\$O2=0,0,IF(\$R2>1,\$R2,COUNTIFS(Item,\$C2)))
Q2:Q4Q2=IF(\$O2=0,0,COUNTIFS(\$C\$2:\$C2,\$C2,\$V\$2:\$V2,\$V2))
R2:R4R2=IF(\$O2=0,0,IF(\$Q2>1,\$Q2,COUNTIF(\$C\$2:\$C2,\$C2)))
S2:S4S2=IF(OR(\$Q2:\$R2)>1,MINIFS(\$H\$1:\$H1,\$C\$1:\$C1,\$C2,\$V\$1:\$V1,\$V2),0)
T2:T4T2=IF(OR(\$Q2:\$R2)>1,MINIFS(\$H\$1:\$H1,\$C\$1:\$C1,\$C2),0)
U2:U4U2=IF(OR(\$Q2:\$R2)>1,MINIFS(\$D\$1:\$D1,\$C\$1:\$C1,\$C2),0)
Named Ranges
NameRefers ToCells
'Orc INV'!_FilterDatabase='Orc INV'!\$A\$1:\$C\$6L2:N4
InvItem='Orc INV'!\$A\$2:INDEX('Orc INV'!\$A:\$A,INV_LAST_ROW)L2:N4
InvQty='Orc INV'!\$C\$2:INDEX('Orc INV'!\$C:\$C,INV_LAST_ROW)L2:N4
Item='Open Orders1'!\$C\$2:\$C\$4P2:U4, L2:N2, G2:G4
SUB='Orc INV'!\$B\$2:INDEX('Orc INV'!\$B:\$B,INV_LAST_ROW)L2:N4

#### jasonb75

##### Well-known Member
It looks as if you may have pointed part of the formula to the wrong range when adapting it to your sheet, but there may be another problem that needs fixing as well. I'll wait until you confirm so that I can work on both problems at the same time rather than possibly having to change some parts twice.

I notice that you've changed SUMIFS to MINIFS in column U, but when I change that in mine the results are still different so there is something else that I haven't found yet.

What should the third row be showing in the example? I've just tried the same data on my test sheet and these are the results that I'm getting.
(going back to the earlier post where you broke down your original formula, which path should each in the new example be following)?

BCDEFGHIJKLMNOPQRS
1Schedule Ship DateItem NumberOrdered QuantityINTNLOn HandBal After OrderFGINTSTGPARGFGAllOnlyFirst statFirstMin StatMinPrev OrdStatus
204/06/2021CHZ600000055000550311000INTNL
304/09/2021CHZ5500550005500055021200600Both
404/09/2021CHZ2000-11500055000550222001150INTNL
Open Orders1
Cell Formulas
RangeFormula
F2:F4F2=IF(L2=0,0,IF(IF(S2="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(S2="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,\$S\$1:\$S1,"Both"))>=D2),P2,0)),IF(IF(S2="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(S2=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(S2="INTNL",H2<=D2),IF(S2="Staged",I2<=D2),IF(S2="Both",(H2+I2)<=D2),OR(M2=1,O2=1)),J2+K2-R2,IF(IF(O2>1,OR(IF(S2="INTNL",H2<=D2),IF(S2="Staged",(I2-R2)<=D2),IF(S2="Both",(H2+I2)<=D2))),IF(SUM(COUNTIFS(\$C\$1:\$C1,C2,\$S\$1:\$S1,{"Staged","Both","INTNL"}))>0,L2-R2,Q2),"x")))))))
G2:G4G2=IF(F2<=0,0,F2-D2)
H2:H4H2=SUMIFS(INVqty,INVItem,C2,INVsub,"FGINT")
I2:I4I2=SUMIFS(INVqty,INVItem,C2,INVsub,"STG")
J2:J4J2=SUMIFS(INVqty,INVItem,C2,INVsub,"PARG")
K2:K4K2=SUMIFS(INVqty,INVItem,C2,INVsub,"FG")
L2:L4L2=SUM(H2:K2)
M2:M4M2=IF(L2=0,0,IF(O2>1,O2,COUNTIFS(ItemAll,C2)))
N2:N4N2=IF(L2=0,0,COUNTIFS(\$C\$2:\$C2,C2,\$S\$2:\$S2,S2))
O2:O4O2=IF(L2=0,0,IF(N2>1,N2,COUNTIFS(\$C\$2:\$C2,C2)))
P2:P4P2=IF(OR(N2=1,O2>1),MINIFS(\$G\$1:\$G1,\$C\$1:\$C1,C2,\$S\$1:\$S1,S2),0)
Q2:Q4Q2=IF(OR(N2=1,O2>1),MINIFS(\$G\$1:\$G1,\$C\$1:\$C1,C2),0)
R2:R4R2=IF(OR(N2=1,O2>1),SUMIFS(\$D\$1:\$D1,\$C\$1:\$C1,C2),0)
Named Ranges
NameRefers ToCells
INVItem=INV!\$A\$2:INDEX(INV!\$A:\$A,INV_LR)H2:K4
INVqty=INV!\$C\$2:INDEX(INV!\$C:\$C,INV_LR)H2:K4
INVsub=INV!\$B\$2:INDEX(INV!\$B:\$B,INV_LR)H2:K4
ItemAll='Open Orders1'!\$C\$2:INDEX('Open Orders1'!\$C:\$C,ORD_LR)H2:K2, F2:F4, M2:R4

#### jasonb75

##### Well-known Member
Following up on my reply above, I have found another difference.

You've changed the first criteria in the main formula to
Excel Formula:
``OR(\$O2=0,(\$O2-\$U2)<0))``

In the second row that will evaluate to OR(\$O2=0,(550-600)<0)) which is true, so the result of 0 would be correct for that row.

Replies
3
Views
138
Replies
5
Views
157
Replies
5
Views
88
Replies
1
Views
192
Replies
1
Views
200

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.

### Which adblocker are you using?

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

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