Counting postcodes

I'm working on a summary of premium postcode deliveries, whereby I have a sheet with raw data, and a summary sheet which tells me how many times the postcode appears.

The problem is that sometimes we're interested in a different number of digits of the postcode for instance:

IV (all IV postcodes)
or
IV6 (Only IV6 not any other IV codes)
or
IV6 1 (only IV6 1 codes not any other IV6 codes)

I have about 50 postcodes entries (i.e. IV, ED6, TR12 4) in total to summarize. Is there a quick way to count the number of occurrences of each from the list?

I bodged up a way that works yesterday, but the postcode data is 64000 rows long, and my method effectively mirrored this 5 times (and crashes my machine every 5 mins). I'll post an example of how I bodged if no-one has any simpler ideas (otherwise I'd rather not embarrass myself lol).

Maybe you could use a Pivot Table? This will tell you how many of each postcodes you have

I've tried playing with pivot tables but it claims I have too much data to fit, or something similar.

Can you use a countif with a 'contains' text element? At the moment, I'm using an andvanced filter and countif using the LEN function, but its using way too much calculation power.

You can also use Data Subtotals if your potscode column is sorted

Originally Posted by mikeyfear

Can you use a countif with a 'contains' text element?

COUNTIF accepts wildcards, so to count all postcodes that start with "IV"

=COUNTIF(range,"IV*")

Blonde moment... I spent ages yesterday trying to do just that, but I couldn't add the wildcard to a cell value so I gave up. I only just thought I can just add a CountIF(B:B, A1 & "*") .

Thanks tho, that's just what I was after. My 48mb file is now 300k (oopsy).