Help with counting postcode types

[NJ34A]

Hi, I'm very basic in my knowledge of excel but can really do with some help.
I simple want to be able to count partial post codes but the problem, I'm finding is the formula is either counting post codes it shouldn't not working at all.
I want to count all post codes starting with B without also counting postcodes in BA or BB etc and likewise for other post codes
Please make it as easy as possible - Thanking you kindly

AB	Aberdeen
AL	St Albans
B	Birmingham
BA	Bath
BB	Blackburn		13 (Highest number 18)
BD	Bradford		24
BH	Bournemouth		26 (Highest number 31)
BL	Bolton

 

[alansidman]

You say you don't want to count BB but you show in your sample a value. Confused. Do you want to count all Post Codes starting with B? Can you explain in full detail your requirements as what you have said is not entirely clear.

 

[rlv01]

I want to count all post codes starting with B without also counting postcodes in BA or BB

Do you mean something like this?

Book1
	A	B	C	D
1	Post Code	Loc		Count post codes starting with B w/o BA or BB
2	AB	Aberdeen		4
3	AL	St Albans
4	B	Birmingham
5	BA	Bath
6	BB	Blackburn
7	BD	Bradford
8	BH	Bournemouth
9	BL	Bolton
Sheet1

Cell Formulas
Range	Formula
D2	D2	=COUNTIFS(A2:A9,"=B*")-COUNTIF(A2:A9,"BA")-COUNTIF(A2:A9,"BB")

 

[NJ34A]

Hi

Thanks for your response, yes that is correct

 

[NJ34A]

Hi

Thanks for your solution, will work on that now

Kind Regards

 

[NJ34A]

Do you mean something like this?

Book1
	A	B	C	D
1	Post Code	Loc		Count post codes starting with B w/o BA or BB
2	AB	Aberdeen		4
3	AL	St Albans
4	B	Birmingham
5	BA	Bath
6	BB	Blackburn
7	BD	Bradford
8	BH	Bournemouth
9	BL	Bolton
Sheet1

Cell Formulas
Range	Formula
D2	D2	=COUNTIFS(A2:A9,"=B*")-COUNTIF(A2:A9,"BA")-COUNTIF(A2:A9,"BB")

Hi

It's still counting the wrong fields maybe I have done something wrong.
Here is what I have done.
Working with the below post codes on the left.
Here is my formula based on what you have supplied

=COUNTIFS(AV21:AV515,"=B*")-COUNTIF(AV21:AV515,"BA")-COUNTIF(AV21:AV515,"BB")-COUNTIF(AV21:AV515,"BD")-COUNTIF(AV21:AV515,"BH")-COUNTIF(AV21:AV515,"BL")-COUNTIF(AV21:AV515,"BN")-COUNTIF(AV21:AV515,"BR")-COUNTIF(AV21:AV515,"BS")-COUNTIF(AV21:AV515,"BT")

B	Birmingham
BA	Bath
BB	Blackburn
BD	Bradford
BH	Bournemouth
BL	Bolton
BN	Brighton
BR	Bromley
BS	Bristol
BT	Belfast

These are the post codes I put in to test, but for B (Birmingham) the count after executing the formula is 4

SW12 1TY

SK 2

SK1 E45

AL L90

AB Y78

B1 24

BA 54

AB34 B67

BB1

B1 232

 

[rlv01]

I think you should consider posting some actual data to indicate what you want to count vs. what you want to exclude. Example:

Book1
	A	B	C
1	Example Data	Starting with "B" Items to include	Starting with "B" Items to exclude
2	SW12 1TY
3	SK 2
4	SK1 E45
5	AL L90
6	AB Y78
7	B1 24	x
8	BA 54		x
9	AB34 B67
10	BB1		x
11	B1 232	x
12	SW12 1TY
13	SK 2
14	SK1 E45
15	AL L90
16	AB Y78
17	B1 24	x
18	BA 54		x
19	AB34 B67
20	BB1		x
21	B1 232	x
Sheet2

Condsider using the free XL2BB tool (link below) to post your data in a way that makes it accessible to others.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[NJ34A]

Hi Again,

do not have no data, downloaded the file but it wont open in protected view.

 

[NJ34A]

I think you should consider posting some actual data to indicate what you want to count vs. what you want to exclude. Example:

Book1
	A	B	C
1	Example Data	Starting with "B" Items to include	Starting with "B" Items to exclude
2	SW12 1TY
3	SK 2
4	SK1 E45
5	AL L90
6	AB Y78
7	B1 24	x
8	BA 54		x
9	AB34 B67
10	BB1		x
11	B1 232	x
12	SW12 1TY
13	SK 2
14	SK1 E45
15	AL L90
16	AB Y78
17	B1 24	x
18	BA 54		x
19	AB34 B67
20	BB1		x
21	B1 232	x
Sheet2

Condsider using the free XL2BB tool (link below) to post your data in a way that makes it accessible to others.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

This is exactly what I'm trying to achieve

 

[NJ34A]

Thankyou.
It's working now

Thanking you kindly