Formula to count cell text by value
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
|