Forum Discussion
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
peeblescd2020 =SUMPRODUCT(IFERROR(1/COUNTIF(S2:S1000,S2:S1000),0))
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.
- 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 formulaThank 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?
- 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
