How to Use the COUNTUNIQUEIFS Function in Google Sheets

To conditionally count the unique values in a range, you can now use the COUNTUNIQUEIFS function in Google Sheets. This new function helps us to count the ... GoogleSheets Functions Charts Sheetsvs.Excel Docs Search InfoInspired GoogleSheets Functions Charts Sheetsvs.Excel Docs HomeGoogleDocsSpreadsheetHowtoUsetheCOUNTUNIQUEIFSFunctioninGoogleSheets Toconditionallycounttheuniquevaluesinarange,youcannowusetheCOUNTUNIQUEIFSfunctioninGoogleSheets.Thisnewfunctionhelpsustocounttheuniquevaluesinarangeifprovidedconditionsmatchinadditionalranges. Actually,GoogleSheetsCountuniqueifsfunctionsimplifiestheconditionalcountofuniquevaluesinarange.Earlier,IwasusingtheCountuniquefunctiontogetherwiththeFiltertoachievethis. Inthistutorial,Ihaveincludedthatearliermethodtoo.Let’sexplorethenewCountuniqueifsfunctioninGoogleSheets. COUNTUNIQUEIFSSyntaxinGoogleDocsSheets: COUNTUNIQUEIFS(range,criteria_range1,criterion1,[criteria_range2,…],[criterion2,…]) FunctionArguments: range–Therange/arrayfromwhichthenumberofuniquevaluestobecounted.criteria_range1–Therangeorarrayoverwhichtoevaluatethe‘criterion1’.criterion1–Thepatternortesttoapplyto‘criteria_range1’.Otherargumentsareoptionalandrepetitionoftheabovetwoarguments. Iknowtheseargumentsmaybeconfusingtoyou.Don’tworry.Iwilltrytoelaboratetheseontheformulasectionbelow. FormulaExamplestotheUseofCOUNTUNIQUEIFSFunctioninGoogleSheets Iambeginningwithasimpleexampletomakeyouunderstandthisnewcountfunctionuse. Example:Assume,threepersonsparticipatedinanathleticsevent,i.e.inLongjump(broadjump),andtheirscoreshavebeenrecordedinGoogleSheets.Eachpersonhasthreescoresastheyhaveattemptedthrice. Iwanttofindthecountofuniquescores,‘Performance(inMtr)’,ofeachperson.SeehowtodothatusingaCountuniqueifsformulainGoogleSheets. Formula(incellF2): =COUNTUNIQUEIFS(B2:B,A2:A,E2) InthisCountuniqueifsformula,B2:Bisthe‘range’,A2:Aisthe‘criteria_range1’andE2isthecellcontainingthe‘criterion1’. Asyoucansee,theformulareturns2astheoutputwhenthe‘criterion1’incellE2is“John”. Whenyoucheckthescoresof“John”,youcanfindthescoresentered–8.2m,8m,and8.2m.Theuniquecountofthesescoresis2,right? IhopethisexamplecouldhelpyouunderstandtheargumentsusedintheCountuniqueifsfunction. AlternativeFormulatoConditionallyCountUniqueValuesinGoogleSheets AsIhavementionedatthebeginningofthistutorial,COUNTUNIQUEIFSisarelativelynewfunctioninGoogleSheets. Earlier,Iwasusingacomboformulaforcountinguniquevaluesconditionally.HereisthatCOUNTUNIQUE+FILTERcomboalternativetoCOUNTUNIQUEIFS. =countunique(filter(B2:B,A2:A=E2)) MultipleCriteriainCOUNTUNIQUEIFSFunction Youcanusecomparisonoperators,aswellasmultiplecriteriaintheCountuniqeifsfunctioninDocsSheets. Ifthemultiplecriteriaarefromthesamecolumn,thenyoucantaketheuseoftheRegexmatchinCountuniqeifs(I’llexplainthatlater).Otherwise(ifthemultiplecriteriaarefromdifferentcolumns)thingsareprettyeasy. CriteriafromDifferentColumnsinCOUNTUNIQUEIFSFormula Herewemustusetheoptionalarguments‘criteria_range2’and‘criterion2’. Actually,it’seasytoincludeintheformula.ButIamprovidingyouoneexampleasIamgoingtouseacomparisonoperatorwithoneofthecriteria.Seethatformulabelow. =countuniqueifs(A2:A,B2:B,">100",C2:C,"Delivered") Inthissample(fruits)data,thegivenformulacountstheuniquefruitsifthe‘OrderQty’is>100and‘DeliveryStatus’isdelivered. WhatiftheCriterionisadate? ThedatecriterioninCountuniquesformulamustbeenteredlikethis. ">"&date(2019,8,14) Hereisoneexampleformulatomakeyouknowtheuseofdatecriteriainthisconditionalcountuniqueformula. =countuniqueifs(A2:A,D2:D,">"&date(2019,8,14)) Basedonyourrequirement,replacethecomparisonoperator>with=oranyothercomparisonoperators. MultipleCriteriafromtheSameColumninCOUNTUNIQUEIFSFormula Sometimes,insteadofcountingtheuniquevaluesbasedonasinglecondition/criterionfromanothercolumn,youmaywanttousemultiplecriteria/conditionsfromthatanothercolumn. Forexample,Iwanttheuniquecountofthescoresoftheplayers“John”and“Andie”.Howtoincludethesetwocriteriaintheformula. Actually,Icouldn’tfindanyofficialGoogleDocsdocumentationonhowtoapplymultiplecriteriato‘criteria_range1’inCountuniqueifs. ThismaypossiblybecausethefunctionCountuniqueifswillonlysupportonecriterioninacolumn!Don’tworry!Ihaveasolution/workaroundtoovercomethislimitationwithRegexmatch. Herearetheformulaandexplanation. =ArrayFormula(COUNTUNIQUEIFS(B2:B,regexmatch(A2:A,"John|Andie"),TRUE)) FormulaExplanation: TheRegexmatchreturnsTRUEwhereverthevaluesintherow(ColumnA)matches“John”or“Andie”,else,itreturnsFALSE. Hereintheformula,B2:Bisthe‘range’,theRegexmatchformulaoutput(TRUE/FALSE)isthe‘criteria_range1’,andTRUEisthecriterion. Thatmeansstillweareusingonlyonecriterion,thatistheBooleanvalueTRUE,butdoesthejobofmultiplecriteria. TheArrayFormulaisusedsincetheRegexmatchusestwo(multiple)criteria.Whatifthesaidtwocriteriaareintwocells?ImeancellE2contains“John”andcellE3contains“Andie”. =ArrayFormula(COUNTUNIQUEIFS(B2:B,regexmatch(A2:A,E2&"|"&E3),TRUE)) Pleasetakeanoteofthe‘criteria_range1’use.That’sallfornow.Enjoy! Thanks,Ryanforyourvaluabletipregardingtheintroductionofthis‘new’function. RELATEDARTICLESMOREFROMAUTHOR Spreadsheet HowtoUsetheBAHTTEXTFunctioninGoogleSheets Spreadsheet HowtoHighlightanEntireColumninGoogleSheets Spreadsheet HowtoUsetheVARPAFunctioninGoogleSheets 8COMMENTS HelloPrashanth, Thankyouforyourtutorialstheyhavebeenveryhelpful.Youhavegivenanexamplefordatecriterionwithintheformula,however,whatifthedatesarepartofthetable? Let’ssayinyourfruittableexampleyourcolumnsBandCwereactuallytwodates.Inaseparatecell,I’dliketoenteradateforwhichuniquefruitsincolumnAarecountedifthedateenteredisbetweenthe2datesgivenincolumnsBandC.IsthisstillpossibleusingtheCOUNTUNIQUEIFSfunction? Thanks! Reply HiPablo, AssumethedatecriterionforCOUNTUNIQUEIFSisincellE2.Thentheformulawouldbeasfollows. DaterangeinCountuniqueifsfunction: Best, Reply HiPrashanth IwantedtoknowwhyCountuniqueifsformuladoesn’texpandwhenusedwithArrayFormulalikeotherfunctionssuchasIFSorSUMIFetc. Ihavethreecolumns.ColAisfordates,ColBisforoperatorsandColCistheproducttheyhavehandled.Therearealmost15to20entrieswiththesamedateandtheoperatorhandled3to4differentproducts. Iwantedtoknowthenumberofoperatorsworkedonaparticulardate.IhaveusedaUniqueformulatogetthedatesandusingCountuniqueifsformulatogetthenumberofoperatorspresentforthatday. Itworkswhenusedinasinglecellbutdoesn’texpandwhenusedwithanarrayformula. Anysuggestion? Reply Hi,AdityaDarekar, SimilartoSUMIFSitseemstheCOUNTUNIQUEIFSwon’tworkwiththeArrayFormulatoexpand.YoucantrythisUniqueandQuerycombination. =query(unique(A1:B),"SelectCol1,count(Col2)whereCol1isnotnullgroupbyCol1") Reply Thanks,Prashanth, Ihavetriedbutitisnotgivingmetheuniquevaluescountratheritisgivingmetotalcount. Reply Canyoushareyoursheet,please?Thelinkwillbesafe(Iwon’tpublishit).Onlyshareamockupsheet. Reply Thanksalot,Prashanth…Itisworking. Actually,lateron,IhaveaddedonemorecolumnforItemnameinColBsomycolumnBshiftedtoColC.SoIjustalteredintheformulatoCandCol3. =query(unique(A1:C),"SelectCol1,count(Col3)whereCol1isnotnullgroupbyCol1") Butdidn’twork. Nowjustchangedto =query(unique({A1:A,C1:C}),"SelectCol1,count(Col2)whereCol1isnotnullgroupbyCol1") ITWORKED!!!! Hi,AdityaDarekar, That’sbecauseoftheUnique(). Thanksforyourfeedback! LEAVEAREPLYCancelreply Pleaseenteryourcomment! Pleaseenteryournamehere Youhaveenteredanincorrectemailaddress! Pleaseenteryouremailaddresshere Savemyname,email,andwebsiteinthisbrowserforthenexttimeIcomment. Δ RecentCommentsPrashanthonHowtoUsetheFilterFunctioninGoogleSheets(BasicandAdvancedUse)JimonHowtoUsetheFilterFunctioninGoogleSheets(BasicandAdvancedUse)PrashanthonHowtoFindMultipleOccurrencesofRowsinGoogleSheets[Duplicates]PrashanthonComparisonOperatorsinGoogleSheetsandEquivalentFunctionsDanielonHowtoHideTabsfromSpecificPeopleinGoogleSheets KeyFunctions|ResourcesSumif|Query|Date|IF|Filter|Vlookup|ConditionalFormatting|DataValidation|ExcelVsSheets|Forms|Docs|Row-wiseArray Home ContactUs About Disclaimer PrivacyPolicy Sitemap ©2021PRASHANTKV|InfoInspired|AllRightsReserved