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


.



.