count distinct values in spreadsheet - Stack Overflow

This can be accompished using pivot tables. ... Use the unique formula to get all the distinct values. Then use countif to get the count of each ... Home Public Questions Tags Users Collectives ExploreCollectives FindaJob Jobs Companies Teams StackOverflowforTeams –Collaborateandshareknowledgewithaprivategroup. CreateafreeTeam WhatisTeams? Teams CreatefreeTeam CollectivesonStackOverflow Findcentralized,trustedcontentandcollaboratearoundthetechnologiesyouusemost. Learnmore Teams Q&Aforwork Connectandshareknowledgewithinasinglelocationthatisstructuredandeasytosearch. Learnmore countdistinctvaluesinspreadsheet AskQuestion Asked 8years,11monthsago Active 8monthsago Viewed 154ktimes 92 15 IhaveaGooglespreadsheetwithacolumnthatlookslikethis: City ---- London Paris London Berlin Rome Paris Iwanttocounttheappearancesofeachdistinctcity(soIneedthecitynameandthenumberofappearances). City|Count -------+------ London|2 Paris|2 Berlin|1 Rome|1 HowdoIdothat? countgoogle-sheetsdistinct Share Improvethisquestion Follow editedDec11'18at5:25 Alex.K. 3,6901414goldbadges4141silverbadges4949bronzebadges askedJan17'13at14:07 MariusBancilaMariusBancila 15.5k88goldbadges4545silverbadges8484bronzebadges 1 DeletedmyanswerduetoIwasn'tabletocreateaworkingexample.IrecommendhavingalookatacombinationwithIF()andCOUNTIF() – sascha Jan25'13at12:35 Addacomment  |  7Answers 7 Active Oldest Votes 112 +50 LinktoWorkingExamples Solution0 Thiscanbeaccompishedusingpivottables. Solution1 Usetheuniqueformulatogetallthedistinctvalues.Thenusecountiftogetthecountofeachvalue.Seetheworkingexamplelinkatthetoptoseeexactlyhowthisisimplemented. UniqueValuesCount =UNIQUE(A3:A8)=COUNTIF(A3:A8;B3) =COUNTIF(A3:A8;B4) ... Solution2 Ifyousetupyourdataassuch: City ---- London1 Paris1 London1 Berlin1 Rome1 Paris1 Thenthefollowingwillproducethedesiredresult. =sort(transpose(query(A3:B8,"Selectsum(B)pivot(A)")),2,FALSE) I'msurethereisawaytogetridofthesecondcolumnsinceallvalueswillbe1.Notanidealsolutioninmyopinion. viahttp://googledocsforlife.blogspot.com/2011/12/counting-unique-values-of-data-set.html OtherPossiblyHelpfulLinks http://productforums.google.com/forum/#!topic/docs/a5qFC4pFZJ8 Share Improvethisanswer Follow editedJan20'20at13:11 AlanFranzoni 2,86811goldbadge2121silverbadges2929bronzebadges answeredJan25'13at14:09 JSuarJSuar 20.7k44goldbadges4141silverbadges8181bronzebadges 7 Thatworked.Thanks.FunnythatIalreadyappliedthesecondsolutionwithanotherset,butdidn'tthinkofithere. – MariusBancila Jan26'13at14:06 forsolution2:youcouldleavecolumnBemptyandthenuse"count"insteadof"sum":=sort(transpose(query(A3:B8,"Selectcount(B)pivot(A)")),2,FALSE) – wivku Dec21'14at11:36 ...buthowdoIcreateapivottable?whilethisanswerlooksgreatanddetailed,dnlbrky'smorestraightforwardansweristheonewhichsavedme.here,Ididn'tevennoticethe-moresimple-alternative1&2solutionsgiven.Toobad:( – Balmipour Mar25'16at1:22 1 @BalmipourData>PivotTable>thenjustcopythescreenshot. – JoshuaDance Feb23'17at0:45 Justcamebackhereafteryourcomment.Ididn'tguessthefrenchfor"Pivottables",was"tableauxcroisésdynamiques".GuessIcouldhavesearchedalittlebetterthatday,butatleast,knowingthemenuoptionwasjustin"Data"allowedmetofindit. – Balmipour Feb23'17at18:46  |  Show2morecomments 27 Youcanusethequeryfunction,soifyourdatawereincolAwherethefirstrowwasthecolumntitle... =query(A2:A,"selectA,count(A)whereA!=''groupbyAorderbycount(A)desclabelA'City'",0) yields Citycount London2 Paris2 Berlin1 Rome1 LinktoworkingGoogleSheet. https://docs.google.com/spreadsheets/d/1N5xw8-YP2GEPYOaRkX8iRA6DoeRXI86OkfuYxwXUCbc/edit#gid=0 Share Improvethisanswer Follow answeredJun6'17at23:47 MikeLatchMikeLatch 27133silverbadges22bronzebadges 3 Thistechniqueworkedwellandwaseasiertoapplyinmyparticularcase.However,oddlythequeryseemedtobreak(i.e.,itwouldnotoutputanything)iftherewasmorethanonedistinctnumericalvalueinthecolumnbeingcounted.ItworkedwhenIconvertedthenumericalvaluestotheirstringequivalentsthough. – MattV. Jul4'17at20:27 @MattV.Itriedaddingdistinctnumericalvaluesintothecolumnbeingcountedandthequeryworkedjustfine.docs.google.com/spreadsheets/d/… – MikeLatch Jul12'17at22:55 Tolabelothercolumns,repeattheirdefinitionexactly.Inthisexample:labelA'City',count(A)'Total' – BryanRoach Jan11'20at19:43 Addacomment  |  21 =iferror(counta(unique(A1:A100)))countsnumberofuniquecellsfromA1toA100 Share Improvethisanswer Follow answeredSep1'13at20:03 d-_-bd-_-b 19.9k3434goldbadges126126silverbadges213213bronzebadges 2 2 canyouexplainthefunctionofiferror()inthiscase?counta(unique(A1:A100))gavemetheresultIwaslookingfor. – Stew Oct31'14at17:48 10 OPwasnotaskingforacountofuniquevaluesbutifyoudowantthatthenyoucouldsimplifyitbyusing=COUNTUNIQUE(A:A) – wivku Dec21'14at10:30 Addacomment  |  11 Notexactlywhattheuserasked,butaneasywaytojustcountuniquevalues: Googleintroducedanewfunctiontocountuniquevaluesinjustonestep,andyoucanusethisasaninputforotherformulas: =COUNTUNIQUE(A1:B10) Share Improvethisanswer Follow editedFeb24'17at4:03 answeredJun25'16at9:56 RudolfRealRudolfReal 1,8182323silverbadges2727bronzebadges 5 1 Thiswillreturnjust1number.Thecountoftheuniquevalues.However,theOPaskedhowtocounteachuniquevalue. – JoshuaDance Feb23'17at0:34 3 @JoshuaDanceYou'reright,buttheop'spostiscalled"countdistinctvaluesinspreadsheet".Thislookslikealegitanswertothisprecisequestiontome.Justnotethatitdoesn'tcount"emptycell"asavalue – Balmipour Feb23'17at18:37 1 Ifyouguysarefinewith,Iwillleaveitfornow.UnlesstoomanyusersfindthisabadanswersoIwilldeleteit.@Balmipour – RudolfReal Feb24'17at4:02 1 ThisisexactlywhatIneeded. – FellyTone84 Apr19'17at16:41 IfyoucamefromGoogle,thisisthecorrectanswer – Matt Jan25at19:01 Addacomment  |  7 Thisworksifyoujustwantthecountofuniquevaluesine.g.thefollowingrange =counta(unique(B4:B21)) Share Improvethisanswer Follow editedApr10'19at3:55 xskxzr 11.6k1212goldbadges3636silverbadges7373bronzebadges answeredApr10'19at3:35 PaulPaul 7111silverbadge11bronzebadge Addacomment  |  0 ThisissimilartoSolution1from@JSuar... AssumeyouroriginalcitydataisanamedrangecalleddataCity.Inanewsheet,enterthefollowing: A|B ---------------------------------------------------------- 1|=UNIQUE(dataCity)|Count 2||=DCOUNTA(dataCity,"City",{"City";$A2}) 3||[copydowntheformulaabove] 4||... 5||... Share Improvethisanswer Follow answeredOct13'14at19:33 dnlbrkydnlbrky 8,42222goldbadges4848silverbadges6161bronzebadges Addacomment  |  -5 =UNIQUE({filter(Core!L8:L27,isblank(Core!L8:L27)=false),query(ArrayFormula(countif(Core!L8:L27,Core!L8:L27)),"selectCol1whereCol1<>0")}) Core!L8:L27=list Share Improvethisanswer Follow answeredNov15'18at8:10 user3626588user3626588 111bronzebadge 0 Addacomment  |  YourAnswer ThanksforcontributingananswertoStackOverflow!Pleasebesuretoanswerthequestion.Providedetailsandshareyourresearch!Butavoid…Askingforhelp,clarification,orrespondingtootheranswers.Makingstatementsbasedonopinion;backthemupwithreferencesorpersonalexperience.Tolearnmore,seeourtipsonwritinggreatanswers. Draftsaved Draftdiscarded Signuporlogin SignupusingGoogle SignupusingFacebook SignupusingEmailandPassword Submit Postasaguest Name Email Required,butnevershown PostYourAnswer Discard Byclicking“PostYourAnswer”,youagreetoourtermsofservice,privacypolicyandcookiepolicy Nottheansweryou'relookingfor?Browseotherquestionstaggedcountgoogle-sheetsdistinctoraskyourownquestion. TheOverflowBlog Smashingbugstosetaworldrecord:AWSBugBust Podcast399:ZerotoMVPwithoutprovisioningadatabase FeaturedonMeta Reducingtheweightofourfooter NewresponsiveActivitypage Communityinputneeded:Therulesforcollectivesarticles A/BtestingontheAskpage Linked 15 Countuniqueingroupby-GoogleQuery 0 HowtoCOUNTUNIQUEacrossmultiplesheetsinsingleworkbook 1 Countinguniquevaluesin1columnbasedon5criteria? 0 DetectanddisplayduplicateandtriplicateCOUNTofdateentries Related 1795 Howtoefficientlycountthenumberofkeys/propertiesofanobjectinJavaScript 1006 Howtosortalistofobjectsbasedonanattributeoftheobjects? 1311 LINQ'sDistinct()onaparticularproperty 283 SelectuniqueordistinctvaluesfromalistinUNIXshellscript 1115 Countthenumberofoccurrencesofacharacterinastring 473 HowdoyoufindtherowcountforallyourtablesinPostgres 1888 HowcanIcounttheoccurrencesofalistitem? 352 Pandas'count(distinct)'equivalent 1 GoogleSpreadsheet-MIXseveralcolumnvalues HotNetworkQuestions Whyaregraphsrepresentedasadjacencylistsinsteadofadjacencysets? WhereistheBTCprice"stored"? Whatisthepurposeofusingtractorbeamwhenthereisateleportingdeviceonboard? Myadvisorwantsmetoquittheprogram.WhatshouldIdo? JumptofirstandlastslidesinKeynote Isthisdruidsubclassbalancedcomparedtothenormaldruidsubclasses? WhyarescientistssayingthattheOmicronCOVID-19variantisareasontogetabooster? AoCG2021Day10:Memoryreallocationroutine Doesvoltagedropexistinacurrentcarryingloopofwire Findingtheresonancefrequencyforforceddampedoscillations Howdoyoudistinguish"Substantiv+nach"from"after"inGerman? HowcanIrunashellasauserthathasnoshellaccess? WhatwastheBigBangmodeloriginallycalled? Aquestionontableformatting Atwhatspeedwillthecrankshaftberotatingifeachcylinderofafour-strokecycleengineistobefired200timesaminute? DoIholdthecopyrightontestanswers? Idiomorbetteryetawordforlossoffidelitybycopying Issoundaclassicalmechanicphenomenonorisitaquantumeffect? Whatismeantbyfilterorderofapassivefilter? Howrealisticisaceilingcrawl/cling,canitactuallybedone? Boss'scriticismforthingsoutofmyscope Areallfreeultrafilters'thesame'insomesense? Coloringtheareabetweencircleandpathintikz Afterourfirstzoominterview,mypotentialsupervisoraskedmetoprepareapresentationforanextzoommeeting morehotquestions Questionfeed SubscribetoRSS Questionfeed TosubscribetothisRSSfeed,copyandpastethisURLintoyourRSSreader. Yourprivacy Byclicking“Acceptallcookies”,youagreeStackExchangecanstorecookiesonyourdeviceanddiscloseinformationinaccordancewithourCookiePolicy. Acceptallcookies Customizesettings