CountIfs with Left/Len error

[bolomrk1]

Hope everyone is doing well.

I have an issue with COUNTIFS() that is not making any since.

=COUNTIFS(DATA!$D:$D,"="&RPT!$A2, DATA!$D:$D,"="&LEFT(RPT!$A2,LEN(RPT!$A2)))
NOTE: THE “$A2” will change according to which row (2) the formula is in. If it is in row(3) the formula will contain “$A3”(exclulding the quotes. Header row is (1)).
The formulas will located in "C" column of the RPT table.
I use the CountIFs() due to several other criteria that helps filter the data. I have such function in another spreadsheet now but does not attempt to do what I am trying to do here. If you would like to see one of these formulas just so that you understand I am not an expert with EXCEL but I am not an ignorant of it either, it is listed at the bottom of this message.

There are two worksheets contained in the same workbook.
DATA has the raw data being counted.

RPT has the specifications to use for the counts.

DATA file format

WH	Completed	Item	ShortID	Serial Number
XX	02/17/2020​	ADD1632csskv	ADD1632cssk	2608575​
AA	03/04/2020​	ADD1832csak.	ADD1832csak	3046409​
AA	03/04/2020​	ADD1832csak.	ADD1832csak	3046408​
XX	02/14/2020​	ADD1832csskv	ADD1832cssk	2608574​
AA	01/30/2020​	ADD1832esnk.	ADD1832esnk	3045854​
AA	01/31/2020​	ADD1832esnk.	ADD1832esnk	3045889​
XX	02/24/2020​	ADD1832esskv-dr02	ADD1832essk	2608887​

NOTE: I am only showing a few records here. For real that can be as many as 50K-60K records. Not all records start with “ADD”.



RPT file format

Mdl​	Date​	QTY​
ADD1632cssk	1/19/2020​	​
ADD18	3/4/2020​	​
ADD1832cssk	2/14/2020​	​
ADD1832esnk	1/30/2020​	​
ADD2052esnk	2/4/2020​	​
ADD2052esrk	2/4/2020​	​

All data will be parsed by the Name & Length of the model in the RPT table.
I need to be able to match the name in the DATA table “Short Id” column to the RPT Mdl Id and get a count of these models.

As shown in the DATA table there is only one record for “ADD1632cssk” (row 2 of RPT table) so the Qty count should be one.
This works but I do not believe correctly.

In the case of ADD18 (row 3) there is a total count of 7 (in this configuration all records happen to start with “ADD1”).
I get a count of “0” instead of 7.


If someone could give me a ideal as to what I am overlooking or not understanding about the CountIFs() function when used with Left()/Len() functions it would be greatly appreciated.

Thanks in advance for any help you may be able to provide.

PS: CountIFs example

=COUNTIFS(DATA!$D:$D,"="&RPT!$B4,DATA!$B:$B,">="&DATE(YEAR(RPT!$C4),MONTH(RPT!$C4),DAY(RPT!$C4)),DATA!$B:$B,">=03/01/"&YEAR(TODAY()),DATA!$B:$B,"<=03/31/"&YEAR(TODAY()),DATA!$B:$B,"<"&DATE(YEAR(RPT!$C4)+1,MONTH(RPT!$C4),DAY(RPT!$C4)-1))

 

[KRice]

It's not clear what you are trying to accomplish with this formula: =COUNTIFS(DATA!$D:$D,"="&RPT!$A2, DATA!$D:$D,"="&LEFT(RPT!$A2,LEN(RPT!$A2)))
The first condition ["="&RPT!$A2] and the second condition ["="&LEFT(RPT!$A2,LEN(RPT!$A2))] are the same thing. The first looks at the contents of A2 in sheet RPT. The second takes the length of the string in A2 in sheet RPT and uses it to return the contents of A2 in RPT beginning at the left and taking all characters.
Instead, do you want to find the length of the shortened model name in RPT and use that length, L, to take the first L characters of the model name shown in the DATA sheet?

 

[KRice]

Something like this will take the shortened model string in RPT and check to see if any longer strings in column D of DATA contain that same short string. If so, the starting position of the string is returned (I'm assuming this will always be a 1 if the string is found, but you'll need to confirm that). If the string is not found, an error is generated, which will return a 0 due to the IFERROR function wrapping the SEARCH function. Then SUM will add the number of matches.

Book1
	A	B	C
1	Mdl	Date	QTY
2	ADD1632cssk	1/19/2020	1
3	ADD18	3/4/2020	6
4	ADD1832cssk	2/14/2020	1
5	ADD1832esnk	1/30/2020	2
6	ADD2052esnk	2/4/2020	0
7	ADD2052esrk	2/4/2020	0
RPT

Cell Formulas
Range	Formula
C2:C7	C2	=SUM(--(IFERROR(SEARCH(A2,DATA!$D$2:$D$10),0)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[bolomrk1]

Something like this will take the shortened model string in RPT and check to see if any longer strings in column D of DATA contain that same short string. If so, the starting position of the string is returned (I'm assuming this will always be a 1 if the string is found, but you'll need to confirm that). If the string is not found, an error is generated, which will return a 0 due to the IFERROR function wrapping the SEARCH function. Then SUM will add the number of matches.

Book1
	A	B	C
1	Mdl	Date	QTY
2	ADD1632cssk	1/19/2020	1
3	ADD18	3/4/2020	6
4	ADD1832cssk	2/14/2020	1
5	ADD1832esnk	1/30/2020	2
6	ADD2052esnk	2/4/2020	0
7	ADD2052esrk	2/4/2020	0
RPT

Cell Formulas
Range	Formula
C2:C7	C2	=SUM(--(IFERROR(SEARCH(A2,DATA!$D$2:$D$10),0)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

KRice,
Thanks the Sum() function works as intended per your script but in my haste I did not explain the complete process, So here it is:
The data will be calculated 1st of each month for the productivity completed in the prior month on a rolling 12 month basis. The RPT Date is used as the control date for the rolling 12 month calculations.

EX: Record #1 date in the RPT table is "01/19/2020". The calculations for this record for the month of Jan will start on 01/19/2020 and be calculated by month until 01/18/2021. Each month has it's on column in the RPT spreadsheet and the formula is modified to take into account the month being calculated.

The attached formula at the bottom of the original message displays this without taking into the possibility of a partial match (uses exact match only) on the product. Now it has been decided that partial matches may be needed. This will be determined by the record in the RPT file and extracted from the DATA file,
hence my problem with the CountIFs() function when combining the LEFT() & LEN() functions.

The logic should be something like this:
EX. #1 Exact match to RPT Record #1 All records based off LEFT(XX) Chars For Month of JAN 2020
COUNTIFS(LEFT(DATA ShortID),LEN(RPT Mdl Record) = RPT Mdl Record _______ ADD1632cssk = ADD1632cssk True
AND DATA Record Completion Date (cDate)>= RPT Record Date (rDate) _______ AND 02/17/2020 >= 01/19/2020 True
AND DATA Record cDate >= 1st Day of MM/MM/YYYY _______ AND 02/17/2020 >=01/01/2020 True
AND DATA Record cDate <= Last Day of DD/MM/YYYY _______ AND 02/17/2020 <=01/31/2020 False
AND DATA Record cDate < DD -1/MM/YYYY -1 _______ AND 02/17/2020 <= 02/16/2021 True
THEN COUNT _______ THEN COUNT for month total Not included in count
Else ignore.
In this example DATA record 1 would fail for JAN but Count in FEB. . All other records fail due to no Match.



EX. #2 Partial Match to RPT Record #2 All records base off LEFT (XX) Chars Only DATA Records #5 & #6 match for JAN.
COUNTIFS( LEFT(DATA ShortID),LEN(RPT Mdl Record) = RPT Mdl Record _______ ADD16 = ADD18 False
AND DATA Record Completion Date (cDate)>= RPT Record Date (rDate) _______ AND 02/17/2020 >= 01/19/2020 True
AND DATA Record cDate >= 1st Day of MM/MM/YYYY _______ AND 02/17/2020 >=01/01/2020 True
AND DATA Record cDate <= Last Day of DD/MM/YYYY _______ AND 02/17/2020 <=01/31/2020 True
AND DATA Record cDate < DD -1/MM/YYYY -1 _______ AND 02/17/2020 <= 02/16/2021 True
THEN COUNT _______ THEN COUNT for month total included in count
Else ignore.

In this example Data Record #1 Fails due to no match. Data Records #5 & #6 are the only records that match all criteria for JAN.
ONLY DATA records #4 & #6 would match for the month of FEB. and so on.

The problem I have is either my count is wrong, or if I rearrange the CountIFs() formula I get the error message attached to my original message.
This is where I have the problem with the CountIfs() function giving me the error attached to the original message.
=COUNTIFS(LEFT(DATA!$D:$D,LEN(RPT!$A2)),"="&RPT!$A2)

I understand that this is a long message (hope I didn't make any mistakes) and that yourself or anyone else may not have the time to assist.

Again I thank you and anyone else in advance for any help they may be able to provide.

Thanks.

 

[KRice]

Have a look at the formula in cell E3 and let me know if this does what you want. I've attempted to convert the COUNTIFS to a SUMPRODUCT, which has some additional flexibility in handling the LEFT(LEN construction....and the column D formula isn't used at all.

Book3
	A	B	C	D	E
1		Mdl	Date	QTY
2		ADD1632cssk	1/19/2020	1
3		ADD18	3/4/2020	6	2
4		ADD1832cssk	2/14/2020	1
5		ADD1832esnk	1/30/2020	2
6		ADD2052esnk	2/4/2020	0
7		ADD2052esrk	2/4/2020	0
RPT

Cell Formulas
Range	Formula
E3	E3	=SUMPRODUCT(--(RPT!B3=LEFT(DATA!D:D,LEN(B3))), (DATA!$B:$B>=DATE(YEAR(RPT!$C3),MONTH(RPT!$C3),DAY(RPT!$C3)))*(DATA!$B:$B>=DATE(YEAR(TODAY()),3,1))*(DATA!$B:$B<=DATE(YEAR(TODAY()),3,31))*(DATA!$B:$B<DATE(YEAR(RPT!$C3)+1,MONTH(RPT!$C3),DAY(RPT!$C3)-1)))
D2:D7	D2	=SUM(--(IFERROR(SEARCH(B2,DATA!$D$2:$D$10),0)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Book3
	A	B	C	D	E
1	WH	Completed	Item	ShortID	Serial Number
2	XX	2/17/2020	ADD1632csskv	ADD1632cssk	2608575
3	AA	3/4/2020	ADD1832csak.	ADD1832csak	3046409
4	AA	3/4/2020	ADD1832csak.	ADD1832csak	3046408
5	XX	2/14/2020	ADD1832csskv	ADD1832cssk	2608574
6	AA	1/30/2020	ADD1832esnk.	ADD1832esnk	3045854
7	AA	1/31/2020	ADD1832esnk.	ADD1832esnk	3045889
8	XX	2/24/2020	ADD1832esskv-dr02	ADD1832essk	2608887
DATA

 

[KRice]

It looks like the example formula at the end of post #1 has hard-wired dates for beginning and ending of the month, but you've mentioned that each month is considered separately in different columns. This version uses a similar approach as my last post, but relies on the month heading to establish the beginning and ending dates relevant to that month. For performance reasons, you may want to consider using more realistic ranges for B:B and D:D.

Book3
	A	B	C	D	E	F	G	H
1		Mdl	Date	QTY	Jan	Feb	Mar	Apr
2		ADD1632cssk	1/19/2020		0	1	0	0
3		ADD18	3/4/2020		0	0	2	0
4		ADD1832cssk	2/14/2020		0	1	0	0
5		ADD1832esnk	1/30/2020		2	0	0	0
6		ADD2052esnk	2/4/2020		0	0	0	0
7		ADD2052esrk	2/4/2020		0	0	0	0
RPT

Cell Formulas
Range	Formula
E2:H7	E2	=SUMPRODUCT(--($B2=LEFT(DATA!$D:$D,LEN($B2))), (DATA!$B:$B>=DATE(YEAR($C2),MONTH($C2),DAY($C2)))*(DATA!$B:$B>=DATE(YEAR(TODAY()),MONTH(DATEVALUE(E$1&"1")),1))*(DATA!$B:$B<=EOMONTH(DATE(YEAR(TODAY()),MONTH(DATEVALUE(E$1&"1")),1),0))*(DATA!$B:$B<=DATE(YEAR($C2)+1,MONTH($C2),DAY($C2)-1)))

 

[bolomrk1]

Mr. Rice,

Thank you very much. This is doing exactly what I want it to do.

I would have taken for every to come across this function and use the way you did.
Now I have to learn more about the SUMPRODUCT() function as it seems to do what the COUNTIFS() does with a little less formula.

Again, Thank you!

PS Now I have to check out you second form of the formula.

 

[KRice]

Thanks for the update. In this case, SUMPRODUCT is being used to make several sets of logical comparisons, where dates in DATA!$B:$B are compared to some date. The result of each set of comparisons is an array consisting of TRUE's and FALSE's. In the formula shown in post #6, the explicit multiplication operations (*) coerce the TRUE's to 1's and the FALSE's to 0's, producing a final array consisting only of 0's and 1's....a 0 when any condition is not met and a 1 when all of the conditions are met. The double unary operator (--) does a similar coercion on the first set of logical comparisons to be made where the short model name is compared to DATA!$D:$D, and the resulting array of 0's and 1's is then multiplied by the product of the date arrays. An equivalent formulation that doesn't rely on the double unary would look like this:
=SUMPRODUCT(($B2=LEFT(DATA!$D:$D,LEN($B2)))*(DATA!$B:$B>=DATE(YEAR($C2),MONTH($C2),DAY($C2)))*(DATA!$B:$B>=DATE(YEAR(TODAY()),MONTH(DATEVALUE(E$1&"1")),1))*(DATA!$B:$B<=EOMONTH(DATE(YEAR(TODAY()),MONTH(DATEVALUE(E$1&"1")),1),0))*(DATA!$B:$B<=DATE(YEAR($C2)+1,MONTH($C2),DAY($C2)-1)))

In the end, SUMPRODUCT simply sums the values in the final array, and since that array consists only of 1's and 0's, it emulates a counting function.