When using: =COUNTIF(A2:A8628,A2) and select all €˜1s from the Filter to
obtain the Unique records my count on these records was 8193.

In looking for a more €œautomated€� way of counting unique records I found the following formula: =SUM(N(FREQUENCY(IF(J$2:J$50000=L5,A$2:A$50000),A$ 2:A$50000)0))
'A' being my Unique record Range
'J' Criteria
'L' Specified item to count from Unique record

But my unique records now yielded a total of 8487 (??)
Which formula is extracting the wrong total?
Further, having to use €˜Ctrl/Shift/Enter for each criteria range in a pain. Is the no way around this, we need to copy & past data daily and require formulas to update automatically.

Any advice?

try =sumproduct(1/countif(A$2:A$8628,A2:A8628))
to get the number of unique values
also you can use advanced filter unique to get a list of the unique values.

"EricB" wrote:

When using: =COUNTIF(A2:A8628,A2) and select all €˜1s from the Filter to
obtain the Unique records my count on these records was 8193.

In looking for a more €œautomated€� way of counting unique records I found the following formula: =SUM(N(FREQUENCY(IF(J$2:J$50000=L5,A$2:A$50000),A$ 2:A$50000)0))
'A' being my Unique record Range
'J' Criteria
'L' Specified item to count from Unique record

But my unique records now yielded a total of 8487 (??)
Which formula is extracting the wrong total?
Further, having to use €˜Ctrl/Shift/Enter for each criteria range in a pain. Is the no way around this, we need to copy & past data daily and require formulas to update automatically.

Any advice?

Formula is killing EXCEL on 25.000+ records. Futhermore, I am trying to
extract data for certain 'sales' people, the formula does not allow me to
define what I want to count. I need to input a 'Sales' persons name and get
the unique records for the individual.

Any other advice?

"bj" wrote:

try =sumproduct(1/countif(A$2:A$8628,A2:A8628))
to get the number of unique values
also you can use advanced filter unique to get a list of the unique values.

"EricB" wrote:

When using: =COUNTIF(A2:A8628,A2) and select all €˜1s from the Filter to
obtain the Unique records my count on these records was 8193.

In looking for a more €œautomated€� way of counting unique records I found the following formula: =SUM(N(FREQUENCY(IF(J$2:J$50000=L5,A$2:A$50000),A$ 2:A$50000)0))
'A' being my Unique record Range
'J' Criteria
'L' Specified item to count from Unique record

But my unique records now yielded a total of 8487 (??)
Which formula is extracting the wrong total?
Further, having to use €˜Ctrl/Shift/Enter for each criteria range in a pain. Is the no way around this, we need to copy & past data daily and require formulas to update automatically.

Any advice?

This may an issue of the definition of unique records

do you mean you want to find the number of records assigned to an individual
with an entry in another column

try
=Sumproduct(--(Name range =Name),--(Item range = item))

I normally think of unique records as not having a duplicate

If you have another meaning please let me know.

"EricB" wrote:

Formula is killing EXCEL on 25.000+ records. Futhermore, I am trying to
extract data for certain 'sales' people, the formula does not allow me to
define what I want to count. I need to input a 'Sales' persons name and get
the unique records for the individual.

Any other advice?

"bj" wrote:

try =sumproduct(1/countif(A$2:A$8628,A2:A8628))
to get the number of unique values
also you can use advanced filter unique to get a list of the unique values.

"EricB" wrote:

When using: =COUNTIF(A2:A8628,A2) and select all €˜1s from the Filter to
obtain the Unique records my count on these records was 8193.

In looking for a more €œautomated€� way of counting unique records I found the following formula: =SUM(N(FREQUENCY(IF(J$2:J$50000=L5,A$2:A$50000),A$ 2:A$50000)0))
'A' being my Unique record Range
'J' Criteria
'L' Specified item to count from Unique record

But my unique records now yielded a total of 8487 (??)
Which formula is extracting the wrong total?
Further, having to use €˜Ctrl/Shift/Enter for each criteria range in a pain. Is the no way around this, we need to copy & past data daily and require formulas to update automatically.

Any advice?

I seem to be having difficulty in explaining what I need.

In Column €˜A I have client reference numbers €“ some duplicated
In Column €˜B I have my 5 €˜sales people that worked on the above clients, these are identified by their initials, i.e. EB, EF, ME
I need to determine how many individual clients we worked, i.e. wanting to extract the unique records from €˜A.
In certain cases EB/EF & ME worked the same client, which returns 3 records/hits. I only need 1 (one) unique record returned.
Hence, a formula is required where multiple criterias can be used.

Hope the explanation helps; I am starting to confuse myself.

Regards

EricB


"bj" wrote:

This may an issue of the definition of unique records

do you mean you want to find the number of records assigned to an individual
with an entry in another column

try
=Sumproduct(--(Name range =Name),--(Item range = item))

I normally think of unique records as not having a duplicate

If you have another meaning please let me know.

"EricB" wrote:

Formula is killing EXCEL on 25.000+ records. Futhermore, I am trying to
extract data for certain 'sales' people, the formula does not allow me to
define what I want to count. I need to input a 'Sales' persons name and get
the unique records for the individual.

Any other advice?

"bj" wrote:

try =sumproduct(1/countif(A$2:A$8628,A2:A8628))
to get the number of unique values
also you can use advanced filter unique to get a list of the unique values.

"EricB" wrote:

When using: =COUNTIF(A2:A8628,A2) and select all €˜1s from the Filter to
obtain the Unique records my count on these records was 8193.

In looking for a more €œautomated€� way of counting unique records I found the following formula: =SUM(N(FREQUENCY(IF(J$2:J$50000=L5,A$2:A$50000),A$ 2:A$50000)0))
'A' being my Unique record Range
'J' Criteria
'L' Specified item to count from Unique record

But my unique records now yielded a total of 8487 (??)
Which formula is extracting the wrong total?
Further, having to use €˜Ctrl/Shift/Enter for each criteria range in a pain. Is the no way around this, we need to copy & past data daily and require formulas to update automatically.

Any advice?

It spunds like you really need at least two different equaitons
one the number of unique clients and
two the number of clients for each sales person

For unique client numbers
going back to your original posting did you use in your helper column (B?)
=COUNTIF(A2:A8628,A2) or actually
=COUNTIF(A$2:A$8628,A2)
If you used the first, the number should have been correct
if you used the second
a correct number could be achieved by
=countif(B:B,1)+Countif(B:B,2/2+countif(B:B,3)/3 ... up to the max number of
duplicates

for he issue raised in the second posting where you want to know the number
of clients each salesman has
I assume Column J
=countif(J:J,l5) etc
should give you the number of times the Saleman in L5 etc is listed

was the value of 8487 from the sum of the count of each salespereson?
if this is the case there might be some names not entered correctly

"EricB" wrote:

I seem to be having difficulty in explaining what I need.

In Column €˜A I have client reference numbers €“ some duplicated
In Column €˜B I have my 5 €˜sales people that worked on the above clients, these are identified by their initials, i.e. EB, EF, ME
I need to determine how many individual clients we worked, i.e. wanting to extract the unique records from €˜A.
In certain cases EB/EF & ME worked the same client, which returns 3 records/hits. I only need 1 (one) unique record returned.
Hence, a formula is required where multiple criterias can be used.

Hope the explanation helps; I am starting to confuse myself.

Regards

EricB


"bj" wrote:

This may an issue of the definition of unique records

do you mean you want to find the number of records assigned to an individual
with an entry in another column

try
=Sumproduct(--(Name range =Name),--(Item range = item))

I normally think of unique records as not having a duplicate

If you have another meaning please let me know.

"EricB" wrote:

Formula is killing EXCEL on 25.000+ records. Futhermore, I am trying to
extract data for certain 'sales' people, the formula does not allow me to
define what I want to count. I need to input a 'Sales' persons name and get
the unique records for the individual.

Any other advice?

"bj" wrote:

try =sumproduct(1/countif(A$2:A$8628,A2:A8628))
to get the number of unique values
also you can use advanced filter unique to get a list of the unique values.

"EricB" wrote:

When using: =COUNTIF(A2:A8628,A2) and select all €˜1s from the Filter to
obtain the Unique records my count on these records was 8193.

In looking for a more €œautomated€� way of counting unique records I found the following formula: =SUM(N(FREQUENCY(IF(J$2:J$50000=L5,A$2:A$50000),A$ 2:A$50000)0))
'A' being my Unique record Range
'J' Criteria
'L' Specified item to count from Unique record

But my unique records now yielded a total of 8487 (??)
Which formula is extracting the wrong total?
Further, having to use €˜Ctrl/Shift/Enter for each criteria range in a pain. Is the no way around this, we need to copy & past data daily and require formulas to update automatically.

Any advice?