Highlighted
New Contributor

# count unique cell text values

Hi, I'm trying to create a formula which counts the unique number of country names in the second sheet of column S attached. I want the range to be the entire column since we're continuously adding data to this sheet. Cell S1 and all blank cells should be ignored. Can anyone help?

6 Replies
Highlighted

# Re: count unique cell text values

Hello, copy all the countries in column S and paste in column U2> Remove Duplicate values > In V2, execute the formula: =COUNTIFS(\$S\$2:\$S\$89,U2)

Copy down the formula
Highlighted

# Re: count unique cell text values

Thank you for your reply! I'm looking for a solution which won't require me to copy any values in column S. More data will be added to the column as time goes by, so the idea is to create a formula which will automatically update the unique text count (number of country names) in case a new country name happens to be added to the column. Is there a formula that would accomplish this?

Highlighted

# Re: count unique cell text values

Since you wanna count the number of unique countries, definitely, you must copy the data to a separate column and remove duplicates countries..

To automate your data, format the data in Excel Tables
Highlighted

# Re: count unique cell text values

=SUM(IF(FREQUENCY(IFNA(MATCH(\$S\$2:\$S\$1000,\$S\$2:\$S\$1000,0),0),IFNA(MATCH(\$S\$2:\$S\$1000,\$S\$2:\$S\$1000,0),0))>0,1))-1

Press Ctrl Shift Enter to calculate the formula as an array.

Highlighted

# Re: count unique cell text values

@peeblescd2020 =SUMPRODUCT(IFERROR(1/COUNTIF(S2:S1000,S2:S1000),0))

Highlighted