Count Duplicates in a List (2000)

[stanlafayette]

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?

[sdckapr]

Check out Chip Pearson's site for some techniques

Steve

[stanlafayette]

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))

[bosco_yip]

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

[stanlafayette]

This formula worked thanks.