COUNTIF data counting
 

I use the COUNTIF function to eliminate duplicate job applications.

Application serial number is in column A, each application ser# is 11 digits
Applicant SSN is in column B
Column C has formula =A1&"."&B1
Column D has formula =COUNTIF(A1:A15000,A1)

The problem that seems to have risen is that the COUNTIF function only looks
at the first 15 digits. What is happening now is we have people with similar
SSN's applying for the same job and they are being grouped together. Is there
a way to make COUNTIF look at the entire string of digits, or is there
another function that can be used?

Thanks for any help.

COUNTIF data counting
 

"joebogey" wrote in message
...
Column D has formula =COUNTIF(A1:A15000,A1)

I'm assuming you mean:

=COUNTIF(C$1:C$15000,C1)

Try instead:

=SUM(--(C1=C$1:C$15000))

entered as an array formula with <Ctrl <Shift <Enter.

--

Vasant

COUNTIF data counting
 

I tried that and it worked perfectly. I was just wondering if you could give
an explanation of exactly how this is working, such as what the "--" does to
the formula?

Sorry, once I implement this I'll probably have to explain how it works.

Thanks for your help, I really appreciate it.
Joe

"Vasant Nanavati" wrote:

I'm assuming you mean:

=COUNTIF(C$1:C$15000,C1)

Try instead:

=SUM(--(C1=C$1:C$15000))

entered as an array formula with <Ctrl <Shift <Enter.

--

Vasant

COUNTIF data counting
 

The "double minus" simply converts TRUE and FALSE values to 1 and 0, so the
SUM function adds up all the TRUEs in the array; that is, it counts up the
number of matches.

--

Vasant




"joebogey" wrote in message
...
I tried that and it worked perfectly. I was just wondering if you could
give
an explanation of exactly how this is working, such as what the "--" does
to
the formula?

Sorry, once I implement this I'll probably have to explain how it works.

Thanks for your help, I really appreciate it.
Joe

"Vasant Nanavati" wrote:

I'm assuming you mean:

=COUNTIF(C$1:C$15000,C1)

Try instead:

=SUM(--(C1=C$1:C$15000))

entered as an array formula with <Ctrl <Shift <Enter.

--

Vasant