Hi Biff

I can't really explain it any other way mate. Our postcode system has both
single and double letters followed by area codes For example

west London might be W1 5cg
north west London might be NW12 5cg

the search I trying to is to collate info on areas basedon the first part of
the post code so I might want to check how many products went to NW area and
the check the products for N1 area too. On the single letter postcodes, I
have to include the number as it serves the same purpose of the W in the
above example

I can filter the 2 letter ones ok but the ones with a single letter and
number, it returns ALL postcodes that start with the letter being searched
rather than the result for the search. Example
NW1
NW5
NW6
NW14
N1
N7
N12
Search criteria "NW" returns 4
search for "N" returns 7 where it needs to return 3

regs

Nigel


"T. Valko" wrote:

it could be N1 which is 2 but it can also be N16 which is 3

You said you wanted to count the ones that start with a single letter N so
both of those would be counted with the SUMPRODUCT portion of the formula.

I'm is the US and we don't have postcodes like that so I'm not familiar with
your postcode system. The only way I can figure this out is to actually see
the REAL data for myself or if you post enough samples and the expected
results so that I can see the all the possible "nuances" that have to be
dealt with.

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hello mate

thanks for your help so far, it's really appreciated :)

the formula wont work because the of length of the cell content. It could
be
NG which is 2 and also it could be N1 which is 2 but it can also be N16
which
is 3 unless it counted the letters Only excluding the numbers.

Cheers mate

Nigel



"T. Valko" wrote:

Man, I'll be glad when we get this sorted. <g ...and we will!

Ok, a single formula...

Data in the range C2:C15, criteria in the range E2:E4.

=IF(LEN(E2)=1,SUMPRODUCT(--(LEFT(C$2:C$15)=E2),--(ISNUMBER(-MID(C$2:C$15,2,1)))),COUNTIF(C$2:C$15,E2&"*"))

Copied down

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...

Hi

thanks for your reply. There are 2 formulas here. Only problem is, the
criteria range is changeable so a formula would need to be able to
handle
both single and double letters in postcode range with the one formula.

Would an if scenario be needed?

Many thanks


Nigel
"T. Valko" wrote:

For postcodes that start with 2 letters you can use a formula like
this...

Data
.............C........
2...NG12 7HR
3...NW3 8NC
4...NW8 2SA
5...N14 9KJ

Criteria
........E....
2...NG
3...NW
4...N

Formulas
.......F......
2...=COUNTIF(C$2:C$15,E2&"*")
3...=COUNTIF(C$2:C$15,E3&"*")
4...=SUMPRODUCT(--(LEFT(C2:C15)=E4),--(ISNUMBER(-MID(C2:C15,2,1))))

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi,

sorry it took a while to get back, been really busy. my range in
column
C
holds postcodes like below-

NG12 7HR
NW3 8NC
NW8 2SA
N14 9KJ

i have products searching for areas so each result cell wants to
look
up
the
postcode for example-

NG - 1
NW - 2
N - 1

the results are returned based on how many postcodes contain the
first
2
letters so NG = 1 as there is only 1 postcode with NG. NW returns 2
as
there
are 2 postcodes that contain NW. N however returns 4 as there are 4
postcodes
that contain N whereas, i need to return 1 as in reality, only 1
postcode
has
the letter N then a number whereas the others have 2 letters then a
number.

i need to create a formula that doesnt have the postcode hard keyed
as
there
are hundreds of postcode variations with hundreds of products so i
wanted
to
do a formula that i could drag down. there is a cell that contains
the
first
2 letters of the postcode which the formula uses to count the
instances
of
the postcode in the list. i have tried countif, sumproduct, if,
counta
which
dont seem to return the result. i then tried a MID,2,1 to test the
2nd
character for a number but that didnt work either so my basic
question
is-

how can i return a result in a list of postcodes that all start with
the
same letter but count only the ones with a single letter not a
double
letter?



many thanks,


Nigel


.



.



.