COUNTIF not calculating postcodes correctly

[davo3286]

Hi,

I am trying to use COUNTIF to count postcodes however, I am struggling to get this to work with some postcodes starting with the same character.

Uploaded a desensitised sample for context and highlighted the error in red.

Any help with the formula is much appreciated!

Thanks

[RChad]

Maybe try :

Please Login or Register  to view this content.

[davo3286]

My apologies - the sample was not as clear as it should have been. The postcodes column will be actual full postcodes (I used '...' to show there will be more characters).

I have amended my sample and re-uploaded.

Please note all postcodes in the sample are made up and not sensitive.

[RChad]

There needs to be some consistency for your lookup. Maybe the first 2 characters would work then we can use LEFT in the formula.

Please Login or Register  to view this content.

[Bo_Ry]

Please try

for 1 digit number after District Code
=COUNTIF(Table1,C2&"? *")

for 1-2 digit number after District Code
=SUMPRODUCT(--(LEFT(Table1,MMULT(--ISNUMBER(-MID(Table1,{2,3},1)),{1;2}))=C2))

[davo3286]

Thanks for your responses, much appreciated!

My only concern is that on my live workbook, postcodes are constantly added to the document. Therefore I am looking for one formula to cover the full list, regardless of one or two digits, if that makes sense?

I have a full list of all district codes and that won't change, I will simply adding postcodes and hope the one formula can do the equation?

[Bob Phillips]

Bo_Ry's formula does exactly that, have you tried it?

[davo3286]

I have added two additional postcodes to the list and highlighted in green. Their district should be 'L' and 'B' however they are not added to the total count.

I do apologise if I'm not explaining myself very well, I do appreciate your time.

[RChad]

You are making this more complicated than it needs to be I am opting out.

[davo3286]

I can't help that some district postcodes have one number following and others have two. That's why I'm scratching my head. I'm not an advanced user of Excel, so if I'm making it more difficult than needed, it's certainly not intentional. None the less, appreciate your input.

[Bo_Ry]

Please try

=SUMPRODUCT(--(LEFT(Table1,MOD(MMULT(--ISNUMBER(-MID(Table1,{2,3},1)),{1;2})-1,2)+1)=C2))


Or
=SUMPRODUCT(COUNTIF(Table1,C2&({0,1,2,3,4}+{0;5})&"*"))+COUNTIF(Table1,C2&" *")

[davo3286]

Absolutely spot on - thanks!