Formula to count cell text by value

[NigelShaw]

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

 

[T. Valko]

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

=COUNTIF(C:C,"N")

 

[NigelShaw]

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]

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*")

 

[NigelShaw]

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]

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

 

[NigelShaw]

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

 

[T. Valko]

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))))

 

[NigelShaw]

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]

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

 

[NigelShaw]

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]

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.

 

[NigelShaw]

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]

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?