formulas for postal codes

[Rebecca]

Hi,

I have a list of hundreds of postal codes from various
regions, mostly starting with N1M and N0B. The formulas I
use to sum up those two are:

="N1M = "&TEXT(COUNTIF(M3:M357,"N1M*"),"0", and
="N0B = "&TEXT(COUNTIF(M4:M361,"N0B*"),"0")
What I need to do now is to have a sum of all the other
postal codes together that do not begin with N1M or N0B so
I know how many applicants are not within a certain area.
Can you help?

Thanks alot

 

[Peo Sjoblom]

Can't you just subtract the ones you have from the total and that would give
the count for
the non N1M and N0B?

=COUNTA(M3:M361)-countif(1)-countif(2)



--

Regards,

Peo Sjoblom

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

 

[Domenic]

Hi Rebecca,

=SUMPRODUCT(--(A1:A10<>""),--(LEFT(A1:A10,3)<>"N1M"),--(LEFT(A1:A10,3)<>"
N0B"))

Hope this helps!

 

[Rebecca]

Hi, Thanks, but it said I entered too few argument. I
assumed that where you wrote countif(1)-countif(2) I put
in countif(N1M)-(N0B)
Is there any other formula?

 

[Debra Dalgleish]

You can use the COUNTA function:

="Other = " &
(COUNTA(M3:M361)-1-COUNTIF(M3:M361,"N1M*")-COUNTIF(M3:M361,"N0B*"))

Also, you should check your other formulas, because they're counting
slightly different ranges (M3:M357, M4:M361), and you might miss some
postal codes.

 

[Peo Sjoblom]

You should replace the countif(1) in my example with
COUNTIF(M3:M357,"N1M*") and countif(2) with
COUNTIF(M4:M361,"N0B*")


--

Regards,

Peo Sjoblom

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

 

[Rebecca]

Thanks Debra,

The reason some of my ranges are different(such as
M3:M357, M4:M361)is I guess because The formulas were
created when I was only at M357 and I guess that one didnt
take into account when I added the next few rows, while
the other formula did. Do you know what I mean? I would
like to figure this out since I am not going to be the end
user of this database.

 

[Rebecca]

Ok, but what does this formula do differently? Iam also
trying to understand how this program works so I can
better explain it to the end user of this database

 

[Debra Dalgleish]

You're welcome. To allow for additional rows in the table, you could
include the entire column in the countif formula, or a large range. For
example:

="N1M = "&TEXT(COUNTIF(M:M,"N1M*"),"0")

or

="N1M = "&TEXT(COUNTIF(M3:M5000,"N1M*"),"0")