# Thread: Count Duplicates in a List (2000)

1. ## Count Duplicates in a List (2000)

I need to count the entries in a long cell range, but I don't want to count duplicates. Can anyone help with a formula to do this?

2. ## Re: Count Duplicates in a List (2000)

Check out Chip Pearson's site for some techniques

Steve

3. ## Re: Count Duplicates in a List (2000)

Thanks, I tried the formula below but it returned a #VALUE for the result. The entries in the cells are a combination of letters and number i.e. HI458a

=SUM(IF(FREQUENCY(IF(LEN('H off'!J70:J92)>0,MATCH('H off'!J70:J92,'H off'!J70:J92,0),""),IF(LEN('H off'!J70:J92)>0,MATCH('H off'!J70:J92,'H off'!J70:J92,0),""))>0,1))

4. ## Re: Count Duplicates in a List (2000)

Hi stanlafayette,

I think your mentioned formula is a array formula, must enter with Shift+Ctrl+Enter

Or you can try the below count unique formula :

=SUMPRODUCT(('H off'!J70:J92<>"")/COUNTIF('H off'!J70:J92,'H off'!J70:J92&""))

Regards

5. ## Re: Count Duplicates in a List (2000)

This formula worked thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•