Thanks:  0
Likes:  0

# Thread: count text in cells, dont count duplicates

1. ## count text in cells, dont count duplicates

i need to count a column of text, but not count duplicates, tried this, but it doesnt work
=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

Eddie
John
Eddie
jack

answer should be 3 total names

2. ## Re: count text in cells, dont count duplicates

Try this:
Code:
```

=SUMPRODUCT(ISTEXT(A1:A100)*(A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))```
Is that something you can work with?

Edited to include this comment:
BTW....Your formula WILL count all unique items (text and numbers).
If the count of your sample data is 4 and not 3...Are there possibly trailing spaces on any of them?

3. ## Re: count text in cells, dont count duplicates

Does the formula return the #DIV/0! error value? If so, then it's due to an inherent bug...

Here are a couple of alternatives...

=SUM(IF(A1:A100<>"",1/COUNTIF(A1:A100,A1:A100)))

=SUM(IF(FREQUENCY(IF(A1:A100<>"",MATCH("~"&A1:A100,A1:A100&"",0)),ROW(A1:A100)-ROW(A1)+1),1))

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER. Also, the second formula is more efficient.

## User Tag List

#### Posting Permissions

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