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 previous post was referring to the implementation on your CM worksheet. Looking further, on the FM worksheet, the LET formula also has some issues:

1. Again, change these definitions to cover the full data table and note that the rightmost column needed in tblprod is column R (not W):

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

   I mention the column R/W issue because later in the formula, you'll see this array: {1,0,1,0,1,1,1,0,0,1,0,0,0,0,0,0,1,0}. The 1's/0's array describes which columns to return (1) and which to ignore (0), so there needs to be a one-to-one correspondence between these array elements and columns A:R. If you were to change the full tblprod definition to

   tblprod, sab_prod!$A$2:$W$1789

   ...then the 1's/0's array would become {1,0,1,0,1,1,1,0,0,1,0,0,0,0,0,0,1,0,0,0,0,0,0} to account for ignoring the additional columns.
2. It looks as if you are referencing a different set of cells for the Date Range, but in the formula those dates have not been "fixed" so as the formula is copied down the column, the date range "walks". Change the date criteria inside the LET function to (date>=$L$3) * (date<=$L$4)
3. I noticed the internal definition of "mats" appears as "material" (where you assign any one of several arrays containing material types to the variable mats), but the subsequent usage of that array still refers to "mats". I'd recommend changing the first definition back to "mats" rather than material, otherwise an error will be produced because there would be no mats array.

Speaking of errors, if nothing is found, the innermost FILTER function will produce an error (an empty results array error). One way to induce this error is to tighten up the Date Range window until nothing satisfies the date range...and an error is produced. How do you want that situation handled? The final SUM function in the LET formula could be wrapped with an IFERROR function to return a message or a 0 in that event. If you want to go this route, then it might make sense to revise the formula to eliminate the outermost FILTER and the associated 1's/0's array (that was used primarily as a way to aggregate some meaningful columns when I originally constructed the formula, but it's not essential). Instead the formula could retain all of the columns internally and then the final error-trapped SUM would refer only to the Qty column, which would be the 6th column in this larger array. So the formula would look like this and return either the sum of quantities meeting the criteria or a 0 if none are found:

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

 

[mporter785]

Thank you so much! The updated formula worked. How could I also alter the formula for the "number without dotline" column so that no dotlines get counted and the date is based only on the ship actual column? Also if you see later down the sheets when I am counting endplates, in the description column I am looking for keywords "E/P,ENDPLATE" AND the model like "MD20". Is there any way to make this work in your formula?

Is there a way I can buy you a drink...or a few?

 

[KRice]

That's good to hear. About your other questions...for the exercise above, the maxitm helper column on the sab_prod worksheet was used to identify all relevant rows that might be considered. "Relevant" meaning that for any given job number, we could examine all of the "item" numbers associated with that job number, and where those job numbers shared the same integer value, we kept only the largest one (with the greatest decimal component) and the others were considered to be not relevant. For example, job number 1100472831 has items 30 and 30.1, so we flagged 30.1 with a 1 (possibly considered) and 30 with a 0 (ignore). This means that we would have flagged some "dot" line as not relevant for the situation where there are multiple decimal items with the same integer (e.g. for 30, 30.1, 30.2, and 30.3, we would flag 30.3 with a 1 (relevant) and 30, 30.1, 30.2 with a 0 (ignore))...so in some cases the dots are ignored. Now to flip this condition to consider "without dotlines", how would these "somewhere-in-between" dot items be handled?...I'm talking about the 30.1 and 30.2 is this hypothetical example. Presumably 30 is categorized as "without dotline" relevant and 30.3 (in this example) is firmly a dotline-relevant candidate (therefore to be ignored for the "without dotline" exercise). But what is the rule for 30.1 and 30.2 here?

Which version of the formula above were you planning to use?...the nested double FILTER or the single FILTER with an error trap in my most recent post?

 

[KRice]

About your other questions...yes, just like we have a criteria for checking whether any one of several materials are present in the 13th column of tblprod (sab_prod!$A$2:$R$1789), we can use that same construction to replace this part of the formula...(ISNUMBER(SEARCH(desc,INDEX(tblprod,,7))))...which works only for a single item defined by "desc". If we make that switch, add some new description search term array placeholders (I've called them desc1 and desc2, similar to what was done with the material search term arrays matg1 and matg2), and then make an assignment of one of those arrays to the "desc" variable, we will have addressed that part of your question. The newly added "desc2" has the three search terms you mention, so it should find any rows where those strings occur in the 7th column of tblprod.

Regarding the date question, you could take a similar approach to make it easy to switch between whichever date criteria should be applied. Below, I've simply added another date definition called "dateact" which references the 17th column of tblprod. I've renamed the original "date" definition to "datedep" (i.e., the date depends on whether the item number is an integer...but you can call it whatever you want). Then a new entry in the LET formula is used to assign one of the date definitions to the "date" variable name, just like the approach taken to assign search terms to "desc" and "mats", we can switch between date formulas using the same method.

You also mentioned a "without dotline" calculation. I'm assuming that a "without dotline" row would be described by this expression when TRUE:

(SIGN(MOD(INDEX(tblprod,,3),1)=0))

In plain language, this says that a "without dotline" row occurs when the item number found in the 3rd column of tblprod is an integer (no decimal component). So in the questions posed in my previous post, if you clarify that only 30 should be considered and 30.1, 30.2, and 30.3 should be ignored, then this part of the formula will do. Let me know about this.

After looking at this last criteria, it occurred to me that the earlier formula doesn't need to say (maxitm=1)...it can simply say (maxitm) because "maxitm" is already a range of values consisting only of 1's and 0's, so it can be used directly without having to perform the =1 logical check. It might be beneficial to build flexibility into the main formula for switching between these two criteria. The former "maxitm" is now renamed "wdot" (with dot, which uses the rule described in the helper column formula on the sab_prod worksheet), and a new "wodot" (without dot) variable name is assigned to the formula described above. Then a generic "itemtype" variable is used in the main formula and it is assigned whichever variation you choose.

I've structured the formula to make it a little easier to see the different parts...there are two lines where common search terms are described in array form and given names. The next three lines assign names and definitions for the main data table, the dot/no-dot formulas or helper cells, and the date formulas. The next four lines assign these named definitions to common names used in the main formula (so editing the main formula should not be necessary). Then the main formula (called "res"...for results) is shown, and finally the instruction for what to do with the results is given. In this case, we sum the 6th column of the results. For a case where you want: 1) to search for any of these strings...{"E/P","ENDPLATE","MD20"}...in the Description column (so "desc2" is assigned to "desc"); and 2) to search for any of these material type strings...{"SS","304","316"}...so "matg1" is assigned to "mats"; and 3) to consider only the actual date rather than the dependent date used before...so "dateact" is assigned to "date"); and 4) to consider "without dot" items (so "wodot" is assigned to "itemtype")...the formula would look like:

=LET(desc1,{"MD20 air"},   desc2,{"E/P","ENDPLATE","MD20"},
         matg1,{"SS","304","316"},   matg2,{"GI","CS"},
         tblprod,sab_prod!$A$2:$R$1789,
         wdot,sab_prod!$Y$2:$Y$1789,   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)),
         desc, desc2,
         mats, matg1,
         date, dateact,
         itemtype, wodot,
         res,FILTER( tblprod,
                    (MMULT(--(ISNUMBER(SEARCH(desc,INDEX(tblprod,,7)))),SEQUENCE(COUNTA(desc),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))

 

[KRice]

I'm curious about how these formulas are to be used. Is there something on each row that tells you what to search for? I ask because it is odd that a formula would be copied down a column in a table to calculate a number pertaining to that row, yet the formula does not explicitly reference anything on that row. Do you have certain clues on each row that trigger a set of search terms? Are those search terms listed anywhere so that the formula could automatically perform a lookup to identify the search terms?

 

[mporter785]

Thank you. sorry for not logging in for a while I have been sick with COVID. The information to differentiate each entry or the descriptor I am searching for comes from column A 'description' in each of the sheets (MD20 Housings, MD20 GI Housings India, etc.) I don't mind manually changing the description for each entry.

You have the "without dot line" filtering assumption correct. Except, and I may have been wrong before, .2's and .3's should be counted in the maxitm column in the sab_prod query. Those are just orders that were split into batches so 20.1, 20.2, and 20.3 should be counted for example.

Also when looking for endplates, I need it to look for either "E/P" or "ENDPLATE" AND the description must contain "MD20" for example.

 

[KRice]

That’s okay… I hope you are feeling better. We may have a disconnect on a few points:
1. For the “dotline”, I thought you wanted only the largest decimal for any lines having a common job number and the same integer components for their item numbers…so for 20, 20.1, 20.2, and 20.3 with the same job number, we’d count only the 20.3 line. Are you saying this is not correct?…and we really want to count those item numbers that have a literal dot (i.e., they explicitly show a decimal component), so 20.1, 20.2, and 20.3 should be used?

2. Then for the “without dot” we are to avoid any item numbers that do not contain an explicit dot? So in this example, 20 is counted and the other three decimal-containing lines are ignored?

3. And then let’s clarify the search terms. Right now we search columns 7 (description) and 13 (materials) for different terms. As the formula is written, search terms shown in an array (e.g., matg1,{"SS","304","316"}) are treated with an OR condition, meaning the presence of any of those terms is sufficient to satisfy this part of the row-matching criteria. Are you saying in your latest post that you'd like to search the Description column using an AND/OR condition? For example, only consider rows where, within a single cell under the Description column, one finds both ("MD20") AND either ("E/P" OR "Endplate")? I'm somewhat confused about the column designations/numbering. You mention searching column A "description", but I've been using column 7 (Description). But I'd really like some clarity about the AND/OR question. If that is what you want, it's doable, and probably most easily done by adding one more condition to the formula.

 

[mporter785]

1. yes, that is not correct. 20.1, 20.2, and 20.3 should be used for 'wdot'. I am sorry about that.

2. Correct. Count only item numbers that do not contain decimals.

3. I am referring to the output sheets like 'LM' and 'CM' for column A description. That's how I know what product category and model number I am searching for in the sab_prod query. The or condition works for the first 38 rows of housings. But for end plates, there are several different ways a MD20 endplate could be entered in the sab_prod query could be "E/P MD20" or "endplate md20" or "md20 endplate" etc. so it must search for all the different ways a person would type that in.

Additionally, we sell endplates separately but also two endplates come with every one airlock. so for example, in [LM] for G40 would be = 2*G3 + the equation looking for the endplates specifically. Unless there is a more elegant way to code this in a function.

 

[KRice]

Thanks for clarifying those questions. Regarding questions 1 and 2, with the news that any item number having a dot should be used for wdot, we no longer need a helper column to sort out which of the dotted items number to consider...because we don't care which one is the maximum--we take all of them and subject them to other filtering criteria. So the "maxitm" formula and helper column can be eliminated. Instead we replace the "wdot" definition with a formula that looks very much like the "wodot" formula, except "wdot" checks whether there is a non-zero decimal component and "wodot" checks whether the decimal part is 0. Both of those functions work inside the main LET function.

Since you need the flexibility to look for terms using an AND/OR construction, I've added an extra condition to the FILTER function assigned to the results array called "res". You'll recognize the part of the formula that is now repeated and slightly modified...

(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)*

Each of these MMULT expressions establishes an OR condition, so for the 1st one, it says to search the 7th column of "tblprod" for any of the terms found in the variable called "descA", and if any are found, consider that row, which results in an array of TRUE/FALSE values representing the assessment of each row in the 7th column of tblprod. Similarly, the 2nd one does the same for any terms found in the "descB" array. Then by multiplying both of these arrays together, we create the AND condition. So the plain language interpretation of this is that we're looking for any terms (this OR that OR something else) in "descA" AND also any terms in "descB". Both "descA" and "descB" can consist of however many terms you'd like, from one up to any reasonable number. If you want to turn off one of these because you do not need the AND condition, then one of the MMULT expressions needs to evaluate to 1 so that that part of the filter is essentially deactivated. The easiest way to do that while preserving a consistent methodology for the query is to create a variable (I've called "desc5") that passes along a wildcard to the SEARCH function. Then SEARCH should find something in every row and all rows are considered by this part of the filtering criteria.

Below is what the revised formula looks like. You can build a bank of common combinations and variations of search terms. Where the OR condition is to be applied, all of those search terms need to be in the same array. In the example below, I've deactivated the "descA" filter by invoking the wildcard ("desc5" has been assigned to "descA"), and "descB" is looking for "ROTOR". I'm also requiring the material types to be anything (I've deactivated it with a wildcard "matg3"). You may need to change some references to adapt it to your sheet. Let me know if you have any questions/comments and whether it works as desired.

=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$1789,
         wdot,SIGN(MOD(INDEX(tblprod,,3),1)<>0),   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. It seems like 'wdot' formula is counting only items with dotlines. I should have clarified more in the last post. It still needs to follow the logic where it sums the qty of both dotlines and non-dotlines where dotlines takes precedence to be summed when job numbers are the same. the Maxitm column seemed to sort this logic correctly. Ship-act dates for dotlines and job-pln date for non-dotlines. But in the event an order is split into batches, i.e. item number is 20.1, 20.2, 20.3 for the same job number, all three of these qty's should be summed. Thanks.