COUNTIF vs. COUNTIFS in Google Sheets | Coupler.io Blog

文章推薦指數: 80 %
投票人數:10人

Every Google Sheets user knows what the COUNT function is for. It returns the amount of numeric values in a data range. EveryGoogleSheetsuserknowswhattheCOUNTfunctionisfor.Itreturnstheamountofnumericvaluesinadatarange.ForcountingnotonlynumbersyoucanuseCOUNTA.Andwhenyourgoalistocountdatathatmatchspecificcriteria,thenyoushouldgowitheitherCOUNTIForCOUNTIFS.Readontolearnhowtheydifferandwhatyoucandowithbothfunctionsinreal-lifecases.  Doyoupreferwatchingthanreading?ThencheckoutthisvideoaboutCOUNT,COUNTIF,COUNTIFSfunctionsinGoogleSheetsbyRailsware. TableofContents hide COUNTIFfunctiontocountvaluesbyonecriterion COUNTIFsyntax WhatyoucancountusingCOUNTIF Counttextualandnumericvaluesbyexactmatch Counttextualvaluesbypartialmatch Countnumericvaluesbyalogicalexpressioncriterion:greater,lessorequal Countblankandnon-blankcells CanIcountvaluesbymultiplecriteriawithCOUNTIF? COUNTIFSfunctiontocountvaluesbymultiplecriteria COUNTIFSsyntax COUNTIFSformulaexample UseQUERYasaCOUNTIFSalternative HowtocountvaluesbypartialmatchcheckwithCOUNTIF&COUNTIFSwildcards Questionmarkwildcard(?) Asteriskwildcard(*) COUNTIForCOUNTIFSreallifeusecases Usecase:AlivesalesdashboardintegratedwithPipedrive Towrapup COUNTIFfunctiontocountvaluesbyonecriterion TheCOUNTIFfunctionisahybridofCOUNTandIFfunctions.Itallowsyoutoperformdatacountbasedonaspecificcriterion.  COUNTIFsyntax =COUNTIF(data_range,"criterion") data_range–Inserttherangeofcellstocount. criterion–Insertthecriterion(eithernumberorstring)tocheckthedata_rangeagainst.Alternatively,youcanreferenceacellthatcontainsacriterion(inthiscase,don’tusequotationmarks).WhatyoucancountusingCOUNTIF  Alltheformulaexamplesbelowareavailableinthisspreadsheet. Counttextualandnumericvaluesbyexactmatch Youcancountthenumberofcellsinthedatarangethatcontainaspecifictextornumber. COUNTIFformulaexamplefortextualvalues =COUNTIF('Allorders'!F:F,"OliviaJensen") Interpretation: CountalltheOliviaJensenvalues(criterion)intheFcolumnoftheAllorderssheet(data_range).  Counttextualvaluesbypartialmatch Youcancountthenumberofcellsinthedatarangethatcontainaspecificpartofthetext.Forthis,you’llneedtousewildcardcharacters:*or?.   COUNTIFformulaexampleforpartialtextualmatch  =COUNTIF('Allorders'!E:E,"*burger") Interpretation: Countallthevaluesthatcontaintheword“burger”(criterion)intheEcolumnoftheAllorderssheet(data_range).  Countnumericvaluesbyalogicalexpressioncriterion:greater,lessorequal Youcancountthenumberofcellsinthedatarangethatcontainanumericvaluethatislessthan,greaterthanorequaltoaspecifiednumber.Theselogicalcriteriacanbecombinedwitheachother. COUNTIFformulaexamplefornumericvalues =COUNTIF('Allorders'!H:H,">5") Interpretation: Countallthevaluesgreaterthan5(criterion)intheHcolumnoftheAllorderssheet(data_range).  Countblankandnon-blankcells Youcancountthenumberofblankornon-blankcellsinthedatarange. COUNTIFformulaexampletocountblankcells =COUNTIF('Allorders'!A:A,"") COUNTIFformulaexampletocountnon-blankcellswithanyvalue =COUNTIF('Allorders'!A:A,"<>") ThesyntaxusthesameasforExcelCOUNTIFnotblank. COUNTIFformulaexampletocountnon-blankcellswithatextualvalue =COUNTIF('Allorders'!A:A,"*") CanIcountvaluesbymultiplecriteriawithCOUNTIF? InExcel,COUNTIFsupportsmultiplecriteria.InGoogleSheets,theCOUNTIFfunctionacceptsonlyonedatarangeandonecriterion.Somesuggestthefollowingtricktocountvaluesacrossmultiplecriteriawith: =COUNTIF(data_range,“criterion#1”)+COUNTIF(data_range#2,“criterion#2”)+COUNTIF(data_range#3,“criterion#3”)… However,thiswillonlyreturnthesumofseparatecountsbasedonseparatecriteria,whichisunlikelytobewhatyouneed.  Let’snotreinventthewheel,sincethereisanout-of-the-boxsolution:COUNTIFS. COUNTIFSfunctiontocountvaluesbymultiplecriteria  TheCOUNTIFSfunctionisahybridofCOUNTandIFSfunctions.Itallowsyoutocheckmultiplerangeswithmultiplecriteria.Theformulareturnsthecountbasedonthecriteriamet.  COUNTIFSsyntax =COUNTIFS(data_range#1,"criterion#1",data_range#2,"criterion#2",data_range#3,"criterion#3",...) data_range#1–Inserttherangeofcellstocount. criterion#1–Insertthecriterion(eithernumberorstring)tocheckthedata_range#1against.Alternatively,youcanreferenceacellthatcontainsacriterion(inthiscase,don’tusequotationmarks).data_range#2,"criterion#2",...–Additionalrangesandcriteriatocheck.Thenumberofrowsandcolumnsinadditionalrangesmustbeequaltothoseinthedata_range#1. COUNTIFSformulaexample =COUNTIFS('Allorders'!G:G,">=10",'Allorders'!F:F,"MarkOliver",'Allorders'!I:I,"<50") Interpretation: Countthevaluesthatmeetallthefollowingcriteria:1.ValuesintheGcolumnoftheAllorderssheet(data_range#1)thataregreaterorequalto10(criterion#1).2.ValuesintheFcolumnoftheAllorderssheet(data_range#2)thatcontain“MarkOliver”textexactly(criterion#2).3.ValuesintheIcolumnoftheAllorderssheet(data_range#3)thatarelessthan50(criterion#3).  Inthistab,youcancheckouttheformulaexampleandcompareittoseparateCOUNTIFformulasbasedonthementionedcriteria. HowdoyouuseCOUNTIFSforthesamerange?Tocheckmultiplecriteriainthesamecolumnrange,youneedtouseARRAYFORMULA+SUM+COUNTIFSandplaceyourcriteriainthecurlybraces Let’ssayyouneedtocheckmultiplecriteriainthesamedatarange.Forexample,youwanttocountthevaluesintheFcolumnoftheAllorderssheet(data_range)thatcontainexact“MarkOliver”(criterion#1)andthevaluesthatcontain“OliviaJensen”(criterion#2).Youmighthavethoughtaboutthefollowing: =COUNTIFS(‘Allorders’!F:F,”MarkOliver”,’Allorders’!F:F,”OliviaJensen”) Unfortunately,thiswon’tdobecauseitactsliketheANDfunctioninGoogleSheets:it’lllookforavaluethatis“MarkOliver”and“OliviaJensen”atthesametime. Tocheckmultiplecriteriainthesamecolumnrange,youneedtouseARRAYFORMULA+SUM+COUNTIFSandplaceyourcriteriainthecurlybracesasfollows: =Arrayformula( Sum( COUNTIFS('Allorders'!F:F,{"MarkOliver","OliviaJensen"}) ) ) UseQUERYasaCOUNTIFSalternative EarlierwebloggedabouttheGoogleSheetsQUERYfunction,whichallowsyoutograbthedatabasedoncriteriaandperformvariousdatamanipulations.YoucanuseQUERYasanalternativetoCOUNTIFS: UsetheSELECTclausecombinedwiththeCOUNT()functiontomakeadatacount.UsetheWHEREclausecombinedwiththeANDlogicalfunctiontosetupmultiplecriteria.HereishowtheQUERYformulaforcountingvaluesbasedonthemultiplecriteriawilllook: COUNTIFSformulaQUERYformula=COUNTIFS('Allorders'!G:G,">=10",'Allorders'!F:F,"MarkOliver",'Allorders'!I:I,"<50")=QUERY('Allorders'!F:I,"selectcount(F)whereF='MarkOliver'andG>=10andI<50",0)Checkouttheformulaexampleinthistab.YoumayalsobeinterestedinhowtouseacombinationofQUERY+IMPORTRANGEinGoogleSheets. HowtocountvaluesbypartialmatchcheckwithCOUNTIF&COUNTIFSwildcards Therearetwowildcardsyoucanusetocheckthepartialmatchofthedatarange:?and*.  Questionmarkwildcard(?)  Thequestionmark(?)allowsyoutodisguiseonecharacteryouwanttoignore.Onequestionmark=onecharacter.Forexample,"???ed"meansthatyou’relookingforthe5-letterwordsthatendwith“ed“.  COUNTIFformulaexamplewith? =COUNTIF('Allorders'!E:E,"??????sandwich") Interpretation: CountthevaluesintheEcolumnoftheAllorderssheet(data_range)thatcontainthe14-letterwordingthatendswith“sandwich”(criterion).  Asteriskwildcard(*)  Theasterisk(*)allowsyoutodisguiseanynumberofcharactersyouwanttoignore.Forexample: "*sandwich"–meansthatyou’relookingforwordingsthatendwith“sandwich“. "sandwich*"–meansthatyou’relookingforwordingsthatstartwith“sandwich“. "*sandwich*"–meansthatyou’relookingforanywordingsthatcontain“sandwich“. COUNTIFformulaexamplewith* =COUNTIF('Allorders'!E:E,"*cheese*") Interpretation: CountthevaluesintheEcolumnoftheAllorderssheet(data_range)thatcontain“cheese“inthewording(criterion).  Bothexamplesareavailableinthistab. Note:Usetildesign(~)beforeanasterisk(*)oraquestionmark(?)totreatthemassimplesigns.Forexample,"~**"meansthatyou’relookingforthevaluesthatstartwithanasterisk“*“. COUNTIForCOUNTIFSreallifeusecases Theexamplesabovewillunlikelybeusefulinreallife.However,bothCOUNTIFandCOUNTIFScandothejobwhenyouneedtobuildcomplexdashboards.Checkoutthefollowingusecasethatwillunfoldthepracticalvalueofthesefunctions. Usecase:AlivesalesdashboardintegratedwithPipedrive Idea:  Importthedatafromasalesmanagementsoftware(wepickedPipedriveasanexample)andbuildasalesdashboardstuffedwithactionablemetricsanddatavisualizations.Moreover,thedashboardwillbeupdatedautomaticallyeveryday! Step1:Pipedrivedataimport FirstweneedtoimportourdealsfromPipedrivetoGoogleSheetsandautomatedataupdatesinthefuture.Coupler.iocanhandlethesetaskseasily.It’saGoogleSheetsadd-ontoimportdatafromversatileappsandonlinedatasources.You’llneedtodothefollowing: FindCoupler.ioontheGoogleWorkspaceMarketplaceandinstallitSetupanecessaryintegrationRuntheintegrationtopullyourdatatothespreadsheetStep2:Buildingthesalesdashboard Thesalesdashboardtemplate,whichyoucandownloadandadjustforyourproject,displaysmultiplesalesmetrics.ButintermsoftheCOUNTIFusage,we’llneedthefollowingones: GeochartwithaconversionrateDealsbreakdown(won,open,andlost)WinrateGeochartwithaconversionrate Conversionrateshowstheratioofwondealstototaldeals.Tocountthetotaldeals,we’llneedtheCOUNTAfunction,whereasCOUNTIFwillhelpusreturnthewondeals.ApplytheFILTERfunctiontosortresultsbythecountryandhereistheformula: =COUNTIF(Filter(Deals!AL2:AL,Deals!Z2:Z=A61),"won")/ COUNTA(Filter(Deals!AL2:AL,Deals!Z2:Z=A61)) Deals!AL2:AL–thestatuscolumnoftheimporteddataDeals!Z2:Z–theorg_id.addresscolumnoftheimporteddataA61–acellwiththecountrynameDealsbreakdown(won,open,andlost) Asimilarformulawillreturnthenumberofdealsbystatus.Hereishowitlooksforthelostdeals: =IF( ISBLANK(A19), COUNTIF(Deals!AL2:AL,"lost"), COUNTIF(Filter(Deals!AL2:AL,Deals!Z2:Z=A19),"lost") ) Deals!AL2:AL–thestatuscolumnoftheimporteddataDeals!Z2:Z–theorg_id.addresscolumnoftheimporteddataA19–acellwiththecountrynameWinrate Winrateistheratioofwondealstocloseddeals,whichinturnequalsthesumoflostdealsandwondeals.COUNTIFwillhelpushandlethiscomplexcalculation: =IF( ISBLANK(A19), IFERROR( COUNTIF( Filter(Deals!AL2:AL,Deals!CN2:CN=2016),"won")/ (COUNTIF( Filter(Deals!AL2:AL,Deals!CN2:CN=2016),"won")+ COUNTIF( Filter(Deals!AL2:AL,Deals!CN2:CN=2016),"lost")), "Nocloseddeals"), IFERROR( COUNTIF( Filter(Deals!AL2:AL,Deals!Z2:Z=A19,Deals!CN2:CN=2016),"won")/ (COUNTIF( Filter(Deals!AL2:AL,Deals!Z2:Z=A19,Deals!CN2:CN=2016),"won")+ COUNTIF( Filter(Deals!AL2:AL,Deals!Z2:Z=A19,Deals!CN2:CN=2016),"lost")), "Nocloseddeals") ) Deals!CN2:CN–theYearcolumnDeals!AL2:AL–thestatuscolumnoftheimporteddataDeals!Z2:Z–theorg_id.addresscolumnoftheimporteddataFormoreaboutthisinteractivedashboard,readourblogpost“HowtoBuildSalesTrackerwithGoogleSheets“. Towrapup NowyouknowhowtocountwithCOUNTIF/COUNTIFS.Willthisknowledgebehelpful?WehopesoandencourageyoutocheckotherGoogleSheetsfunctionswe’vedescribedintheCoupler.ioblog.Themoreyouknow,thebetteryourreportinganddashboardswillwork.Goodluckwithyourdata! BacktoBlog Commentsareclosed. Accessyourdatainasimpleformatforfree! StartFree



請為這篇文章評分?