Using COUNTIF, COUNTIFS in Google Sheets to Sort ...

Typing “=COUNTIF” into the formula bar in Google Sheets will auto-generate formula options from a list. Select “=COUNTIF” and navigate to the ... Advertisement Analytics&Data UsingCOUNTIF,COUNTIFSinGoogleSheetstoSort,AnalyzeData December31,2019•ArmandoRoggio Whetheryou’reresearchingkeywords,reviewingleadsources,oranalyzingcustomerattributesfromaCRMexport,GoogleSheets’COUNTIFandCOUNTIFSformulascanhelp. Thesetwospreadsheetformulaswillonlycountacell(fromarangeofcells)ifspecificcriteriaaremet. Recently,amultichannelretailerwantedtoanalyzefiveyears’worthofleadandconversiondatafromitscustomerrelationshipmanagementsoftware.Leadsmightcomefromtheretailer’swebsite,itsvariousdigitalmarketingefforts,offlineadvertising,orfolksjustwalkingintoastore. …amultichannelretailerwantedtoanalyzefiveyears’worthofleadandconversiondata… Thecompanysellsrelativelyexpensiveproductsthatrangefrom$10,000to$50,000each.Thus,itmaytakeafewcontactstocloseasale.Understandingwhichsourcesproduceleadscouldhelptheretailerunderstandhowtogetmorecustomers. Unfortunately,thecompany’sCRMdidnotprovidetherequiredreport.Soacoupleoffolksfromtheretailer’smarketingdepartmentexportedthedataincomma-separatedvaluesformat.ThisCSVfilewasuploadedtoaGoogleSheet,andthankstoCOUNTIFandCOUNTIFS,itwasfairlyeasytoidentifywhichleadsourcesproducedthemostsales. TodemonstratehowtoapplyCOUNTIFandCOUNTIFSformulas,I’llusesampledata. COUNTIF Thedatahasfourcolumns:anordernumber,theU.S.statefromwhichtheorderwasplaced,theleadsource,andthesaleamount.Fortheexamples,I’llfocusonjusttwoofthesecolumns:thestateandtheleadsource.Thenameofthesheetis“LeadData.”NoticethatIincludedthisnametherangeofcells. TheexampleorsampledataasitappearedinaGoogleSheet.Noticethefourcolumnsofdata;ourexampleswillfocusonstateandleadsource.Clickimagetoenlarge. IwillcreateasecondsheettoanalyzeleadsourcesbyU.S.state.Thissheetwillhaveacolumntolistthestates,thetotalcountforeachstate,andthecountandpercentageofthetotalforeachleadsource. AsecondsheetwillemploytheCOUNTIFandCOUNTIFSformulas.Itincludesapairofcolumns—“Count”and“Percent”— foreachleadsource.Eachstate’scountisinarow.Clickimagetoenlarge. IwillusetheCOUNTIFformulatogetthetotalnumberofordersoriginatingfromeachstate.The formulaacceptstwoparameters,therangeandthecriterion. =COUNTIF(range,criterion) Therangeisanysetofcellsinthecurrentsheetoranothersheet.Ourrangewillcomefromthe“LeadData”sheetandnotthecurrentone. Typing “=COUNTIF”intotheformulabarinGoogleSheetswillauto-generateformulaoptionsfromalist.Select“=COUNTIF”andnavigatetotherangeandthendragtoselectit. GoogleSheetswillrecognizetheCOUNTIFformulaasyoustarttotypeit.Clickimagetoenlarge. Whenacellcontainstext,thecriterionisquoted.Forourexample,IfirstwantedtogetacountofallofthesalestoCalifornia—designatedas“CA”inthecellsofthestatecolumn.ThisiswhattheCOUNTIFformulalookedlike: =COUNTIF('LeadData'!B2:B25,"CA") TherangeshouldappearasyouselectitintheGoogleSheets’formulabar.Thentypeacommaandthecriterionvalue.Clickimagetoenlarge. Thesectiondescribingthesheetandrangeuses‘LeadData’!B2:B25astherangeandthestate(“CA”)forthecriterion.Iftherangehadbeeninthesameorcurrentsheet,itwouldnothaveincludedthesheetname. Icanusethissameapproachtogetthecountforeachstateonthelist. UsetheCOUNTIFformulatoobtainacountforeachofthestatesinconsideration.Clickimagetoenlarge. COUNTIFS TherelatedCOUNTIFSformulawillacceptaseriesofrangesandcriterionpairs.Wecanuseittofindthenumberofleadsthatconvertedfromeachsource,suchasFacebook. TheformulaissimilartoCOUNTIF.Inthiscase,wearecountingonlyrowsthathave“CA”inthestatecolumn. =COUNTIFS('LeadData'!B2:B25,"CA") Tothis,weaddacomma,followedbyasecondrangeandasecondcriterion—Facebookinthisexample. =COUNTIFS('LeadData'!B2:B25,"CA",'LeadData'!C2:C25,"Facebook") TheCOUNTIFSformulawillallowforseveralseriesofrangeandcriterionpairsseparatedbyacomma.Clickimagetoenlarge. ChangingthecriterionforthestatewillgiveusacountoftheleadsfromFacebookthatconvertedconsumersfromeachstate.Forourexample,Ialsowantedtoknowthepercentageoftotalleadstheserepresented.SoIcanaddaslashandareferencetothetotalleadsforagivenstate. =COUNTIFS(('LeadData'!B2:B25,"CA",'LeadData'!C2:C25,"Facebook")/B3) UsetheCOUNTIFSformulaforeachstateandeachleadsource.Theresultingcountcanbedividedbythetotalnumberofconvertedleadsforthestatetogetapercentage.Clickimagetoenlarge. Operators YoucanalsoincludeoperatorsinthecriterionvalueforeitherCOUNTIForCOUNTIFS.Placetheseinsidethequotationmarkssurroundingthecriterionvalue. Herearesomeexamples. “<>CA”—Notequal“CA”where“<>”meansnotequal. “>10”—Greaterthan10. “<10”—Lessthan10. “>=10”—Greaterthanorequalto10. “<=10”—Lessthanorequalto10. Wildcards Finally,therearealsotwoavailablewildcardcharactersforcriterionvalues. ?—Matchesanysinglecharacter. *—Matcheszeroormorecontiguouscharacters. Tomatchanactual?or*,putatilde(~)infrontofit.Forexample,“~?”wouldmatchaquestionmark. 〈 PreviousArticle NextArticle 〉 ArmandoRoggio Bio  •  RSSFeed Management&FinanceCreateGoogleDocsfromaGoogleSheet Analytics&DataUsingExceltoForecastSales,Expenses,More Advertisement Popular 11NewSocialNetworksfor2022SalesReport:2021ThanksgivingDay,BlackFriday,Cyber...5ContentMarketingIdeasforJanuary202222TopLocalBusinessDirectories3WaysGoogle'sSearchConsoleImprovesOrganicClicks...TheBestEcommerceContentforInstagram  OurPartners EmailNewsletter+FreeEbook 50GreatEcommerceIdeas PrivacyPolicy IndustryIQ SalesChannelsforthe2021HolidaySeason ZohoInventory BestSiteSearchPracticesforQ4andBeyond Doofinder ExecutiveGuidetoEcommerceReplatforming KalioCommerce Advertisement WebinarU InstantVideo+Ebook OperationalFoundationsofKPIDashboards EcommerceConversionMastery HowIOSSDrivesSalestoE.U.Consumers 3TipstoSucceedinInternationalEcommerce Advertisement EmailNewsletter+FreeEbook 50GreatEcommerceIdeas PrivacyPolicy x