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 that I could drag down.

Hope I've explained it well enough.
Thanks.

Okay so I thought about an alternative but I still need a bit of help. I thought about using a vlookup in combination so I have something along the lines of this:

ABER AM AB145-------ABER AM 5
ABER AM AB145-------ABER AM 4
ABER AM AB250-------ABER AM 3
ABER AM AB250-------ABER AM 5
ABER AM AB35--------ABER AM 3
AER 132--------------AER 2
AER 234--------------AER 2
AER 235--------------AER 1
etc.

But how could you create a graph that would combine the cells with the same name so that I would just have

ABER AM 20
AER 5

in a bar chart?

Thanks.

If you're using Excel 2007 or later you can use countifs to do this. If 2003 or earlier you can use sumproduct. Far easier than array formulas...

To show you exactly how I would need to see an example workbook.