How to Count Unique and Distinct Values in Excel | Excelchat

You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM(IF(1/COUNTIF(data, ... Connectanytimetofree,instant,liveExperthelpbyinstallingtheChromeextension AddExcelchattoChrome QuickExcelHelp AVERAGEAVERAGEIFCELLREFERENCECOUNTCOUNTACOUNTIFSUMSUMIFVLOOKUP GetFREEstep-by-stepguidanceonyourquestionfromourExcelExperts. TRYNOW TRYNOW TRYNOW QuickExcelHelp AVERAGEAVERAGEIFCELLREFERENCECOUNTCOUNTACOUNTIFSUMSUMIFVLOOKUP GetinstantliveexperthelpwithExcelorGoogleSheets “MyExcelchatexperthelpedmeinlessthan20minutes,savingmewhatwouldhavebeen5 hoursofwork!” Postyourproblemandyou'llgetexperthelpinseconds Yourmessagemustbeatleast40characters Ourprofessionalexpertsareavailablenow.Yourprivacyisguaranteed. ConnecttoanExpert Allarticles COUNT HowtoCountUniqueandDistinctValuesinExcel HowtoCountUniqueandDistinctValuesinExcel Workingwithlargedatasets,weoftenrequirethecountofuniqueanddistinctvaluesinExcel.Thoughthismayberequiredinmanycases,Exceldoesnothaveanypre-definedformulatocountuniqueanddistinctvalues.Inthistutorial,youwillseeafewtechniquestocountuniqueanddistinctvaluesinExcel. HowtoCountUniqueandDistinctValuesinExcel Theuniquevaluesaretheonesthatappearonlyonceinthelist,withoutanyduplications.Thedistinctvaluesareallthedifferentvaluesinthelist.                        Inthisexample,youhavealistofnumbersrangingfrom1-6.Theuniquevaluesaretheonesthatappearonlyonceinthelist,withoutanyduplications.Thedistinctvaluesareallthedifferentvaluesinthelist.Thetablesbelowshowtheuniqueanddistinctvaluesinthislist. CountuniquevaluesinExcel YoucanusethecombinationoftheSUMandCOUNTIFfunctionstocountuniquevaluesinExcel.Thesyntaxforthiscombinedformulais=SUM(IF(1/COUNTIF(data,data)=1,1,0)).HeretheCOUNTIFformulacountsthenumberoftimeseachvalueintherangeappears. Theresultingarraylookslike{1;2;1;1;1;1}.Inthenextstep,youdivide1bytheresultingvalues.TheIFfunctionimplementsthelogicsuchthatifthevaluesappearonlyonceintherange,thisstepwillgenerate1,otherwiseitwillbeafractionvalue.TheSUMfunctionthensumsallthevaluesandreturnstheresult.Thisisanarrayformula,soyouhavetoassignitusingCtrl+Shift+Enter. ThefollowingexamplecontainsalistofautomobileproductswiththeirproductIDandNames.Youwillcounttheuniqueitemsinthisexample. {=SUM(IF(1/COUNTIF(A2:A11,A2:A11)=1,1,0))} Thiscountsthenumberofuniquevaluesinthelist.Itfollowsthesyntaxmentionedaboveandreturnsthecountforuniqueitems,whichis5. {=SUM(IF(ISNUMBER(A2:A11)*COUNTIF(A2:A11,A2:A11)=1,1,0))} Thiscountsthenumberofuniquenumericvaluesinthelist.TheonlydifferencewiththepreviousformulaishereisthenestedISNUMBERformulathatmakessurethatyouonlycountthenumericvalues.Returnsthenumber3,whichisthecountoftheuniquenumericvalues. {=SUM(IF(ISTEXT(A2:A10)*COUNTIF(A2:A11,A2:A11)=1,1,0))} Worksthesameasthepreviousformula,countstheuniquenumberoftextvaluesinstead.TheISTEXTfunctionisusedtomakesurethatonlythetextvaluesarecounted.Returnsthenumber2. CountDistinctValuesinExcel CountDistinctValuesusingaFilter YoucanextractthedistinctvaluesfromalistusingtheAdvancedFilterdialogboxandusetheROWSfunctiontocounttheuniquevalues.Tocountthedistinctvaluesfromthepreviousexample: SelecttherangeofcellsA1:A11. GotoData>Sort&Filter>Advanced. IntheAdvancedFilterdialogbox,clickCopytoanotherlocation. SetbothListRangeandCriteriaRangeto$A$1:$A$11. SetCopytoto$F$2. KeeptheUniqueRecordsOnlyboxchecked.ClickOK. ColumnFwillnowcontainthedistinctvalues. SelectH6. Entertheformula=ROWS(F3:F9).ClickEnter. Thiswillshowthecountofdistinctvalues,7. CountDistinctValuesusingFormulas YoucanusethecombinationoftheIF,MATCH,LENandFREQUENCYfunctionswiththeSUMfunctiontocountdistinctvalues. {=SUM(IF(A2:A11<>"",1/COUNTIF(A2:A11,A2:A11),0))} CountsthenumberofdistinctvaluesbetweencellsA2toA11.Liketheuniquecount,heretheCOUNTIFfunctionreturnsacountforeachindividualvaluewhichisthenusedasadenominatortodivide1.Thereturningvaluesarethensummediftheyarenot0.Thisgivesyouacountforthedistinctvalues,regardlessoftheirtypes. =SUM(IF(FREQUENCY(MATCH(A2:A11,A2:A11,0),MATCH(A2:A11,A2:A11,0))>0,1)) Doesthesamethingasthepreviousformula.TheonlydifferencesbeingtheuseoftheFREQUENCYandMATCHfunctions.Thefrequencyfunctionreturnsthenumberofoccurrencesforavalueforthefirstoccurrence.Forthenextoccurrenceofthatvalue,itreturns0.TheMATCHfunctionisusedtoreturnthepositionofatextvalueintherange.ThesearereturnedasthenusedasanargumenttotheFREQUENCYfunctionwhichgivesusacountofthetotalnumberofdistinctvalues. =SUM(IF(FREQUENCY(A2:A11,A2:A11)>0,1)) Countsthenumberofdistinctnumericvalues.AstheFREQUENCYfunctionignorestextandblanks,itreturns5,whichisthenumberofnumericvalues. {=SUM(IF(ISTEXT(A2:A11),1/COUNTIF(A2:A11,A2:A11),""))} Returnsthecountofdistincttextvaluesinarange.Likethefirstexample,thiscountsthedistinctvalues,buttheISTEXTfunctionmakessureonlythetextvaluesaretakenintocount. CountDistinctValuesusingaPivotTable YoucanalsocountdistinctvaluesinExcelusingapivottable.Tofindthedistinctcountofthebikenamesfromthepreviousexample: Tocountthedistinctitemsusingapivottable: SelectcellsA1:B11.GotoInsert>PivotTable. Inthedialogboxthatpopsup,checkNewWorksheetandAddthistotheDataModel. DragtheProductIDfieldtoRows,NamesfieldtoValuesinthePivotTableFields. Right-clickonanyvalueincolumnB.GotoValueFieldSettings.IntheSummarizeValuesBytab,gotoSummarizeValuefieldbyandsetittoDistinctCount. Thiswillshowthedistinctcount7incellB11. StillneedsomehelpwithExcelformattingorhaveotherquestionsaboutExcel?Connectwithalive Excelexpert hereforsome1on1help.Yourfirstsessionisalwaysfree.  Didthispostnotansweryourquestion?Geta solutionfrom connecting withtheexpert. AnotherblogreaderaskedthisquestiontodayonExcelchat: IhaveaproblemregardingtoCountuniquevaluesinacolumn AnExcelchatExpertsolvedthisproblemin15mins! largedatasetin19columnsandneedtofinduniquetextvaluesthencountthem. AnExcelchatExpertsolvedthisproblemin29mins! Iwanttocountuniquevaluesperparticipantofastudy. AnExcelchatExpertsolvedthisproblemin17mins! Postyourquestionto getapersonalizedsolution LeaveaComment Thiscommentformisunderantispamprotection Thiscommentformisunderantispamprotection SubscribetoExcelchat.co GetupdatesonhelpfulExceltopics Didthispostnotansweryourquestion?Getasolutionfromconnectingwiththeexpert AnotherblogreaderaskedthisquestiontodayonExcelchat: IhaveaproblemregardingtoCountuniquevaluesinacolumn AnExcelchatExpertsolvedthisproblemin15mins! largedatasetin19columnsandneedtofinduniquetextvaluesthencountthem. AnExcelchatExpertsolvedthisproblemin29mins! Iwanttocountuniquevaluesperparticipantofastudy. AnExcelchatExpertsolvedthisproblemin17mins! Postyourproblemandyou'llgetexperthelpinseconds Yourmessagemustbeatleast40characters Ourprofessionalexpertsareavailablenow.Yourprivacyisguaranteed. Tryforfree Trustedbypeoplewhoworkat Connectanytimetofree,instant,liveExperthelpbyinstallingtheChromeextension AddExcelchattoChrome Privacy&Cookies:Thissiteusescookies.Bycontinuingtousethiswebsite,youagreetotheiruse. Tofindoutmore,seehere:CookiePolicy Ok,GotIt! wpDiscuz Gotit!YourquestionwillbeansweredbyanExcelchatExpert.Hangtightfor30secswhilewe redirectyou. Savetimebyaskinginstead!Mostquestionssolvedandansweredin10minutes. Postyourproblemandyou’llgetexperthelpinseconds. Yourmessagemustbeatleast40characters Freetrialendsin00days12hours Try forfree