Array counting

[blivy]

I have a long list of data and I'm attempting count the number o
occurences for different conditions using an array formula. I've got s
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(January!$M$2:$M$9945=B7,1,0),0))}

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

The problem is that the names in column M in January do not alway
appear exactly the same. For example, ABER AM includes all th
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 longe
but this would mean having lots of variations for each and when I cam
to analyse the data, would make it misleading.

What I'm trying to do is group them by the first few characters as thes
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(January!$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 o
characters by which all the variations are still the same differs. So i
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 o
characters by which all variations match so in B I have ABER AM an
AER).

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

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

Hope I've explained it well enough.
Thanks

 

[blivy]

Okay so I thought about an alternative but I still need a bit of help.
thought about using a vlookup in combination so I have something alon
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 th
same name so that I would just have

ABER AM 20
AER 5

in a bar chart?

Thanks

 

[Spencer101]

Okay so I thought about an alternative but I still need a bit of help.
thought about using a vlookup in combination so I have something alon
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 th
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. I
2003 or earlier you can use sumproduct. Far easier than arra
formulas...

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

 

[Spencer101]

I have a long list of data and I'm attempting count the number o
occurences for different conditions using an array formula. I've got s
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(January!$M$2:$M$9945=B7,1,0),0))}

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

The problem is that the names in column M in January do not alway
appear exactly the same. For example, ABER AM includes all th
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 longe
but this would mean having lots of variations for each and when I cam
to analyse the data, would make it misleading.

What I'm trying to do is group them by the first few characters as thes
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(January!$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 o
characters by which all the variations are still the same differs. So i
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 o
characters by which all variations match so in B I have ABER AM an
AER).

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

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

Hope I've explained it well enough.
Thanks.

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