Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF not counting properly | Excel Worksheet Functions | |||
Countif not counting | Excel Discussion (Misc queries) | |||
COUNTIF, only counting once if both cells contain the data | Excel Worksheet Functions | |||
COUNTIF not counting | Excel Discussion (Misc queries) | |||
Counting Data from Another Worksheet - Countif | Excel Worksheet Functions |