Counting data from an external workbook

[niklas24]

I have a column of data that I am trying to count based on specific criteria. I have attached an image of my data. I cannot use COUNTIFS because I need this to compute in a closed workbook. I must use SUMPRODUCT

Note - the values in row C4:F4 of the first attached image are what the results from the formula should yield. There is no formula applied to these cells.

The data in column A is actually in an external workbook 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*.

I have tried using the following generic formula:

in cells C2/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 cell D2/F2

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

The formula works for D2:F2 just fine, but not in C2. I keep getting a value of zero and I have no idea why. I have tried manually retying the data in column A into a brand new sheet and assigning the cell category as both general and text but I still have the same problem. The second and third images show the formulas and values in cells C2 and E2. Does anyone have any idea why this is occurring?

[p45cal]

Please supply files which you've shown pictures of; it's hard work trying to recreate your scenario. If there's sensitive stuff in the files, remove all unnecessary sheets/code, but leave in your attempted formulae.

[Roger Govier]

Hi

I think you need to also coerce your Lefts from Text values to Numbers and, your second part I think the test should be = and not <>

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