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


.