Counting data from an external workbook


New Member
Nov 9, 2015

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?


Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.


MrExcel MVP, Moderator
Jun 12, 2014
Office Version
Cross posted

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.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...