SUMIFS filtering on dotline

[mporter785]

forum_ex.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Descritption	Material Number	EXCEL DESCRIPTION	Prod_Type	Prod_Cat.	Time Saved	Number w/0 dotline	Number with Dotline	Time Saved (W/o)	Time Save With
2	MD20 SS Housings	V353378.B01	HSG,MD20,MACH,304	HOUSING	MD	6	9	18	54	108
3	MD20 GI Housings India	V353377.B01	HSG,MD20,MACH,GI	HOUSING	MD	3	23	47	69	141
Airlocks

Cell Formulas
Range	Formula
G2	G2	=SUM(SUMIFS(sab_prod!F:F,sab_prod!G:G,"*MD20 AIR*",sab_prod!M:M,{"*SS*","*304*","*316*"},sab_prod!B:B,"<>*.*",shipact,">="&Airlocks!$L$3,shipact,"<="&Airlocks!$L$4,cancel1,"FALSE"))
H2	H2	=SUM(SUMIFS(sab_prod!F:F,sab_prod!G:G,"*MD20 air*",sab_prod!M:M,{"*SS*","*304*","*316*"},sab_prod!J:J,">="&Airlocks!$L$3,sab_prod!J:J,"<="&Airlocks!$L$4,cancel1,"FALSE"))
I2:I3	I2	='offloaded parts.xlsx'!TBL_SUM[@[Time Saved]]*'offloaded parts.xlsx'!TBL_SUM[@[Number w/0 dotline]]
J2:J3	J2	='offloaded parts.xlsx'!TBL_SUM[@[Time Saved]]*'offloaded parts.xlsx'!TBL_SUM[@[Number with Dotline]]
G3	G3	=SUM(SUMIFS(sab_prod!F:F,sab_prod!G:G,"*MD20 AIR*",sab_prod!M:M,{"*GI*","*CS*"},sab_prod!B:B,"<>*.*",shipact,">="&Airlocks!$L$3,shipact,"<="&Airlocks!$L$4,cancel1,"FALSE"))
H3	H3	=SUM(SUMIFS(sab_prod!F:F,sab_prod!G:G,"*MD20 air*",sab_prod!M:M,{"*GI*","*CS*"},sab_prod!J:J,">="&Airlocks!$L$3,sab_prod!J:J,"<="&Airlocks!$L$4,cancel1,"FALSE"))

Named Ranges
Name	Refers To	Cells
cancel1	=sab_prod!$R:$R	G2:H3
shipact	=sab_prod!$Q:$Q	G2:G3

job number	Column1	item	plant	customer	qty	description	dept	ship-date	job-pln	shp-pln	Price	mch-mat	mch-hsg	mch-epl	mch-rtr	ship-act	cancel	mch-cmpl	epl-stock	rtr-stock	misc-stock	edate
1100461677	10	10	SAB	WESTLAKE POLYMERS LP	1	MD20 AIRLOCK	M/S	4/8/2022	4/1/2022		8097	SS	SS	SP X	MD20,SS,OE,8VN,FR8,C1	4/7/2022 0:00	FALSE	TRUE	TRUE	TRUE	TRUE	April 2022
1100464389	10	10	SAB	JOHNSON MATTHEY PROCESS	1	MD20 AIRLOCK	M/S	4/8/2022	4/1/2022		11058	304L	304L X	SP CP 304L X	MD20,SS,CE,8VN,FR8,SPCL	4/14/2022 0:00	FALSE	TRUE	TRUE	TRUE	TRUE	April 2022
1100464570	10	10	SAB	HEARTLAND FOOD PRODUCTS	1	MD20 AIRLOCK	M/S	5/13/2022	5/6/2022		9628	SS	SS VNT X	SP X	MD20,SS,OE,8VN,ADJ32,CG80,SPCL 6-8	5/18/2022 0:00	FALSE	TRUE	TRUE	TRUE	TRUE	May 2022
1100464655	10	10	SAB	EMS CHEMIE NORTH AMERICA INC	1	MD20 AIRLOCK	M/S	4/8/2022	4/1/2022		6550	SS	SS	SP	MD20,SS,OE,8VN,FR16,C1	4/7/2022 0:00	FALSE	TRUE	TRUE	TRUE	TRUE	April 2022
1100465810	20	20	SAB	SOUTHWIRE MC CABLE	1	MD20 AIRLOCK	M/S	5/13/2022	5/6/2022		11484	SS	SS	SP X	MD20,304L,OE,8VN,FR8,CG24,RV,C1	5/11/2022 0:00	FALSE	TRUE	TRUE	TRUE	TRUE	May 2022
1100465898	30	30	SAB	SPECTRUM BRANDS PET GROUP INC	1	MD20 AIRLOCK	M/S	6/3/2022	6/1/2022		7362	SS	SS	SP X	MD20,304L,OE,8VN,FR8,C1		FALSE	TRUE	TRUE	TRUE	TRUE	June 2022
1100468055	10	10	SAB	LANXESS CORPORATION	1	MD20 AIRLOCK	M/S	5/27/2022	5/20/2022		9516	316SS	316SS X	SP X	MD20,316,OE,8VN,FR8,C1	5/23/2022 0:00	FALSE	TRUE	TRUE	TRUE	TRUE	May 2022
1100468088	10	10	SAB	ICL SPECIALTY PRODUCTS INC	1	MD20 AIRLOCK	M/S	7/1/2022	6/24/2022		16240	SS	SS	SP CP	RETURN-NEDOX		FALSE	FALSE	FALSE	FALSE	TRUE	June 2022
1100469357	10	10	SAB	DANISCO	1	MD20 AIRLOCK DANISCO	M/S	5/20/2022	5/13/2022		7848	SS	SS VNT PORT X	SP CPX	MD20,.304L,CE,8VN,FR16,CG80,C1	5/23/2022 0:00	FALSE	TRUE	TRUE	TRUE	TRUE	May 2022
1100471350	10	10	SAB	HEARTLAND FOOD PRODUCTS	1	MD20 AIRLOCK HEARTLAND PET FOOD	M/S	6/10/2022	6/3/2022		10037	SS	SS	SP X	MD20,304L,OE,8VN,FR8,CG80,CW,C1		FALSE	TRUE	TRUE	TRUE	TRUE	June 2022
1100471672	10	10	SAB	AMCOR WISCONSIN LLC	1	MD20 AIRLOCK	M/S	6/17/2022	6/10/2022		8977	SS	SS	SP X	MD20,304L,CE,8VN,FR8,C1		FALSE	FALSE	TRUE	TRUE	TRUE	June 2022
1100472165	10	10	SAB	ECOLAB KAY CHEMICAL CO	1	MD20 AIRLOCK	M/S	6/10/2022	6/3/2022		12784	316L	316L 32RA X	SP 32RA X	MD20,316,OE,8VN,FR16,CG150,RV,SPCL 32RA		FALSE	FALSE	TRUE	TRUE	TRUE	June 2022
1100472444	10	10	SAB	RB MANUFACTURING LLC	1	MD20 AIRLOCK RB MANUFACTURING LLC	M/S	7/15/2022	7/8/2022		8431	SS	SS X	SP CP X	MD20,304L,CE,8VN,FR8,C1		FALSE	FALSE	FALSE	FALSE	TRUE	July 2022
1100468088	40	40	SAB	ICL SPECIALTY PRODUCTS INC	1	MD20 ROTOR	M/S	7/1/2022	6/24/2022		2519	SS	NA	NA	RETURN=NEDOX		FALSE	FALSE	TRUE	FALSE	TRUE	June 2022
1100471359	10	10	SAB	HEARTLAND FOOD PRODUCTS	1	OE8,MD20,FR1/8,C1,80,304/L,CW *	M/S	4/22/2022	4/18/2022		1505	304L	NA	NA	MD20,304L,OE,8VN,FR8,CG80,C1,CW	4/26/2022 0:00	FALSE	TRUE	TRUE	FALSE	TRUE	April 2022
1100468088	10.1	10.1	SAB	ICL SPECIALTY PRODUCTS INC	1	MD20 AIRLOCK - food grade nedox	M/S	7/1/2022	6/17/2022			SS	NA	NA	MD20,304L,CE,8VN,FR16,RV,C2 NEDOX X		FALSE	FALSE	TRUE	FALSE	TRUE	June 2022
1100468088	40.1	40.1	SAB	ICL SPECIALTY PRODUCTS INC	1	MD20 ROTOR - food grade nedox	M/S	7/1/2022	6/17/2022 0:00			304L	NA	NA	MD20,MD20,304L,CE,8VN,FR16,RV,C1 NEDOX X		FALSE	FALSE	TRUE	FALSE	TRUE	June 2022
1100473944	10	10	SAB	WACKER CHEMICAL CORPORATION	1	MD20 AIRLOCK	M/S	8/12/2022	8/5/2022 0:00		14412	316SS	316SS	SP CP TS4	MD20,316,CE,8VN,FR16,RV,C1		FALSE	FALSE	FALSE	FALSE	TRUE	August 2022
1100475765	10	10	SAB	BIG HEART PET BRANDS	1	MD20 AIRLOCK	M/S	6/10/2022	6/3/2022 0:00		0	SS	SS	SP X	MD20,SS,OE,8VN,FR8,CG80,C2	6/7/2022 0:00	FALSE	TRUE	TRUE	TRUE	TRUE	June 2022
1100476297	10	10	SAB	VAN DIEST SUPPLY CO	1	OE8,MD20,FR1/8,C1,80RP,304/L	M/S	6/29/2022	6/24/2022 0:00		1939	304L	NA	NA	MD20,304L,OE,8VN,FR8,CG80,RP,C1		FALSE	FALSE	TRUE	FALSE	TRUE	June 2022

Given the SUMIFS equation to filter out the accurate count of the particular part, I want to sum all the items data table above. if there is a dot item on the item column, for example "10.1", I want to count only the 10.1 and not the 10 associated with the same customer. So, the customer ICL specialty products should only be counted once instead of twice. because it has a 10.1 and a 10. IF there is no dotline then the dates should be filtered between the [job-pln] column, if there is a dotline column, the result should be filtered between the [ship-act] column. know it's hard to explain. thanks for the help

 

[mporter785]

I get the "problem with this formula" error. I am using 365. i added the helper column and get ones and zeroes just as you.

=let(desc,"*MD 20", matg1, {"*SS*", "*304*", "*316*"}, matg2, {"*GI*","*CS*"}, tblprod, 'sab-prod'!A:R, maxitm,'sab-prod'!X:X,mats,matg1, date1,if(mod(index(tblprod,,3),1)<>0,index(tblprod,,10),index(tblprod,,17)), res,filter(filter(tblprob,(ISNUMBER(SEARCH(desc,index(tblprod,,7))))*(MMULT(--(ISNUMBER((SEARCH(mats,INDEX(tblprod,,13)))), SEQUENCE(COUNTA(mats),1,1,0))>0)*(date1>=Airlocks!$L$3)*(date1<=Airlocks!$L$4)*(INDEX(tblprod,,18)=FALSE)*(maxitm=)),{1,0,1,0,1,1,1,0,0,1,0,0,0,0,0,0,1,0}), sum(INDEX(res,,4)))

 

[KRice]

I'm assuming the 1's and 0's response refers to what you see in the helper column?
What column is the helper column? I added it to column Y...did you add it to X? Your formula suggests that you did.
Remove the wildcard "*" from the desc text and the matg1 and matg2 arrays...I don't think this is really a problem, but since the formula uses SEARCH, it looks for those substrings and wildcards should not be needed. I don't see any other issues, but let me know what you get.

 

[KRice]

Oh...I see the issue. You've changed the range so that it now includes the header row where text is found. So operations like MOD will throw an error. Change the references to something like X2:X1000 and A2:R1000...or something plenty long, but don't include row 1.

 

[mporter785]

Thank you for your help so far but I am still getting a #ref error and can't figure out why.

=LET(desc, "MD20 air", matg1, {"SS","304","316"}, matg2, {"GI","CS"}, tblprod, sab_prod!$A$2:$R$67, maxitm, sab_prod!$Y$2:$Y$67, mats, matg1, date, IF(MOD(INDEX(tblprod,,3),1)<>0,INDEX(tblprod,,10),INDEX(tblprod,,17)), res, FILTER(FILTER(tblprod, (ISNUMBER(SEARCH(desc,INDEX(tblprod,,7))))* (MMULT(--(ISNUMBER(SEARCH(mats,INDEX(tblprod,,13)))),SEQUENCE(COUNTA(mats),1,1,0))>0)* (date>=Airlocks!$L$3)* (date<=Airlocks!$L$4)* (INDEX(tblprod,,18)=FALSE)* (maxitm=1)), {1,0,1,0,1,1,1,0,0,1,0,0,0,0,0,0,1,0}), SUM(INDEX(res,,4)) )

 

[KRice]

That's puzzling...let's confirm some things because the #REF error suggests there is an invalid reference somewhere, which could happen if something is deleted or shifted on the worksheet.

For the sab_prod worksheet:

1. You have a helper column Y (you showed X in your previous post) on the sab_prod worksheet and the results in those cells make sense?... you see 1's in nearly all of the cells and 0's for those rows that should be excluded because of the dotline criteria?
2. The actual data in the sab_prod tables resides in rows 2:67?
3. The main body of data that is to be examined on sab_prod sheet is in sab_prod!$A$2:$R$67?
4. The column that holds the dot/no-dot values used for decision making is column C, the 3rd column?
5. The two date columns to be used are columns J and Q (columns 10 and 17), column J for dotlines, and column Q for no dots?
6. The Description column where we would find the text "MD20 air" is column G, the 7th column.
7. The mch-mat (material) column is column M, the 13th column?
8. The "cancel" column where we find mostly (all?) FALSE's is column R, the 18th column?
9. The "qty" column whose values are to be summed, subject to certain conditions, is column F, the 6th column on the sab_prod sheet?

For the Airlock worksheet:

1. The "date" range of interest is described on the Airlock sheet in only two cells: L3 and L4 for the early and late dates, respectively?

When I paste your formula into my workbook, it works fine...no adjustments needed...so I suspect there is some minor change in the tables.

 

[KRice]

I'm curious if you managed to get this working?

 

[mporter785]

I'm curious if you managed to get this working?

No, Im not able to. I don't know what the issue is.

 

[KRice]

Another option is to create a copy of the workbook and replace any sensitive information (if that information is critical for the formulas) with other non-sensitive information...and then upload the file to a common file-sharing site (e.g., Dropbox, Box, Google Drive, etc.) and share the link here.

 

[mporter785]

Here is a link. I tried adding the formula to sheet CM and FM.

public - Google Drive

drive.google.com

 

[KRice]

Thanks. If you go to the Airlocks worksheet and check the Date Range in L3:L4, you'll see that they are entered as text. For example, =TYPE(L3) for the start date returns a 2 indicating it is text (a numeric value would return a 1). The solution I posted assumes the date will be numeric. The easiest fix is to coerce those text-formatted dates into actual numeric date values by multiplying them by 1...so on the Airlocks worksheet, edit L3 and L4 to be:

=1*Datepicker!H2
=1*Datepicker!I2

Also modify these terms in the LET formula to cover the entire data range (full range shown here):

tblprod, sab_prod!$A$2:$R$1789, maxitm, sab_prod!$Y$2:$Y$1789

Then let me know if you see expected results.