Count Unique Values in Excel (In Easy Steps)

Count Unique Values. This example shows you how to create an array formula that counts unique values. 1. We use the COUNTIF function. For example, to count ... CountUniqueValues   Thisexampleshowsyouhowtocreateanarrayformulathatcountsuniquevalues. 1.WeusetheCOUNTIFfunction.Forexample,tocountthenumberof5's,usethefollowingfunction. 2.Tocounttheuniquevalues(don'tbeoverwhelmed),weaddtheSUMfunction,1/,andreplace5withA1:A6. 3.FinishbypressingCTRL+SHIFT+ENTER. Note:Theformulabarindicatesthatthisisanarrayformulabyenclosingitincurlybraces{}.Donottypetheseyourself.Theywilldisappearwhenyouedittheformula. Explanation:Therange(arrayconstant)createdbytheCOUNTIFfunctionisstoredinExcel'smemory,notinarange.Thearrayconstantlooksasfollows: {3;1;1;1;3;3}-(three7's,onesun,onemoon,one5,three7's,three7's) Thisreducesto: {1/3;1/1;1/1;1/1;1/3;1/3} ThisarrayconstantisusedasanargumentfortheSUMfunction,givingaresultof1/3+1+1+1+1/3+1/3=4. 4.Thearrayformulabelowcountsthenumberofvaluesthatoccurexactlyonce. Explanation:theIFfunctionconvertsthearrayconstant{3;1;1;1;3;3}to{0;1;1;1;0;0}.ThisarrayconstantisusedasanargumentfortheSUMfunction,givingaresultof3. 5.IfyouhaveExcel365,simplyusethemagicUNIQUEfunctiontoextractuniquevalues. Note:thisdynamicarrayfunction,enteredintocellC3,fillsmultiplecells.Wow!ThisbehaviorinExcel365iscalledspilling. 6.Next,addtheCOUNTAfunctiontocountuniquevaluesinExcel365. 7.Theformulabelowcountsthenumberofvaluesthatoccurexactlyonce. Note:theUNIQUEfunctionhas2optionalarguments.Thedefaultvalueof0(secondargument)tellstheUNIQUEfunctiontoextractvaluesfromaverticalarray.Thevalue1(thirdargument)tellstheUNIQUEfunctiontoextractvaluesthatoccurexactlyonce. 3/11Completed!Learnmuchmoreaboutarrayformulas> NextChapter:Sort Chapter ArrayFormulas Learnmore,it'seasy CountErrors CountUniqueValues CountwithOrCriteria SumEveryNthRow SumLargestNumbers SumRangewithErrors SumwithOrCriteria MostFrequentlyOccurringWord SystemofLinearEquations LETfunction DownloadExcelFile count-unique-values.xlsx NextChapter Sort FollowExcelEasy BecomeanExcelPro 300Examples   CountUniqueValues•©2010-2021 ExcelisAwesome,we'llshowyou:Introduction•Basics•Functions•DataAnalysis•VBA