Counting Unique Values In Excel – 5 Effective Ways

Method 5 – COUNTUNIQUE User Defined Function ExcelTrickExcelTrick-Easy,Smart&Effective!CountingUniqueValuesInExcel–5EffectiveWaysLet’sconsiderwehavealonglistofduplicatevaluesinarangeandourobjectiveistocountonlytheuniqueoccurrencesofeachvalue.Thoughthiscanbeaverycommonrequirementinmanyscenariosbutexceldoesn’thaveanysingleformulathatcandirectlyhelpustocountuniquevaluesinexcelinsidearange.Intoday’spost,wewillsee5differentwaysofcountinguniquevaluesinexcel.But,beforestartinglet’smakeourgoalclear.Bylookingattheaboveimage,wecanclearlyseethatwhatwemeanbycountinguniquevalueinexcel.TableofContentsMethod–1:UsingSumProductandCountIFFormula:Let’sSeeHowThisFormulaWorks:TheCatch:Method–2:UsingSUM,FREQUENCYANDMATCHArrayFormulaLet’sTryToUnderstandHowThisFormulaWorks:Method–3:UsingPivotTable(OnlyworksinExcel2013andabove)Method–4:UsingSUMandCOUNTIFfunctionLet’sSeeHowThisFormulaWorks:Method5–COUNTUNIQUEUserDefinedFunction:HowtoUsetheUDF:Method–1:UsingSumProductandCountIFFormula:ThesimplestandeasiestwaytocountdistinctvaluesinexcelistouseSumProductandCountIFformula.Followingisthegenericformulathatyoucanuse:=SUMPRODUCT(1/COUNTIF(data,data))‘data’–datarepresentstherangethatcontainsthevaluesLet’sSeeHowThisFormulaWorks:Thisformulaconsistsoftwofunctionscombinedtogether.Let’sfirstunderstandtheroleofinnerCountIFfunction.TheCountIFfunctionlooksinsidethedatarange(B2:B9)andcountsthenumberoftimeseachvalueappearsindatarange.Theresultisanarrayofnumbersandinourexampleitmightlooksomethinglikethis:{2;2;3;3;3;2;2;1}.Next,theresultsofCountIFformulaareusedasadivisorwith1asnumerator.ThismodifiestheresultofCountIFformulasuchthat;valuesthatonlyappearonceinthearraybecome1andthenumberscorrespondingtoduplicatevaluesbecomefractionscorrespondingtothemultiple.Thismeansthat,ifavalueappears4timesintherangethenitsvaluewillbecomeas1/4=0.25andvaluethatispresentonlyoncewillbecome1/1=1Finally,theSumProductformulaaddsupalltheelementsinthearrayandreturnsthefinalresult.ReadthesearticlesformoredetailsonCountIFFunctionorSumProductFunction.TheCatch:Thisformulawoksnicelyonacontinuoussetofvaluesinarange,howeverifyourvaluescontainemptycells,thenthisformulafails.Insuchcasestheformulathrowsa‘dividebyzero’error.Because,CountIFfunctiongeneratesa‘0’foreachblankcellandwhen1isdividedby0itreturnsadividebyzeroerror.Tofixthis,youcanusethefollowingvariantoftheaboveformulathatignorestheblankcells:=SUMPRODUCT(((data<>"")/COUNTIF(data,data&"")))‘data’–datarepresentstherangethatcontainsthevaluesMethod–2:UsingSUM,FREQUENCYANDMATCHArrayFormulaTheformulathatwediscussedaboveisgoodtobeusedforsmallranges.AstherangebecomesbiggertheSUMPRODUCTandCOUNTIFformulawillbecomeslowerandwilleventuallymakeyourspreadsheetsunresponsivewhilecountinguniquevaluesinsidearange.So,forlargerdatasets,youmaywanttoswitchtoaformulabasedontheFREQUENCYfunction.Thegenericformulaisasfollows:=SUM(IF(FREQUENCY(IF(data<>"",MATCH(data,data,0)),ROW(data)-ROW(firstcell)+1),1))Here,‘data’representstherangethatcontainsthevalues.And‘firstcell’representsthefirstcelloftherange.Note:Thisisanarrayformulaso,afterwritingtheformulapress‘Control-Shift-Enter’andtheformulawillgetsurroundedbycurlybracesasshownbelow.Let’sTryToUnderstandHowThisFormulaWorks:ThisformulausesFrequencyfunctiontocounttheuniquevalues,buttheproblemhereisthatFREQUENCYfunctionisonlydesignedtoworkwithnumbers.So,hereourfirstobjectiveistoconvertthevaluesintoasetofnumbers.Startingfrominside,theMATCHfunctioninthisformulagivesusthefirstoccurrenceorpositionnumberofeachitemthatappearsinthedatarange.Ifthereareanyvaluesthatareduplicate,thenMATCHwillreturnonlythepositionofthefirstoccurrenceofthatvalueinthedatarange.AftertheMATCHfunction,thereisanIFStatement.ThereasonIFfunctionisrequiredbecauseMATCHwillreturna#N/Aerrorforemptycells.So,weareexcludingtheemptycellswithdata<>"".TheresultingarraycontainsasetofnumberscombinedwithFalsefortheblankcells.So,inourcasetheresultingarraywouldbelike:{1;1;3;3;3;6;FALSE;6;9}ThisarrayisfedtotheFREQUENCYfunctionwhichreturnshowoftenvaluesoccurwithinthesetofdataandfinallytheouterIFfunctionsetseachuniquevalueto1andduplicatevaluetoFALSE.Andthefinalresultcomesouttobe4.Method–3:UsingPivotTable(OnlyworksinExcel2013andabove)Theintegrationofpowerpivotwithexcel(knownasDataModel),hasprovidedsomepowerfulfeaturestotheusers.Nowpivottablescanalsohelpyoutogetthedistinctcountsofuniquevaluesinexcel.Todothisfollowthebelowsteps:Navigatetothe‘Insert’optiononthetopribbonandclickthe‘PivotTable’option.Thiswillopena‘PivotTable’dialog,selectthedatarangeandcheckthecheckboxthatsays‘AddthisdatatoDataModel’andfinallyclick‘Ok’button.BuildthePivotTablebyplacingthecolumnthatcontainsdatarange‘Values’andinthe‘Rows’quadrantasshownbelow.Doingthiswillshowthetotalcountincludingtheduplicatevalues,howeverweonlyneedtogetthecountofdistinctvalues.So,rightclickovertheCountcolumnandselectthe‘ValueFieldSettings’option.Next,Inthe‘ValueFieldSettings’window,selectthe‘DistinctCount’optionandclick‘Ok’button.DoingthiswillfetchthedistinctcountsforthevaluesandpopulatetheminthePivotTable.Method–4:UsingSUMandCOUNTIFfunctionThisisagainanarrayformulatocountdistinctvaluesinarange.=SUM(IF(ISTEXT(range),1/COUNTIF(range,range),""))Here,‘range’representstherangethatcontainsthevalues.Note:Thisisanarrayformulaso,afterwritingtheformulapress‘Control-Shift-Enter’andtheformulawillgetsurroundedbycurlybracesasshownbelow.Let’sSeeHowThisFormulaWorks:Inthisformula,wehaveusedISTEXTfunction.ISTEXTfunctionreturnsatrueforallthevaluesthataretextandfalseforothervalues.Ifthevalueisatextvalue,thentheCOUNTIFfunctionexecutesandlooksinsidethedatarange(B2:B9)andcountsthenumberoftimesthateachvalueappearsindatarange.Afterthis,theresultofCountIFfunctionareusedasadivisorwith1asthenumerator(sameasinfirstmethod).Thismeansthat,ifavalueappears4timesintherangeitsvaluewillbecomeas1/4=0.25andwhenthevalueispresentonlyoncethenitbecomes1/1=1Finally,theSUMfunctioncomputesthesumofallthevaluesandreturnstheresult.Method5–COUNTUNIQUEUserDefinedFunction:Ifnoneoftheaboveoptionsworkforyou,thenyoucancreateyourownuserdefinedfunction(UDF)thatcancountuniquevaluesinexcelforyou.BelowisthecodetowriteyourownUDFthatdoesthesame:FunctionCOUNTUNIQUE(DataRangeAsRange,CountBlanksAsBoolean)AsIntegerDimCellContentAsVariantDimUniqueValuesAsNewCollectionApplication.VolatileOnErrorResumeNextForEachCellContentInDataRangeIfCountBlanks=TrueOrIsEmpty(CellContent)=FalseThenUniqueValues.AddCellContent,CStr(CellContent)EndIfNextCOUNTUNIQUE=UniqueValues.CountEndFunctionToembedthisfunctioninExcelusethefollowingsteps:PressAlt+F11keyontheExcel,thiswillopentheVBAwindow.OntheVBAWindow,rightclickoverthe‘MicrosoftExcelObjects’>‘Insert’>‘Module’.Clickingthe‘Module’willopenanewmoduleintheExcel.PastetheUDFcodeinthemodulewindowasshownbelow.Finally,saveyourspreadsheetandtheformulaisreadytouse.HowtoUsetheUDF:TousetheUDF,youcansimplytypetheUDFname‘CountUnique’likeanormalexcelfunction.=COUNTUNIQUE(data_range,count_blanks)‘data_range’–representstherangethatcontainsthevalues.‘count_blanks’–isabooleanparameterthatcanhavetwovaluestrueorfalse.Ifyousetthisparameterastruethenitwillcounttheblankrowsasunique.BysettingthisparametertofalsetheUDFwillexcludetheblankrows.So,thesewereallthemethodsthatcanhelpyoutocountuniquevalueinexcel.Doletusknowyourownmethodsortrickstodothesame.Subscribeandbeapartofour15,000+memberfamily!NowsubscribetoExcelTrickandgetafreecopyofourebook"200+ExcelShortcuts"(printableformat)tocatapultyourproductivity.YouMayAlsoLikeExcelSUMPRODUCTFunction–HowToUseExcelCOUNTIFFunction–HowtoUseFindandDeleteDuplicatesinExcel–TheUltimateGuideExcelMatchFunction–HowToUseTransposeFunctioninExcel–HowtoUseCalculatingWeightedAverageinExcelAboutAnkitKaulWell,IamAnkitKaul,thefounderofExcelTrick.Iamadie-hardfanofMicrosoftExcelandhavebeenworkingwithspreadsheetsforthepast10+years.Myonlyaimistoturnyouguysinto'ExcelGeeks'.Checkoutmoreaboutmehere.SearchHowTosExcelFunctionsExcelVBAHowtoFindCircularReferencesinExcelFlashFillinExcel-CompleteGuideHowtoEnableMacrosinExcelSumCellsbasedonBackgroundColorHowtoSpellCheckinExcelCountingUniqueValuesInExcelHowtoInsertaCheckboxinExcelExcelFunctionKeysandShortcutsHowtoInsertMultipleRowsinExcelUseanImageasaBackgroundinExcelHowtoFindExternalRefrencesinExcelHowtoConcatenateinExcelExcelIFStatement-HowtoUseExcelFILTERFunction-HowtoUseExcelVLOOKUPFunction-HowtoUseExcelHLOOKUPFunction-HowtoUseExcelROUNDFunction-CompleteGuideExcelSUMIFandSUMIFS-CompleteGuideExcelCOUNTIFFunction-HowtoUseExcelOFFSETFunction-HowtoUseExcelTEXTFunction-HowtoUseExcelMATCHFunction-HowtoUseExcelIFERRORFunction-HowtoUseVBAWaitandSleepFunctionsVBAOnErrorStatement–HandlingErrorsVBASubstring–HowtoSubstringinExcelVBAVBAVLOOKUP–WithExamplesVBAMsgBox-HowtoUseVBAFor,ForEach,DoWhile&DoUntilLoopsVBASplitFunction-HowtoUseVBADIRFunction-HowtoUseVBAIFFunction-HowtoUseVBAInStrandInStrRev-HowtoUseVBAREPLACEFunction-HowtoUseExceltrick.comisyourone-stopshoptolearnallexceltricks,formulas,how-tos&shortcuts.Thegoalofthissiteistomakeyouanexcelgeek.©Copyright2012- ExcelTrick ·AllRightsReserved|| SiteMapSubscribetoExcelTrickEnteryouremailaddresstosubscribetothisblogandreceiveweeklynotificationsofnewpostsbyemail.QuickLinks:AboutUs|TermsOfUse|PrivacyPolicy|CommentPolicy|DMCA|Contact