Counting data from an external workbook

[niklas24]

I have been trying to count a column of data based on specific criteria. Here is an image of my data:



The data in column A is actually in an external workbook (for simplicity I am showing it all in one image here) and I am using the SUMPRODUCT function to try to count how many times any time 4* occurs in cell C2, however I need to keep any values that begin with 4B from being counted as part of 4* and count 4B* seperately in D2. Same thing with 00 (that's zero not O). Anything with 00B I do not want to count as part of 00*. It must be able to work with a closed workbook, hence SUMPRODUCT as opposed to COUNTIFS. Note - the values in row C4:F4 in the above image are what the results from the formula should yield. There is no formula applied to these cells.

Here are the formulas I am using:

in C2 & E2 (changing LEN to E1 when formula is applied to E2)

=SUMPRODUCT(--(LEFT([workbook.xlsx]Sheet!$A1:INDEX([workbook.xlsx]Sheet!$A:$A, MATCH("zzz", [workbook.xlsx]Sheet!$A:$A)), LEN(C1))=C1),
            --(LEFT([workbook.xlsx]Sheet!$A1:INDEX([workbook.xlsx]Sheet!$A:$A, MATCH("zzz", [workbook.xlsx]Sheet!$A:$A)), LEN(D1))<>D1))

in D2 & F2 (changing LEN to F1 when formula is applied to F2)

=SUMPRODUCT(--(LEFT([workbook.xlsx]Sheet!$A1:INDEX([workbook.xlsx]Sheet!$A:$A, MATCH("zzz", [workbook.xlsx]Sheet!$A:$A)), LEN(D1))=D1))​

The formulas work as intended for columns D, E and F (yielding 6, 7 6 respectively) but it produces 0 in column C. Obviously there is something wrong with the data in column A since it's not being calculated correctly, but I can't figure out where the problem is. I've tried entering the data again in a brand new workbook and even applied general, text and custom format to the cells (in both the source and results workbooks). I've also tried to use the evaluate formula tool, but I still don't see the issue. Does anyone have an idea of what could be going on here?

 

[Fluff]

Cross posted https://stackoverflow.com/questions/48937790/counting-data-from-an-external-workbook

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.