I have a long list of data and I'm attempting count the number of occurences for different conditions using an array formula. I've got so far but now I'm stuck. Any help would be appreciated :)

So far I have the following in D7.
{=SUM(IF(LEFT(January!$K$2:$K$9945,2)="US",IF(Janu ary!$M$2:$M$9945=B7,1,0),0))}

Sheet January contains all of my data so I want to count everything that begins with "US" in column K that also has the name I've entered into B7, in column M. This works fine to a point.

The problem is that the names in column M in January do not always appear exactly the same. For example, ABER AM includes all the following:
ABER AM AB145
ABER AM AB145
ABER AM AB250
ABER AM AB250
ABER AM AB35
ABER AM AB938
I could include all the variations in column B, making it much longer but this would mean having lots of variations for each and when I came to analyse the data, would make it misleading.

What I'm trying to do is group them by the first few characters as these are always the same.

I thought about using a LEFT function in place of B7. So perhaps:
{=SUM(IF(LEFT(January!$K$2:$K$9945,2)="US",IF(Janu ary!$M$2:$M$9945=LEFT(B7,7),1,0),0))}
But this doesn't work.
The other problem with doing it this way is that the number of characters by which all the variations are still the same differs. So in some cases, I only require the first 3 to be the same. For example:
AER would need to include all the following
AER 1324
AER 2345
AER 2356
(note that my names in column B are always the exact string of characters by which all variations match so in B I have ABER AM and AER).

Had my LEFT function worked I could manually change every value to the length of my names in column B (from 7 to 3) but this would be too time consuming as I have over 500 different names which include over 1000 variations in total.

So I guess my question is, is there any function that I could put into this array and would either pick out any data in sheet 'January' that contains, or begins with the characters I have put into column B.

Hope I've explained it well enough.
Thanks.

AF is the best workaround to learn after you have exploited the Excel built-in functions. It expands the frontiers of known functions to a land only limited by your imagination. When you see the results of multi-column complex computations in a single cell, you will fall in love too.

Could you post some actual data in workbook format? It makes it far easier to understand your requirement and FAR easier to provide you with a solution.