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