Trouble Finding Correct Formula to Sort Data

Hello All!

Most usually I can look back through previous posts to find my answers here, but this one I just cannot get on my own no matter what function I use (Countif, Countifs, Sumproduct). I will explain it as best as I can and I will attach example spreadsheets to show what I am doing with data. Thanks in advance for any help. Here goes:

1) I have raw equipment data in SAP that I export to an excel file on my computer (example of output is the file "Raw_Export")

2) I open this file and do the following:
a) Format the entire sheet as general format so that I can add formulas to the sheet prior to extracting data if not it will not recognize my formulas.
b) Format the reference date column as date type because general format changes it to an integer
c) I insert a row in front of the equipment column and enter the formula =IF(E2<>"",LEFT(E2,9),"NONE") and copy this down the entire inserted column so that I have no blank rows in this column.
d) I then insert a row in front of the System Status column and enter the formula =LEFT(L2,4) and copy this down the entire inserted column to pull out the last condition of the equipment for sorting.
All of this work can be seen in the file called "Adjusted_Export".

Here is what I need to do:
In the workbook "Chart" I need to count the rows based on the criteria of 1)Equipment number (column D "Adjusted_Export"), 2)System Condition (column G in "Adjusted_Export"), and 3) 4 letter code in column K of "Adjusted_Export".

For example if you open the "Chart" file and use cell B3 as my first example I would need to count the number of rows in "Adjusted_Export" where the equipment number in Column D is between SH100-000 and SH240-999, the system condition is = 5. Cell C3 would be the exact same as B3 plus the added condition of column K is TECO. B4 would be the same as B3 only System condition is <>5. C4 would be the same as B4 plus the added condition of TECO.

I am sorry for the length of this post, just trying to give as much detail as possible. As I said I have tried everything that I can think of and nothing has worked. The best I ever get out of anything is a #VALUE return. I have had success with sumif when pulling values from other sheets exported in this same way before, but no dice here. Also anything is fair game at this point, so if formatting "Raw_Data" file a different way works better that is fine. I only wanted to show how I have worked the export previously. Again thanks in advance for any help. If I have not been clear enough with what I need just let me know and I will try to explain in more detail.

Raw_Export.xlsx
Adjusted_Export.xlsx
Chart.xlsx

Craig,

Thank you for your assistance. However, when I type your code into the "Adjusted_Export" column M I get the #VALUE! Yet again. I see what you are trying to do with the mid statement to extract the machine number but my sheet does not seem to like that. Also I have different modules that will break out in mid machine number. For example one of my cells might start with SH420-075 and got through SH450-320. So I have to use the entire machine number. I can disregard the first two alpha characters though so it would be 2 three digit numbers seperated by the hyphen.

I have the feeling that I am missing something with my book that keeps causing me to get the #VALUE! but I cannot find what it is.

Thanks for the quick reply!

Craig,

That does work!! I am not sure why I am having to change the format to text though. I export data out of SAP for use in other sheets that I have created when using the SUMIF and they work fine with the general format. I guess I should have thought to change the formatting myself. Sometimes I guess I have trouble seeingn the forest for the trees. Thanks again for your help!! Always helps to have someone else looking over your shoulder offering up ideas!

SL