COUNTIF vs. COUNTIFS in Google Sheets | Coupler.io Blog
文章推薦指數: 80 %
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
延伸文章資訊
- 1COUNTIF vs. COUNTIFS in Google Sheets | Coupler.io Blog
Every Google Sheets user knows what the COUNT function is for. It returns the amount of numeric v...
- 2What's Google Sheets alternative to CountIFS that works with ...
https://stackoverflow.com/questions/69589540/whats-google-sheets-alternative-to-countifs-that-wor...
- 3[數據分析#3] google sheet filter() 使用方法
如果你在Excel 或是Google Sheet 很常用SUMIFS()、COUNTIFS()、AVERAGEIFS(),這些公式來做「有條件的」計算統計,那對你來說或許已經很足夠了!
- 4COUNTIFS - 文件編輯器說明- Google Support - 星星公主
本功能僅適用於新版Google 試算表。 ... COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ......
- 5COUNTIFS - 文件編輯器說明
本功能僅適用於新版Google 試算表。 傳回根據多個條件而得出的範圍大小。 使用範本. COUNTIFS(A1:A10, ">20" ...