Formula for postal codes

[Rebecca]

Hi,
I have a column containing hundreds of postal codes,
mostly begining with N1M, but also N0B, N1G, L0N, etc. I
need a formula that will sum up the number of postal codes
beginning with N1M. I tried =COUNTIF(S3:S356,"N1M"), but
it returned a zero. Is the problem that the the N1M is
followed by 3 more letters/numbers, such as N1M 2E4, N1M
3W3, ETC. Can you guys help?

 

[Peo Sjoblom]

=COUNTIF(S3:S356,"N1M *")

or

=COUNTIF(S3:S356,"N1M*")




--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Kostis Vezerides]

Hi,

=COUNT(IF(LEFT(S3:S356,3)="N1M",1,""))
NOTE: This is an ARRAY FORMULA! You must enter it with the
combination Shift+Ctrl+Enter. If you fail doing this the
first time, double click on the cell and repeat
Shift+Ctrl+Enter.

Assuming you have several such parts w/ code prefixes of
interest in another range, say in range A2:A18, then you
can have the count next to them, in cells B2:B18, with the
following more general formula:

B2 =COUNT(IF(LEFT($S$3:$S$356,len(A2))=A2,1,"")).
Array-enter the first formula as above, and then copy.

HTH
Kostis Vezerides