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


.