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