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

 

[KRice]

My bad...I didn't understand that. The 'maxitm' formula/column accepts (assigns a flag of 1) if either of two scenarios is true:

1. there is a singular job number entry paired with only one item number that has no decimal component (which is why it seemed odd to call this a "with dot" condition...because there is no dot), and
2. there are multiple item numbers that have a common integer component and they all share the same job number, in which case only the maximum of these is accepted, and the other smaller decimals and the pure integer entry (if it exists) are ignored and flagged with a 0.

Based on your last clarification, it sounds like you want #1 and a variant of #2 that accepts all of the decimals but ignores the pure integer entry if it exists for this condition. Is that right?...and you're right, that's not what my revision does in post #29.

 

[mporter785]

correct, that is what I want it to do. scenario #1 and #2 variant.

 

[KRice]

Okay...that's a lot trickier because we want to keep the pure integers sometimes (when there are no decimal companions) but exclude them at other times (when there are decimal companions). I think this will indeed need to retain the helper column since we'll need to evaluate each row in [sab_prod] separately. Here is what I came up with for the former 'maxitm' helper column, now renamed 'dotitm'...formula shown in Y2 and pulled down to cover all rows in table:

forum_ex_v2.xlsx
	Y
1	dotitm
2	1
sab_prod

Cell Formulas
Range	Formula
Y2	Y2	=LET(jigrp,FILTER(Table1[item],(Table1[job number]=Table1[@[job number]])*(INT(Table1[item])=INT(Table1[@item]))),IF(MOD(Table1[@item],1)<>0,1,IF(AND(COUNT(jigrp)=1,MOD(Table1[@item],1)=0),1,0)))

This uses the FILTER function to assemble the relatively small set of item numbers where those item numbers share a common integer component and the same job number...and this is known as the job-item group, or 'jigrp' for short. Then we first evaluate the current row's item number and if it is a decimal, we assign a 1. If it is not a decimal (so it's a pure integer) AND it is the only element in the 'jigrp' we also assign a 1...otherwise we assign a 0.

Then we're back to the main LET function that uses this helper column for 'wdot':

=LET(desc1,{"MD20 air"},   desc2,{"E/P","ENDPLATE","END PLATE"},   desc3,{"MD20"},   desc4,{"ROTOR"},   desc5,{"*"},
         matg1,{"SS","304","316"},   matg2,{"GI","CS"},   matg3,{"*"},
         tblprod,sab_prod!$A$2:$R$1792,
         wdot,sab_prod!$Y$2:$Y$1792,   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,desc5,
         descB, desc4,
         mats, matg3,
         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(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))

 

[mporter785]

Thank you so much! Can I ask for one more line in the main LET function to exclude any description containing "rebuild" or "repair"?

I have tried *(INDEX(tblprod,,7)=IF(OR("rebuild","repair"),0,"")*

and other variations of

desc4,{"REBUILD","REPAIR"}
descC, desc4,
((MMULT(--(ISNUMBER(SEARCH(descC,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(descC),1,1,0))>0)*-1)*

 

[KRice]

I haven't tested, but this should work:

=LET(desc1,{"MD20 air"},   desc2,{"E/P","ENDPLATE","END PLATE"},   desc3,{"MD20"},   desc4,{"ROTOR"},   desc5,{"*"},  descexclude,{"repair","rebuild"},
         matg1,{"SS","304","316"},   matg2,{"GI","CS"},   matg3,{"*"},
         tblprod,sab_prod!$A$2:$R$1792,
         wdot,sab_prod!$Y$2:$Y$1792,   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,desc5,
         descB, desc4,
         descC, descexclude,
         mats, matg3,
         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) ),
         IFERROR(SUM(INDEX(res,,6)),0))

This creates a new variable called 'descexclude' where you can compile any terms that should be excluded (i.e., if these test strings exist in the Description field, ignore that row). Then this 'descexclude' array is assigned to the 'descC' variable name (you could avoid this step and simply hardwire 'descexclude' into the MMULT function I'm about to describe, but this approach keeps everything consistent with regard to how you build a list of search terms and then pass them to another variable whose name is found in the general main formula). Then in the main FILTER function, recall that we are filtering 'tblprod' based on what is returned as TRUE after all of the filtering criteria are applied. So in the other MMULT formulas, if any of the search terms are found, the sum-by-row operation described by the MMULT formula will return a number greater than 0 indicating the number of search terms found in each cell as one moves down the Description column. For that reason, when we want to require at least one of the search terms to be present as a condition to consider the row, we establish the condition with the basic expression MMULT>0. And if we want to exclude some terms, we require that MMULT=0. Then if an undesirable term is found, MMULT will return a value >0, and since that does not satisfy the MMULT=0 criteria, this part of the formula will evaluate to FALSE and cause the row to be ignored. Let me know if this works.