LET + Filter formula help

[mporter785]

Can I get some help on this formula to make it work?

=(let(desc1,{" air","M5"}, desc2,{"E/P","ENDPLATE","END PLATE"}, desc3,$D2, desc4,{"REBUILD","REPAIR"}, Desc5, {"hsg","Housing"},
matg1,{"ss","316","306"}, matg2,{"GI","CS","DI"}, matg3,{"al"}, matg4,{"*"},
tblprod,[OffloadedPartsForecast3.xlsx]sab_prod!$A$2:$R$21245,
wdot,[OffloadedPartsForecast3.xlsx]sab_prod!$X$2:$X$21245, wodot,SIGN(MOD(INDEX(tblprod,,3),1)=0),
dateact,INDEX(tblprod,,17), datedep,IF(MOD(INDEX(tblprod,,3),1)<>0,INDEX(tblprod,,10),INDEX(tblprod,,17)),
descA,desc1,
descB, desc3,
descC, desc4,
descD,Desc5,
mats, matg1,
date, dateact,
itemtype, wodot,
res,FILTER( tblprod,
(MMULT(--(ISNUMBER(SEARCH(descA,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descA),1,1,0))>0)*
(MMULT(--(ISNUMBER(SEARCH(descB,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descB),1,1,0))>0)*
(MMULT(--(ISNUMBER(SEARCH(descC,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descC),1,1,0))=0)*
(MMULT(--(ISNUMBER(SEARCH(mats,INDEX(tblprod,,13)))),SEQUENCE(COUNTA(mats),1,1,0))>0)*
(date>=$L$3)*
(date<=$L$4)*
(INDEX(tblprod,,18)=FALSE)*
(itemtype) ),
stdrd,FILTER(tblprod,
(MMULT(--(ISNUMBER(SEARCH(descA,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descA),1,1,0))>0)*
(MMULT(--(ISNUMBER(SEARCH(descC,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descC),1,1,0))=0)*
(MMULT(--(ISNUMBER(SEARCH(mats,INDEX(tblprod,,13)))),SEQUENCE(COUNTA(mats),1,1,0))>0)*
(date>=$L$3)*
(date<=$L$4)*
(INDEX(tblprod,,18)=FALSE)*
(itemtype) ),
cmpt,FILTER(tblprod,
(MMULT(--(ISNUMBER(SEARCH(descD,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descD),1,1,0))>0)*
(MMULT(--(ISNUMBER(SEARCH(descC,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descC),1,1,0))=0)*
(MMULT(--(ISNUMBER(SEARCH(mats,INDEX(tblprod,,13)))),SEQUENCE(COUNTA(mats),1,1,0))>0)*
(date>=$L$3)*
(date<=$L$4)*
(INDEX(tblprod,,18)=FALSE)*
(itemtype) ),
IFERROR(SUM(INDEX(res,,6),(INDEX(stdrd,,6),(INDEX(cmpt,,6)),0)))

 

[KRice]

I recognize that formula! What type of issue are you seeing with it?

 

[KRice]

I can't confirm how the formula is working because the referenced worksheets aren't included. I see that you've expanded the formula to extract multiple sets of information meeting certain criteria. I don't think the formula exceeds any character limits in Excel, but I do see a couple of issues, especially with the last step. First, delete the leading left parentheses before LET, then in the last step where this appears:

IFERROR(SUM(INDEX(res,,6),(INDEX(stdrd,,6),(INDEX(cmpt,,6)),0)))

There are some misplaced parentheses in there. You might try eliminating the IFERROR wrapper, in which case the SUM function would look like this:

SUM( INDEX(res,,6) , INDEX(stdrd,,6) , INDEX(cmpt,,6) )

Following this SUM function, there should be one extra right parentheses to close off the LET function. So it would look like this:

=LET(desc1,{" air","M5"}, desc2,{"E/P","ENDPLATE","END PLATE"}, desc3,$D2, desc4,{"REBUILD","REPAIR"}, Desc5, {"hsg","Housing"},
matg1,{"ss","316","306"}, matg2,{"GI","CS","DI"}, matg3,{"al"}, matg4,{"*"},
tblprod,[OffloadedPartsForecast3.xlsx]sab_prod!$A$2:$R$21245,
wdot,[OffloadedPartsForecast3.xlsx]sab_prod!$X$2:$X$21245, wodot,SIGN(MOD(INDEX(tblprod,,3),1)=0),
dateact,INDEX(tblprod,,17), datedep,IF(MOD(INDEX(tblprod,,3),1)<>0,INDEX(tblprod,,10),INDEX(tblprod,,17)),
descA,desc1,  descB, desc3,  descC, desc4,  descD,Desc5,  mats, matg1,  date, dateact,  itemtype, wodot,
res,FILTER( tblprod,
(MMULT(--(ISNUMBER(SEARCH(descA,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descA),1,1,0))>0)*
(MMULT(--(ISNUMBER(SEARCH(descB,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descB),1,1,0))>0)*
(MMULT(--(ISNUMBER(SEARCH(descC,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descC),1,1,0))=0)*
(MMULT(--(ISNUMBER(SEARCH(mats,INDEX(tblprod,,13)))),SEQUENCE(COUNTA(mats),1,1,0))>0)*
(date>=$L$3) * (date<=$L$4) * (INDEX(tblprod,,18)=FALSE) * (itemtype) ),
stdrd,FILTER(tblprod,
(MMULT(--(ISNUMBER(SEARCH(descA,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descA),1,1,0))>0)*
(MMULT(--(ISNUMBER(SEARCH(descC,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descC),1,1,0))=0)*
(MMULT(--(ISNUMBER(SEARCH(mats,INDEX(tblprod,,13)))),SEQUENCE(COUNTA(mats),1,1,0))>0)*
(date>=$L$3) * (date<=$L$4) * (INDEX(tblprod,,18)=FALSE) * (itemtype) ),
cmpt,FILTER(tblprod,
(MMULT(--(ISNUMBER(SEARCH(descD,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descD),1,1,0))>0)*
(MMULT(--(ISNUMBER(SEARCH(descC,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descC),1,1,0))=0)*
(MMULT(--(ISNUMBER(SEARCH(mats,INDEX(tblprod,,13)))),SEQUENCE(COUNTA(mats),1,1,0))>0)*
(date>=$L$3) * (date<=$L$4) * (INDEX(tblprod,,18)=FALSE) * (itemtype) ),
SUM( INDEX(res,,6), INDEX(stdrd,,6), INDEX(cmpt,,6) ) )

As a spot check, before committing to returning the SUM, I would recommend assigning this "SUM" quantity to an intermediate variable, perhaps called "test"...and then follow that expression with each of the arguments in the SUM function to visually inspect what is being returned. Here is what the end of the formula would look like if you do this:

...(date>=$L$3) * (date<=$L$4) * (INDEX(tblprod,,18)=FALSE) * (itemtype) ),
test, SUM( INDEX(res,,6), INDEX(stdrd,,6), INDEX(cmpt,,6) ),
INDEX(res,,6)
 )

Then swap out INDEX(res,,6) for INDEX(stdrd,,6) to see what stdrd looks like, and finally change that to INDEX(cmpt,,6) to see what cmpt returns. You will need to do this somewhere that will accommodate the vertical array spilling down the column, each value representing a quantity from the 6th column of the results array. If all looks good, then delete the diagnostic INDEX and delete the "test" assignment to revert to returning the sum of three vertical arrays:

SUM( INDEX(res,,6), INDEX(stdrd,,6), INDEX(cmpt,,6) )

 

[KRice]

Another observation: you have the potential to double count some items using the logic shown. For example, regarding only the desc and mats items, “res” says that you want rows having any of descA AND any of descB AND any of mats AND none of any in descC. Then “stdrd” says that you want rows having the same thing except there is no descB constraint (so you’ll get all descB items, including those meeting the “res” requirement…hence double counting those items). To avoid this issue, you’ll need to revise the three sets of constraints to explicitly exclude those being counted by one of the other constraint sets.

 

[mporter785]

I am getting this error

OffloadedParts2.xlsx
	C	D	E	F	G	H	I
2	HSG,MD20,MACH,304	MD20	HOUSING	MD	#CALC!	1	#CALC!
LM

Cell Formulas
Range	Formula
G2	G2	=LET(desc1,{" air","M5"}, desc2,{"E/P","ENDPLATE","END PLATE"}, desc3,$D2, desc4,{"REBUILD","REPAIR"}, desc5, {"housing","hsg"}, matg1,{"ss","316","306"}, matg2,{"GI","CS","DI"}, matg3,{"al"}, matg4,{"*"}, tblprod,sab_prod!$A$2:$R$21309, wdot,sab_prod!$X$2:$X$21309, wodot,SIGN(MOD(INDEX(tblprod,,3),1)=0), dateact,INDEX(tblprod,,17), datedep,IF(MOD(INDEX(tblprod,,3),1)<>0,INDEX(tblprod,,10),INDEX(tblprod,,17)), descA,desc1, descB, desc3, descC, desc4, descD, desc5, mats, matg1, date, dateact, itemtype, wodot, res,FILTER( tblprod, (MMULT(--(ISNUMBER(SEARCH(descA,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descA),1,1,0))>0)* (MMULT(--(ISNUMBER(SEARCH(descB,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descB),1,1,0))>0)* (MMULT(--(ISNUMBER(SEARCH(descC,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descC),1,1,0))=0)* (MMULT(--(ISNUMBER(SEARCH(mats,INDEX(tblprod,,13)))),SEQUENCE(COUNTA(mats),1,1,0))>0)* (date>=$L$3)* (date<=$L$4)* (INDEX(tblprod,,18)=FALSE)* (itemtype) ), cmpt,FILTER( tblprod, (MMULT(--(ISNUMBER(SEARCH(descD,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descD),1,1,0))>0)* (MMULT(--(ISNUMBER(SEARCH(descB,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descB),1,1,0))>0)* (MMULT(--(ISNUMBER(SEARCH(descC,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descC),1,1,0))=0)* (MMULT(--(ISNUMBER(SEARCH(mats,INDEX(tblprod,,13)))),SEQUENCE(COUNTA(mats),1,1,0))>0)* (date>=$L$3)* (date<=$L$4)* (INDEX(tblprod,,18)=FALSE)* (itemtype) ), SUM(INDEX(res,,6),INDEX(cmpt,,6)))
H2	H2	=(LET(desc1,{" air","M5"}, desc2,{"E/P","ENDPLATE","END PLATE"}, desc3,$D2, desc4,{"REBUILD","REPAIR"}, matg1,{"ss","316","306"}, matg2,{"GI","CS","DI"}, matg3,{"al"}, matg4,{"*"}, tblprod,sab_prod!$A$2:$R$21309, wdot,sab_prod!$X$2:$X$21309, wodot,SIGN(MOD(INDEX(tblprod,,3),1)=0), dateact,INDEX(tblprod,,17), datedep,IF(MOD(INDEX(tblprod,,3),1)<>0,INDEX(tblprod,,10),INDEX(tblprod,,17)), descA,desc1, descB, desc3, descC, desc4, mats, matg1, date, datedep, itemtype, wdot, res,FILTER( tblprod, (MMULT(--(ISNUMBER(SEARCH(descA,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descA),1,1,0))>0)* (MMULT(--(ISNUMBER(SEARCH(descB,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descB),1,1,0))>0)* (MMULT(--(ISNUMBER(SEARCH(descC,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descC),1,1,0))=0)* (MMULT(--(ISNUMBER(SEARCH(mats,INDEX(tblprod,,13)))),SEQUENCE(COUNTA(mats),1,1,0))>0)* (date>=$L$3)* (date<=$L$4)* (INDEX(tblprod,,18)=FALSE)* (itemtype) ), IFERROR(SUM(INDEX(res,,6)),0)))
I2	I2	=[@[Number w/0 dotline]]*[@[Time Saved]]

 

[KRice]

One cause of the #CALC! error is when FILTER returns an empty set...that is, it finds nothing. That error might not be a problem. Are you certain that the search criteria specified will find something? To trap that type of error, I would wrap the function with an IFERROR, but I tend to do that only after confirming that the function is working as intended (otherwise the IFERROR can impeded troubleshooting).

Also, please note my recent comment about double counting.