count unique cell text values

%3CLINGO-SUB%20id%3D%22lingo-sub-1215850%22%20slang%3D%22en-US%22%3Ecount%20unique%20cell%20text%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1215850%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I'm%20trying%20to%20create%20a%20formula%20which%20counts%20the%20unique%20number%20of%20country%20names%20in%20the%20second%20sheet%20of%20column%20S%20attached.%20I%20want%20the%20range%20to%20be%20the%20entire%20column%20since%20we're%20continuously%20adding%20data%20to%20this%20sheet.%20Cell%20S1%20and%20all%20blank%20cells%20should%20be%20ignored.%20Can%20anyone%20help%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1215850%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1215875%22%20slang%3D%22en-US%22%3ERe%3A%20count%20unique%20cell%20text%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1215875%22%20slang%3D%22en-US%22%3EHello%2C%20copy%20all%20the%20countries%20in%20column%20S%20and%20paste%20in%20column%20U2%26gt%3B%20Remove%20Duplicate%20values%20%26gt%3B%20In%20V2%2C%20execute%20the%20formula%3A%20%3DCOUNTIFS(%24S%242%3A%24S%2489%2CU2)%3CBR%20%2F%3E%3CBR%20%2F%3ECopy%20down%20the%20formula%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1215882%22%20slang%3D%22en-US%22%3ERe%3A%20count%20unique%20cell%20text%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1215882%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20reply!%20I'm%20looking%20for%20a%20solution%20which%20won't%20require%20me%20to%20copy%20any%20values%20in%20column%20S.%20More%20data%20will%20be%20added%20to%20the%20column%20as%20time%20goes%20by%2C%20so%20the%20idea%20is%20to%20create%20a%20formula%20which%20will%20automatically%20update%20the%20unique%20text%20count%20(number%20of%20country%20names)%20in%20case%20a%20new%20country%20name%20happens%20to%20be%20added%20to%20the%20column.%20Is%20there%20a%20formula%20that%20would%20accomplish%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1215884%22%20slang%3D%22en-US%22%3ERe%3A%20count%20unique%20cell%20text%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1215884%22%20slang%3D%22en-US%22%3ESince%20you%20wanna%20count%20the%20number%20of%20unique%20countries%2C%20definitely%2C%20you%20must%20copy%20the%20data%20to%20a%20separate%20column%20and%20remove%20duplicates%20countries..%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20automate%20your%20data%2C%20format%20the%20data%20in%20Excel%20Tables%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1215918%22%20slang%3D%22en-US%22%3ERe%3A%20count%20unique%20cell%20text%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1215918%22%20slang%3D%22en-US%22%3E%3CP%3EI%20presume%20you%20don't%20have%20365%20and%20access%20to%20UNIQUE...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(IF(FREQUENCY(IFNA(MATCH(%24S%242%3A%24S%241000%2C%24S%242%3A%24S%241000%2C0)%2C0)%2CIFNA(MATCH(%24S%242%3A%24S%241000%2C%24S%242%3A%24S%241000%2C0)%2C0))%26gt%3B0%2C1))-1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPress%20Ctrl%20Shift%20Enter%20to%20calculate%20the%20formula%20as%20an%20array.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1215919%22%20slang%3D%22en-US%22%3ERe%3A%20count%20unique%20cell%20text%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1215919%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F576651%22%20target%3D%22_blank%22%3E%40peeblescd2020%3C%2FA%3E%26nbsp%3B%3DSUMPRODUCT(IFERROR(1%2FCOUNTIF(S2%3AS1000%2CS2%3AS1000)%2C0))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1215927%22%20slang%3D%22en-US%22%3ERe%3A%20count%20unique%20cell%20text%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1215927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F576689%22%20target%3D%22_blank%22%3E%40yydhcl%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F576689%22%20target%3D%22_blank%22%3E%40yydhcl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

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

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.

Highlighted

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

Highlighted