Hi

I'm trying to write a formula that searches a range to values based on but I
can't get it to work.

The range is column C

the problem is when I get to a postcode that has only 1 letter. I am
currently using sumproduct to check the range and using left(B37, 2) to get
only the first 2 letters. If B37 contains NG, I get the count of all
postcodes with NG but some postcodes only have 1 letter. When this happens, I
get ALL postcodes that start with N instead of postcodes that only have N

is there a formula? I've been down the road of If Then formulas and countif
etc but I can't make the filter.


Any help would be really appreciated


Nigel

If they only have one letter and you want to count how many are "N" then:

=COUNTIF(C:C,"N")

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi

I'm trying to write a formula that searches a range to values based on but
I
can't get it to work.

The range is column C

the problem is when I get to a postcode that has only 1 letter. I am
currently using sumproduct to check the range and using left(B37, 2) to
get
only the first 2 letters. If B37 contains NG, I get the count of all
postcodes with NG but some postcodes only have 1 letter. When this
happens, I
get ALL postcodes that start with N instead of postcodes that only have N

is there a formula? I've been down the road of If Then formulas and
countif
etc but I can't make the filter.


Any help would be really appreciated


Nigel

Hi

I need to split the count so I get a result for postcodes that only have a
single N and a separate result with postcodes that have 2 letters NG. The
countif returns a result with all postcodes containing N irrespective of a
single or double letter. For example

N1
N3
N5
NH3
NH15
NH4
NH9

a countif returns 7. I need to return 3 if the criteria is N and 4 if the
criteria is NH

regs

Nigel
"T. Valko" wrote:

If they only have one letter and you want to count how many are "N" then:

=COUNTIF(C:C,"N")

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi

I'm trying to write a formula that searches a range to values based on but
I
can't get it to work.

The range is column C

the problem is when I get to a postcode that has only 1 letter. I am
currently using sumproduct to check the range and using left(B37, 2) to
get
only the first 2 letters. If B37 contains NG, I get the count of all
postcodes with NG but some postcodes only have 1 letter. When this
happens, I
get ALL postcodes that start with N instead of postcodes that only have N

is there a formula? I've been down the road of If Then formulas and
countif
etc but I can't make the filter.


Any help would be really appreciated


Nigel


.

the problem is when I get to a postcode that has only 1 letter.

You didn't say that one letter would be followed by numbers so I thought you
had data like this:

N
NHxx
NHx
N
N

N1
N3
N5
NH3
NH15
NH4
NH9
a countif returns 7. I need to return 3 if the criteria is N and 4 if the
criteria is NH

Try these:

To count entries that are 2 characters long and start with N:

=COUNTIF(A1:A7,"N?")

To count entries that start with NH:

=COUNTIF(A1:A7,"NH*")

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...

Hi

I need to split the count so I get a result for postcodes that only have a
single N and a separate result with postcodes that have 2 letters NG. The
countif returns a result with all postcodes containing N irrespective of a
single or double letter. For example

N1
N3
N5
NH3
NH15
NH4
NH9

a countif returns 7. I need to return 3 if the criteria is N and 4 if the
criteria is NH

regs

Nigel
"T. Valko" wrote:

If they only have one letter and you want to count how many are "N" then:

=COUNTIF(C:C,"N")

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi

I'm trying to write a formula that searches a range to values based on
but
I
can't get it to work.

The range is column C

the problem is when I get to a postcode that has only 1 letter. I am
currently using sumproduct to check the range and using left(B37, 2) to
get
only the first 2 letters. If B37 contains NG, I get the count of all
postcodes with NG but some postcodes only have 1 letter. When this
happens, I
get ALL postcodes that start with N instead of postcodes that only have
N

is there a formula? I've been down the road of If Then formulas and
countif
etc but I can't make the filter.


Any help would be really appreciated


Nigel


.

Hi

thanks for your reply.

Can the formula be set up so only one formula can return the correct result
regardless of the postcode start? I was checking the first 2 letters only
because UK postcodes have either 2 letters or 1 only.

So basically, my one formula should return the correct count if the postcode
had 1 or 2 letters based on the value it is searching for. I will change the
criteria to a cell so if I then change the cell contents, the correct count
should be shown based on the search.

The thing I can't do is separate out the single letter postcodes.

My thought was to use an If scenario and split the postcode i.e.

If(MID(2,1)=1, do something, do something else)

I have tried checking the value for a number but doesn't work.


Regs

Nigel

"T. Valko" wrote:

the problem is when I get to a postcode that has only 1 letter.

You didn't say that one letter would be followed by numbers so I thought you
had data like this:

N
NHxx
NHx
N
N

N1
N3
N5
NH3
NH15
NH4
NH9
a countif returns 7. I need to return 3 if the criteria is N and 4 if the
criteria is NH

Try these:

To count entries that are 2 characters long and start with N:

=COUNTIF(A1:A7,"N?")

To count entries that start with NH:

=COUNTIF(A1:A7,"NH*")

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...

Hi

I need to split the count so I get a result for postcodes that only have a
single N and a separate result with postcodes that have 2 letters NG. The
countif returns a result with all postcodes containing N irrespective of a
single or double letter. For example

N1
N3
N5
NH3
NH15
NH4
NH9

a countif returns 7. I need to return 3 if the criteria is N and 4 if the
criteria is NH

regs

Nigel
"T. Valko" wrote:

If they only have one letter and you want to count how many are "N" then:

=COUNTIF(C:C,"N")

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi

I'm trying to write a formula that searches a range to values based on
but
I
can't get it to work.

The range is column C

the problem is when I get to a postcode that has only 1 letter. I am
currently using sumproduct to check the range and using left(B37, 2) to
get
only the first 2 letters. If B37 contains NG, I get the count of all
postcodes with NG but some postcodes only have 1 letter. When this
happens, I
get ALL postcodes that start with N instead of postcodes that only have
N

is there a formula? I've been down the road of If Then formulas and
countif
etc but I can't make the filter.


Any help would be really appreciated


Nigel


.



.

Can you post several *real examples* and tell us what result you expect?

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi

thanks for your reply.

Can the formula be set up so only one formula can return the correct
result
regardless of the postcode start? I was checking the first 2 letters only
because UK postcodes have either 2 letters or 1 only.

So basically, my one formula should return the correct count if the
postcode
had 1 or 2 letters based on the value it is searching for. I will change
the
criteria to a cell so if I then change the cell contents, the correct
count
should be shown based on the search.

The thing I can't do is separate out the single letter postcodes.

My thought was to use an If scenario and split the postcode i.e.

If(MID(2,1)=1, do something, do something else)

I have tried checking the value for a number but doesn't work.


Regs

Nigel

"T. Valko" wrote:

the problem is when I get to a postcode that has only 1 letter.

You didn't say that one letter would be followed by numbers so I thought
you
had data like this:

N
NHxx
NHx
N
N

N1
N3
N5
NH3
NH15
NH4
NH9
a countif returns 7. I need to return 3 if the criteria is N and 4 if
the
criteria is NH

Try these:

To count entries that are 2 characters long and start with N:

=COUNTIF(A1:A7,"N?")

To count entries that start with NH:

=COUNTIF(A1:A7,"NH*")

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...

Hi

I need to split the count so I get a result for postcodes that only
have a
single N and a separate result with postcodes that have 2 letters NG.
The
countif returns a result with all postcodes containing N irrespective
of a
single or double letter. For example

N1
N3
N5
NH3
NH15
NH4
NH9

a countif returns 7. I need to return 3 if the criteria is N and 4 if
the
criteria is NH

regs

Nigel
"T. Valko" wrote:

If they only have one letter and you want to count how many are "N"
then:

=COUNTIF(C:C,"N")

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi

I'm trying to write a formula that searches a range to values based
on
but
I
can't get it to work.

The range is column C

the problem is when I get to a postcode that has only 1 letter. I am
currently using sumproduct to check the range and using left(B37, 2)
to
get
only the first 2 letters. If B37 contains NG, I get the count of all
postcodes with NG but some postcodes only have 1 letter. When this
happens, I
get ALL postcodes that start with N instead of postcodes that only
have
N

is there a formula? I've been down the road of If Then formulas and
countif
etc but I can't make the filter.


Any help would be really appreciated


Nigel


.



.

Hi,

sorry it took a while to get back, been really busy. my range in column C
holds postcodes like below-

NG12 7HR
NW3 8NC
NW8 2SA
N14 9KJ

i have products searching for areas so each result cell wants to look up the
postcode for example-

NG - 1
NW - 2
N - 1

the results are returned based on how many postcodes contain the first 2
letters so NG = 1 as there is only 1 postcode with NG. NW returns 2 as there
are 2 postcodes that contain NW. N however returns 4 as there are 4 postcodes
that contain N whereas, i need to return 1 as in reality, only 1 postcode has
the letter N then a number whereas the others have 2 letters then a number.

i need to create a formula that doesnt have the postcode hard keyed as there
are hundreds of postcode variations with hundreds of products so i wanted to
do a formula that i could drag down. there is a cell that contains the first
2 letters of the postcode which the formula uses to count the instances of
the postcode in the list. i have tried countif, sumproduct, if, counta which
dont seem to return the result. i then tried a MID,2,1 to test the 2nd
character for a number but that didnt work either so my basic question is-

how can i return a result in a list of postcodes that all start with the
same letter but count only the ones with a single letter not a double letter?



many thanks,


Nigel

For postcodes that start with 2 letters you can use a formula like this...

Data
.............C........
2...NG12 7HR
3...NW3 8NC
4...NW8 2SA
5...N14 9KJ

Criteria
........E....
2...NG
3...NW
4...N

Formulas
.......F......
2...=COUNTIF(C$2:C$15,E2&"*")
3...=COUNTIF(C$2:C$15,E3&"*")
4...=SUMPRODUCT(--(LEFT(C2:C15)=E4),--(ISNUMBER(-MID(C2:C15,2,1))))

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi,

sorry it took a while to get back, been really busy. my range in column C
holds postcodes like below-

NG12 7HR
NW3 8NC
NW8 2SA
N14 9KJ

i have products searching for areas so each result cell wants to look up
the
postcode for example-

NG - 1
NW - 2
N - 1

the results are returned based on how many postcodes contain the first 2
letters so NG = 1 as there is only 1 postcode with NG. NW returns 2 as
there
are 2 postcodes that contain NW. N however returns 4 as there are 4
postcodes
that contain N whereas, i need to return 1 as in reality, only 1 postcode
has
the letter N then a number whereas the others have 2 letters then a
number.

i need to create a formula that doesnt have the postcode hard keyed as
there
are hundreds of postcode variations with hundreds of products so i wanted
to
do a formula that i could drag down. there is a cell that contains the
first
2 letters of the postcode which the formula uses to count the instances of
the postcode in the list. i have tried countif, sumproduct, if, counta
which
dont seem to return the result. i then tried a MID,2,1 to test the 2nd
character for a number but that didnt work either so my basic question is-

how can i return a result in a list of postcodes that all start with the
same letter but count only the ones with a single letter not a double
letter?



many thanks,


Nigel

Hi

thanks for your reply. There are 2 formulas here. Only problem is, the
criteria range is changeable so a formula would need to be able to handle
both single and double letters in postcode range with the one formula.

Would an if scenario be needed?

Many thanks


Nigel
"T. Valko" wrote:

For postcodes that start with 2 letters you can use a formula like this...

Data
.............C........
2...NG12 7HR
3...NW3 8NC
4...NW8 2SA
5...N14 9KJ

Criteria
........E....
2...NG
3...NW
4...N

Formulas
.......F......
2...=COUNTIF(C$2:C$15,E2&"*")
3...=COUNTIF(C$2:C$15,E3&"*")
4...=SUMPRODUCT(--(LEFT(C2:C15)=E4),--(ISNUMBER(-MID(C2:C15,2,1))))

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi,

sorry it took a while to get back, been really busy. my range in column C
holds postcodes like below-

NG12 7HR
NW3 8NC
NW8 2SA
N14 9KJ

i have products searching for areas so each result cell wants to look up
the
postcode for example-

NG - 1
NW - 2
N - 1

the results are returned based on how many postcodes contain the first 2
letters so NG = 1 as there is only 1 postcode with NG. NW returns 2 as
there
are 2 postcodes that contain NW. N however returns 4 as there are 4
postcodes
that contain N whereas, i need to return 1 as in reality, only 1 postcode
has
the letter N then a number whereas the others have 2 letters then a
number.

i need to create a formula that doesnt have the postcode hard keyed as
there
are hundreds of postcode variations with hundreds of products so i wanted
to
do a formula that i could drag down. there is a cell that contains the
first
2 letters of the postcode which the formula uses to count the instances of
the postcode in the list. i have tried countif, sumproduct, if, counta
which
dont seem to return the result. i then tried a MID,2,1 to test the 2nd
character for a number but that didnt work either so my basic question is-

how can i return a result in a list of postcodes that all start with the
same letter but count only the ones with a single letter not a double
letter?



many thanks,


Nigel


.

Man, I'll be glad when we get this sorted. <g ...and we will!

Ok, a single formula...

Data in the range C2:C15, criteria in the range E2:E4.

=IF(LEN(E2)=1,SUMPRODUCT(--(LEFT(C$2:C$15)=E2),--(ISNUMBER(-MID(C$2:C$15,2,1)))),COUNTIF(C$2:C$15,E2&"*"))

Copied down

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...

Hi

thanks for your reply. There are 2 formulas here. Only problem is, the
criteria range is changeable so a formula would need to be able to handle
both single and double letters in postcode range with the one formula.

Would an if scenario be needed?

Many thanks


Nigel
"T. Valko" wrote:

For postcodes that start with 2 letters you can use a formula like
this...

Data
.............C........
2...NG12 7HR
3...NW3 8NC
4...NW8 2SA
5...N14 9KJ

Criteria
........E....
2...NG
3...NW
4...N

Formulas
.......F......
2...=COUNTIF(C$2:C$15,E2&"*")
3...=COUNTIF(C$2:C$15,E3&"*")
4...=SUMPRODUCT(--(LEFT(C2:C15)=E4),--(ISNUMBER(-MID(C2:C15,2,1))))

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi,

sorry it took a while to get back, been really busy. my range in column
C
holds postcodes like below-

NG12 7HR
NW3 8NC
NW8 2SA
N14 9KJ

i have products searching for areas so each result cell wants to look
up
the
postcode for example-

NG - 1
NW - 2
N - 1

the results are returned based on how many postcodes contain the first
2
letters so NG = 1 as there is only 1 postcode with NG. NW returns 2 as
there
are 2 postcodes that contain NW. N however returns 4 as there are 4
postcodes
that contain N whereas, i need to return 1 as in reality, only 1
postcode
has
the letter N then a number whereas the others have 2 letters then a
number.

i need to create a formula that doesnt have the postcode hard keyed as
there
are hundreds of postcode variations with hundreds of products so i
wanted
to
do a formula that i could drag down. there is a cell that contains the
first
2 letters of the postcode which the formula uses to count the instances
of
the postcode in the list. i have tried countif, sumproduct, if, counta
which
dont seem to return the result. i then tried a MID,2,1 to test the 2nd
character for a number but that didnt work either so my basic question
is-

how can i return a result in a list of postcodes that all start with
the
same letter but count only the ones with a single letter not a double
letter?



many thanks,


Nigel


.

Hello mate

thanks for your help so far, it's really appreciated :)

the formula wont work because the of length of the cell content. It could be
NG which is 2 and also it could be N1 which is 2 but it can also be N16 which
is 3 unless it counted the letters Only excluding the numbers.

Cheers mate

Nigel



"T. Valko" wrote:

Man, I'll be glad when we get this sorted. <g ...and we will!

Ok, a single formula...

Data in the range C2:C15, criteria in the range E2:E4.

=IF(LEN(E2)=1,SUMPRODUCT(--(LEFT(C$2:C$15)=E2),--(ISNUMBER(-MID(C$2:C$15,2,1)))),COUNTIF(C$2:C$15,E2&"*"))

Copied down

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...

Hi

thanks for your reply. There are 2 formulas here. Only problem is, the
criteria range is changeable so a formula would need to be able to handle
both single and double letters in postcode range with the one formula.

Would an if scenario be needed?

Many thanks


Nigel
"T. Valko" wrote:

For postcodes that start with 2 letters you can use a formula like
this...

Data
.............C........
2...NG12 7HR
3...NW3 8NC
4...NW8 2SA
5...N14 9KJ

Criteria
........E....
2...NG
3...NW
4...N

Formulas
.......F......
2...=COUNTIF(C$2:C$15,E2&"*")
3...=COUNTIF(C$2:C$15,E3&"*")
4...=SUMPRODUCT(--(LEFT(C2:C15)=E4),--(ISNUMBER(-MID(C2:C15,2,1))))

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi,

sorry it took a while to get back, been really busy. my range in column
C
holds postcodes like below-

NG12 7HR
NW3 8NC
NW8 2SA
N14 9KJ

i have products searching for areas so each result cell wants to look
up
the
postcode for example-

NG - 1
NW - 2
N - 1

the results are returned based on how many postcodes contain the first
2
letters so NG = 1 as there is only 1 postcode with NG. NW returns 2 as
there
are 2 postcodes that contain NW. N however returns 4 as there are 4
postcodes
that contain N whereas, i need to return 1 as in reality, only 1
postcode
has
the letter N then a number whereas the others have 2 letters then a
number.

i need to create a formula that doesnt have the postcode hard keyed as
there
are hundreds of postcode variations with hundreds of products so i
wanted
to
do a formula that i could drag down. there is a cell that contains the
first
2 letters of the postcode which the formula uses to count the instances
of
the postcode in the list. i have tried countif, sumproduct, if, counta
which
dont seem to return the result. i then tried a MID,2,1 to test the 2nd
character for a number but that didnt work either so my basic question
is-

how can i return a result in a list of postcodes that all start with
the
same letter but count only the ones with a single letter not a double
letter?



many thanks,


Nigel


.



.

it could be N1 which is 2 but it can also be N16 which is 3

You said you wanted to count the ones that start with a single letter N so
both of those would be counted with the SUMPRODUCT portion of the formula.

I'm is the US and we don't have postcodes like that so I'm not familiar with
your postcode system. The only way I can figure this out is to actually see
the REAL data for myself or if you post enough samples and the expected
results so that I can see the all the possible "nuances" that have to be
dealt with.

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hello mate

thanks for your help so far, it's really appreciated :)

the formula wont work because the of length of the cell content. It could
be
NG which is 2 and also it could be N1 which is 2 but it can also be N16
which
is 3 unless it counted the letters Only excluding the numbers.

Cheers mate

Nigel



"T. Valko" wrote:

Man, I'll be glad when we get this sorted. <g ...and we will!

Ok, a single formula...

Data in the range C2:C15, criteria in the range E2:E4.

=IF(LEN(E2)=1,SUMPRODUCT(--(LEFT(C$2:C$15)=E2),--(ISNUMBER(-MID(C$2:C$15,2,1)))),COUNTIF(C$2:C$15,E2&"*"))

Copied down

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...

Hi

thanks for your reply. There are 2 formulas here. Only problem is, the
criteria range is changeable so a formula would need to be able to
handle
both single and double letters in postcode range with the one formula.

Would an if scenario be needed?

Many thanks


Nigel
"T. Valko" wrote:

For postcodes that start with 2 letters you can use a formula like
this...

Data
.............C........
2...NG12 7HR
3...NW3 8NC
4...NW8 2SA
5...N14 9KJ

Criteria
........E....
2...NG
3...NW
4...N

Formulas
.......F......
2...=COUNTIF(C$2:C$15,E2&"*")
3...=COUNTIF(C$2:C$15,E3&"*")
4...=SUMPRODUCT(--(LEFT(C2:C15)=E4),--(ISNUMBER(-MID(C2:C15,2,1))))

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi,

sorry it took a while to get back, been really busy. my range in
column
C
holds postcodes like below-

NG12 7HR
NW3 8NC
NW8 2SA
N14 9KJ

i have products searching for areas so each result cell wants to
look
up
the
postcode for example-

NG - 1
NW - 2
N - 1

the results are returned based on how many postcodes contain the
first
2
letters so NG = 1 as there is only 1 postcode with NG. NW returns 2
as
there
are 2 postcodes that contain NW. N however returns 4 as there are 4
postcodes
that contain N whereas, i need to return 1 as in reality, only 1
postcode
has
the letter N then a number whereas the others have 2 letters then a
number.

i need to create a formula that doesnt have the postcode hard keyed
as
there
are hundreds of postcode variations with hundreds of products so i
wanted
to
do a formula that i could drag down. there is a cell that contains
the
first
2 letters of the postcode which the formula uses to count the
instances
of
the postcode in the list. i have tried countif, sumproduct, if,
counta
which
dont seem to return the result. i then tried a MID,2,1 to test the
2nd
character for a number but that didnt work either so my basic
question
is-

how can i return a result in a list of postcodes that all start with
the
same letter but count only the ones with a single letter not a
double
letter?



many thanks,


Nigel


.



.

Hi Biff

I can't really explain it any other way mate. Our postcode system has both
single and double letters followed by area codes For example

west London might be W1 5cg
north west London might be NW12 5cg

the search I trying to is to collate info on areas basedon the first part of
the post code so I might want to check how many products went to NW area and
the check the products for N1 area too. On the single letter postcodes, I
have to include the number as it serves the same purpose of the W in the
above example

I can filter the 2 letter ones ok but the ones with a single letter and
number, it returns ALL postcodes that start with the letter being searched
rather than the result for the search. Example
NW1
NW5
NW6
NW14
N1
N7
N12
Search criteria "NW" returns 4
search for "N" returns 7 where it needs to return 3

regs

Nigel


"T. Valko" wrote:

it could be N1 which is 2 but it can also be N16 which is 3

You said you wanted to count the ones that start with a single letter N so
both of those would be counted with the SUMPRODUCT portion of the formula.

I'm is the US and we don't have postcodes like that so I'm not familiar with
your postcode system. The only way I can figure this out is to actually see
the REAL data for myself or if you post enough samples and the expected
results so that I can see the all the possible "nuances" that have to be
dealt with.

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hello mate

thanks for your help so far, it's really appreciated :)

the formula wont work because the of length of the cell content. It could
be
NG which is 2 and also it could be N1 which is 2 but it can also be N16
which
is 3 unless it counted the letters Only excluding the numbers.

Cheers mate

Nigel



"T. Valko" wrote:

Man, I'll be glad when we get this sorted. <g ...and we will!

Ok, a single formula...

Data in the range C2:C15, criteria in the range E2:E4.

=IF(LEN(E2)=1,SUMPRODUCT(--(LEFT(C$2:C$15)=E2),--(ISNUMBER(-MID(C$2:C$15,2,1)))),COUNTIF(C$2:C$15,E2&"*"))

Copied down

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...

Hi

thanks for your reply. There are 2 formulas here. Only problem is, the
criteria range is changeable so a formula would need to be able to
handle
both single and double letters in postcode range with the one formula.

Would an if scenario be needed?

Many thanks


Nigel
"T. Valko" wrote:

For postcodes that start with 2 letters you can use a formula like
this...

Data
.............C........
2...NG12 7HR
3...NW3 8NC
4...NW8 2SA
5...N14 9KJ

Criteria
........E....
2...NG
3...NW
4...N

Formulas
.......F......
2...=COUNTIF(C$2:C$15,E2&"*")
3...=COUNTIF(C$2:C$15,E3&"*")
4...=SUMPRODUCT(--(LEFT(C2:C15)=E4),--(ISNUMBER(-MID(C2:C15,2,1))))

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi,

sorry it took a while to get back, been really busy. my range in
column
C
holds postcodes like below-

NG12 7HR
NW3 8NC
NW8 2SA
N14 9KJ

i have products searching for areas so each result cell wants to
look
up
the
postcode for example-

NG - 1
NW - 2
N - 1

the results are returned based on how many postcodes contain the
first
2
letters so NG = 1 as there is only 1 postcode with NG. NW returns 2
as
there
are 2 postcodes that contain NW. N however returns 4 as there are 4
postcodes
that contain N whereas, i need to return 1 as in reality, only 1
postcode
has
the letter N then a number whereas the others have 2 letters then a
number.

i need to create a formula that doesnt have the postcode hard keyed
as
there
are hundreds of postcode variations with hundreds of products so i
wanted
to
do a formula that i could drag down. there is a cell that contains
the
first
2 letters of the postcode which the formula uses to count the
instances
of
the postcode in the list. i have tried countif, sumproduct, if,
counta
which
dont seem to return the result. i then tried a MID,2,1 to test the
2nd
character for a number but that didnt work either so my basic
question
is-

how can i return a result in a list of postcodes that all start with
the
same letter but count only the ones with a single letter not a
double
letter?



many thanks,


Nigel


.



.



.

I still don't understand why the below won't work....

...........A..........C
1.....NW1.....NW
2.....NW5.....N
3.....NW6
4.....NW14
5.....N1
6.....N7
7.....N12

C1:C2 are the criteria

Entered in D1 and copied down to D2:

=IF(LEN(C1)=1,SUMPRODUCT(--(LEFT(A$1:A$7)=C1),--(ISNUMBER(-MID(A$1:A$7,2,1)))),COUNTIF(A$1:A$7,C1&"*"))

The results are 4 and 3 which is what you say the results should be.

What am I missing?

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...

Hi Biff

I can't really explain it any other way mate. Our postcode system has both
single and double letters followed by area codes For example

west London might be W1 5cg
north west London might be NW12 5cg

the search I trying to is to collate info on areas basedon the first part
of
the post code so I might want to check how many products went to NW area
and
the check the products for N1 area too. On the single letter postcodes, I
have to include the number as it serves the same purpose of the W in the
above example

I can filter the 2 letter ones ok but the ones with a single letter and
number, it returns ALL postcodes that start with the letter being searched
rather than the result for the search. Example
NW1
NW5
NW6
NW14
N1
N7
N12
Search criteria "NW" returns 4
search for "N" returns 7 where it needs to return 3

regs

Nigel


"T. Valko" wrote:

it could be N1 which is 2 but it can also be N16 which is 3

You said you wanted to count the ones that start with a single letter N
so
both of those would be counted with the SUMPRODUCT portion of the
formula.

I'm is the US and we don't have postcodes like that so I'm not familiar
with
your postcode system. The only way I can figure this out is to actually
see
the REAL data for myself or if you post enough samples and the expected
results so that I can see the all the possible "nuances" that have to be
dealt with.

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hello mate

thanks for your help so far, it's really appreciated :)

the formula wont work because the of length of the cell content. It
could
be
NG which is 2 and also it could be N1 which is 2 but it can also be N16
which
is 3 unless it counted the letters Only excluding the numbers.

Cheers mate

Nigel



"T. Valko" wrote:

Man, I'll be glad when we get this sorted. <g ...and we will!

Ok, a single formula...

Data in the range C2:C15, criteria in the range E2:E4.

=IF(LEN(E2)=1,SUMPRODUCT(--(LEFT(C$2:C$15)=E2),--(ISNUMBER(-MID(C$2:C$15,2,1)))),COUNTIF(C$2:C$15,E2&"*"))

Copied down

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...

Hi

thanks for your reply. There are 2 formulas here. Only problem is,
the
criteria range is changeable so a formula would need to be able to
handle
both single and double letters in postcode range with the one
formula.

Would an if scenario be needed?

Many thanks


Nigel
"T. Valko" wrote:

For postcodes that start with 2 letters you can use a formula like
this...

Data
.............C........
2...NG12 7HR
3...NW3 8NC
4...NW8 2SA
5...N14 9KJ

Criteria
........E....
2...NG
3...NW
4...N

Formulas
.......F......
2...=COUNTIF(C$2:C$15,E2&"*")
3...=COUNTIF(C$2:C$15,E3&"*")
4...=SUMPRODUCT(--(LEFT(C2:C15)=E4),--(ISNUMBER(-MID(C2:C15,2,1))))

--
Biff
Microsoft Excel MVP


"NigelShaw" wrote in message
...
Hi,

sorry it took a while to get back, been really busy. my range in
column
C
holds postcodes like below-

NG12 7HR
NW3 8NC
NW8 2SA
N14 9KJ

i have products searching for areas so each result cell wants to
look
up
the
postcode for example-

NG - 1
NW - 2
N - 1

the results are returned based on how many postcodes contain the
first
2
letters so NG = 1 as there is only 1 postcode with NG. NW returns
2
as
there
are 2 postcodes that contain NW. N however returns 4 as there are
4
postcodes
that contain N whereas, i need to return 1 as in reality, only 1
postcode
has
the letter N then a number whereas the others have 2 letters then
a
number.

i need to create a formula that doesnt have the postcode hard
keyed
as
there
are hundreds of postcode variations with hundreds of products so
i
wanted
to
do a formula that i could drag down. there is a cell that
contains
the
first
2 letters of the postcode which the formula uses to count the
instances
of
the postcode in the list. i have tried countif, sumproduct, if,
counta
which
dont seem to return the result. i then tried a MID,2,1 to test
the
2nd
character for a number but that didnt work either so my basic
question
is-

how can i return a result in a list of postcodes that all start
with
the
same letter but count only the ones with a single letter not a
double
letter?



many thanks,


Nigel


.



.



.