Home Forum Chat Forum Excel help pretty please

Viewing 12 posts - 1 through 12 (of 12 total)
• scruff
Member

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.

???

wwaswas
Subscriber

split the column on ‘ ‘ (space) – it’ll make life easier for you.

then sort by prefix and split with a count.

WackoAK
Member

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!

scruff
Member
thepurist
Subscriber

[EDIT]D’oh sorry, was suggesting Countif and then read the last line of the post…

djglover
Member

Add a new column with =LEFT(A1,FIND(” “,A1)-1)

Stoner
Subscriber

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

acjim
Member

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

`=COUNTIF(\$B\$1:\$B\$3000,C1&"*")`

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.

scruff
Member

will try that, am I in for a fun morning or what …

scruff
Member

acjim,it works but includes B11, B12 in the B1s.

Stoner
Subscriber

as you say, acjim’s shortcut wont work.

Once youve pulled out your “thin” list, then use the countif function to count incidences.

acjim
Member

oh I see, sorry long and very dull day yesterday!

Viewing 12 posts - 1 through 12 (of 12 total)

The topic ‘Excel help pretty please’ is closed to new replies.