count unique cell text values

[peeblescd2020]

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? 

[#2]

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

[#3]

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?

[#4]

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

[#5]

I presume you don't have 365 and access to UNIQUE...

=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.

[#6]

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

[#7]

@yydhcl

@yydhcl