CountIfs with Left/Len error

bolomrk1

New Member
Joined
Dec 21, 2011
Messages
14
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

WHCompletedItemShortIDSerial Number
XX
02/17/2020​
ADD1632csskvADD1632cssk
2608575​
AA
03/04/2020​
ADD1832csak.ADD1832csak
3046409​
AA
03/04/2020​
ADD1832csak.ADD1832csak
3046408​
XX
02/14/2020​
ADD1832csskvADD1832cssk
2608574​
AA
01/30/2020​
ADD1832esnk.ADD1832esnk
3045854​
AA
01/31/2020​
ADD1832esnk.ADD1832esnk
3045889​
XX
02/24/2020​
ADD1832esskv-dr02ADD1832essk
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))
 

Attachments

  • 1594146824763.png
    1594146824763.png
    46.9 KB · Views: 10

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,061
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 9, 2003
Messages
1,061
Office Version
  1. 2019
Platform
  1. Windows
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
ABC
1MdlDateQTY
2ADD1632cssk1/19/20201
3ADD183/4/20206
4ADD1832cssk2/14/20201
5ADD1832esnk1/30/20202
6ADD2052esnk2/4/20200
7ADD2052esrk2/4/20200
RPT
Cell Formulas
RangeFormula
C2:C7C2=SUM(--(IFERROR(SEARCH(A2,DATA!$D$2:$D$10),0)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

bolomrk1

New Member
Joined
Dec 21, 2011
Messages
14
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
ABC
1MdlDateQTY
2ADD1632cssk1/19/20201
3ADD183/4/20206
4ADD1832cssk2/14/20201
5ADD1832esnk1/30/20202
6ADD2052esnk2/4/20200
7ADD2052esrk2/4/20200
RPT
Cell Formulas
RangeFormula
C2:C7C2=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

Well-known Member
Joined
Dec 9, 2003
Messages
1,061
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
ABCDE
1MdlDateQTY
2ADD1632cssk1/19/20201
3ADD183/4/202062
4ADD1832cssk2/14/20201
5ADD1832esnk1/30/20202
6ADD2052esnk2/4/20200
7ADD2052esrk2/4/20200
RPT
Cell Formulas
RangeFormula
E3E3=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:D7D2=SUM(--(IFERROR(SEARCH(B2,DATA!$D$2:$D$10),0)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.


Book3
ABCDE
1WHCompletedItemShortIDSerial Number
2XX2/17/2020ADD1632csskvADD1632cssk2608575
3AA3/4/2020ADD1832csak.ADD1832csak3046409
4AA3/4/2020ADD1832csak.ADD1832csak3046408
5XX2/14/2020ADD1832csskvADD1832cssk2608574
6AA1/30/2020ADD1832esnk.ADD1832esnk3045854
7AA1/31/2020ADD1832esnk.ADD1832esnk3045889
8XX2/24/2020ADD1832esskv-dr02ADD1832essk2608887
DATA
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,061
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGH
1MdlDateQTYJanFebMarApr
2ADD1632cssk1/19/20200100
3ADD183/4/20200020
4ADD1832cssk2/14/20200100
5ADD1832esnk1/30/20202000
6ADD2052esnk2/4/20200000
7ADD2052esrk2/4/20200000
RPT
Cell Formulas
RangeFormula
E2:H7E2=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

New Member
Joined
Dec 21, 2011
Messages
14
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.:biggrin:
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,061
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,000
Messages
5,622,120
Members
415,878
Latest member
jjj12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top