 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
got a list of postcodes & need to count how many of each postcode group (ie WS13, WS 14) there are.
In column B there is a list of post codes nearly 3000 long, like this
WS13 8QR
WS14 6RP
WS14 9DG
WS14 9EF
WS15 1AE
WS15 1BP
etc
I Want it to return how many WS13, WS14 etc there are, cant get =countif to work as I want.
???
split the column on ' ' (space) - it'll make life easier for you.
then sort by prefix and split with a count.
you can create an additional column that creates the first part for you by using this formula =LEFT(#CEll#,LEN(#CELL#)-4)
where #cell# is the postcode, you can then run a pivot table to get counts.
The above formula will be able to take into account WS1 etc.
edit: or use the simple method by wwaswas above, doh!
ta
[EDIT]D'oh sorry, was suggesting Countif and then read the last line of the post...
Add a new column with =LEFT(A1,FIND(" ",A1)-1)
If you dont already have a list of unique postcode stems (WR13, WR14 etc) to do your counting against then you can extract them from your list using:
http://www.cpearson.com/excel/ListFunctions.aspx
and
http://www.cpearson.com/excel/noblanks.htm
try this:
Assuming your list of postcodes is in cells B1 to B3000
List your codes that you want to count, the WS13 etc in C1 to say C50
Now in cell D1 enter this function
[code]=COUNTIF($B$1:$B$3000,C1&"*")[/code]
And copy it down next to all of your WS13 etcs
The &"*" in the function is a text wild card so counts anything starting with the text you set as your criteria.
will try that, am I in for a fun morning or what ...
acjim,it works but includes B11, B12 in the B1s.
as you say, acjim's shortcut wont work.
Once youve pulled out your "thin" list, then use the countif function to count incidences.
oh I see, sorry long and very dull day yesterday!