Counting unique text

[Kcbundy]

I’m part of a small employee engagement committee at my place of work, we decided to play scattergories over email. Instead of counting each individual word, I was curious if there was a way to assign the word a numerical value of 1 while omitting the duplicate words. I know how to conditional format to highlight the cell to show the duplicate values, but I don’t know how to get excel to count words-especially omitting the duplicate values.

 

[rpaulson]

Count unique values in a range with COUNTIF

To count the number of unique values in a range of cells, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In the example shown, the formula in F6 is: =SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14)) In Dynamic Excel, you can use a simpler and faster formula based on UNIQUE.

exceljet.net

 

[Jeffrey Mahoney]

I know this doesn't help you without Office 365, but I'll post it anyway
=COUNTA(UNIQUE(B:B))

 

[Kcbundy]

I know this doesn't help you without Office 365, but I'll post it anyway
=COUNTA(UNIQUE(B:B))

I’m using 365! This is perfect. My only complaint is, it starts with 1 instead of 0. For instance, it’s already counting values before I enter in words. Minor inconvenience.

 

[Rick Rothstein]

I’m using 365! This is perfect. My only complaint is, it starts with 1 instead of 0. For instance, it’s already counting values before I enter in words. Minor inconvenience.

That is odd that it is counting 1 more than the number of entries in the column, but easily fixed by just subtracting 1 from the formula...

=COUNTA(UNIQUE(B:B))-1

 

[Peter_SSs]

That is odd ..

Not really since Excel treats blanks as 0 so when col B is empty UNIQUE(B:B) returns 0 and when there are entries in col B unless all 1 million+ rows are filled with words there will still be a 0 returned as one of the unique values

 

[Rick Rothstein]

Not really since Excel treats blanks as 0 so when col B is empty UNIQUE(B:B) returns 0 and when there are entries in col B unless all 1 million+ rows are filled with words there will still be a 0 returned as one of the unique values

In that case, in my opinion, it might have been useful if the designers of the UNIQUE function would have included an IgnoreBlanks optional argument (defaulted to TRUE).

 

[Peter_SSs]

In that case, in my opinion, it might have been useful if the designers of the UNIQUE function would have included an IgnoreBlanks optional argument

I agree, but it wouldn't actually help here because the function would still have to return something even if the column is empty and COUNTA would still count 1 for whatever was returned. In that circumstance the count would be incorrect for an empty column but correct for non-empty which might be worse.