Hoe om veelvuldige voorwaardes in Excel COUNTIF en COUNTIFS te gebruik

Excel het baie funksies waar die gebruiker een of meer voorwaardes moet spesifiseer om die resultaat te kry.Byvoorbeeld, as jy selle wil tel op grond van verskeie kriteria, kan jy die COUNTIF- of COUNTIFS-funksies in Excel gebruik.

Hierdie tutoriaal dek verskeie maniere om enkele of veelvuldige kriteria in Excel se COUNTIF- en COUNTIFS-funksies te gebruik.

Alhoewel ek hoofsaaklik op die COUNTIF- en COUNTIFS-funksies in hierdie tutoriaal sal fokus, kan al hierdie voorbeelde ook gebruik word met ander Excel-funksies wat verskeie kriteria as invoer neem (soos SUMIF, SUMIFS, AVERAGEIF en AVERAGEIFS).

Inleiding tot die Excel COUNTIF en COUNTIFS funksies

Kom ons verstaan ​​eers hoe om die COUNTIF- en COUNTIFS-funksies in Excel te gebruik.

Excel COUNTIF-funksie (met 'n enkele maatstaf)

Die Excel COUNTIF-funksie werk die beste wanneer jy selle op grond van 'n enkele maatstaf wil tel.As jy op verskeie kriteria wil tel, gebruik die COUNTIFS-funksie.

sintaksis

=COUNTIF(reeks, toestand)

Invoer parameters

• reeks -  Die reeks selle wat jy wil bereken.
• toestand -Kriteria wat geëvalueer moet word teen die reeks selle wat getel moet word.

Excel COUNTIFS-funksie (met veelvuldige kriteria)

Die Excel COUNTIFS-funksie werk die beste wanneer jy selle wil tel op grond van verskeie kriteria.

sintaksis

=COUNTIFS(kriteriareeks1;kriteria1;[kriteriareeks2;kriteria2]...)

Invoer parameters

• kriteriareeks1 – Die reeks selle wat jy teen kriterium 1 wil evalueer.
• kriteria1 – Die maatstaf wat jy wil evalueer is criterion_range1 om te bepaal watter selle om te evalueer.
• [kriteriareeks2] – Die reeks selle wat jy teen kriterium 2 wil evalueer.
• [kriteria2] – Die kriteria wat jy wil evalueer is criterion_range2 om te bepaal watter selle om te tel.

Kom ons kyk nou na 'n paar voorbeelde van die gebruik van veelvuldige kriteria in Excel se COUNTIF-funksie.

Gebruik die NUMBER voorwaarde in die Excel COUNTIF funksie

#1 Tel selle wanneer maatstaf gelyk is aan 'n waarde

Om die telling van selle te kry waar die voorwaarde parameter gelyk is aan die gespesifiseerde waarde, kan jy die voorwaarde direk invoer of 'n selverwysing gebruik wat die voorwaarde bevat.

Hieronder is 'n voorbeeld waar ons selle tel wat die getal 9 bevat (wat beteken dat die toestand parameter gelyk is aan 9).Hier is die formule:

=COUNTIF($B$2:$B$11,D3)

In die voorbeeld hierbo (in die prentjie), is die maatstaf in sel D3.Jy kan ook voorwaardes direk in formules invoer.Byvoorbeeld, jy kan ook gebruik:

=COUNTIF($B$2:$B$11,9)

#2 Tel selle wanneer toestand groter is as waarde

Om die telling van selle met 'n waarde groter as 'n gespesifiseerde waarde te kry, gebruik ons ​​die groter as-operateur (">").Ons kan dit direk in formules gebruik of ons kan selverwysings met voorwaardes gebruik.

Wanneer ons 'n operateur in 'n toestand in Excel gebruik, moet ons dit binne dubbele aanhalingstekens plaas.Byvoorbeeld, as die toestand groter as 10 is, moet ons ">10" as die voorwaarde invoer (sien prent hieronder):

Hier is die formule:

=COUNTIF($B$2:$B$11,">10″)

Jy kan ook toestande in selle insluit en selverwysings as voorwaardes gebruik.In hierdie geval hoef jy nie die voorwaarde tussen dubbele aanhalingstekens te plaas nie:

=COUNTIF($B$2:$B$11,D3)

Dit kan ook die geval wees wanneer jy wil hê dat 'n toestand in 'n sel moet wees, maar dit nie saam met 'n operateur wil gebruik nie.Byvoorbeeld, jy wil dalk hê dat die getal in sel D3 10 moet wees in plaas van >10.

In hierdie geval moet jy 'n voorwaardelike parameter skep, wat 'n kombinasie van operateur en selverwysing is (sien prent hieronder):

=COUNTIF($B$2:$B$11,">"&D3)

Let wel: Wanneer jy operateurs en selverwysings kombineer, word die operateurs altyd in dubbele aanhalingstekens ingesluit.Operateurs en selverwysings word met 'n ampersand (&) verbind.

#3 Tel selle wanneer kriterium minder as 'n waarde is

Om die telling van selle met 'n waarde minder as 'n gespesifiseerde waarde te kry, gebruik ons ​​die minder as-operateur ("<").Ons kan dit direk in formules gebruik of ons kan selverwysings met voorwaardes gebruik.

Wanneer ons 'n operateur in 'n toestand in Excel gebruik, moet ons dit binne dubbele aanhalingstekens plaas.Byvoorbeeld, as die maatstaf is dat die hoeveelheid minder as 5 moet wees, dan moet ons "<5" as maatstaf invoer (sien prent hieronder):

=COUNTIF($B$2:$B$11,"<5")

Jy kan ook toestande in selle insluit en selverwysings as voorwaardes gebruik.In hierdie geval hoef jy nie die standaard in dubbele aanhalingstekens te plaas nie (sien prent hieronder):

=COUNTIF($B$2:$B$11,D3)

Ook wil jy dalk hê dat die toestand in die sel moet wees, maar nie by die operateur nie.Byvoorbeeld, jy wil dalk hê dat sel D3 die nommer 5 moet hê in plaas van <5.

In hierdie geval moet jy 'n voorwaardelike parameter skep wat 'n kombinasie van operateur en selverwysing is:

=COUNTIF($B$2:$B$11,"<"&D3)

Let wel: Wanneer jy operateurs en selverwysings kombineer, word die operateurs altyd in dubbele aanhalingstekens ingesluit.Operateurs en selverwysings word met 'n ampersand (&) verbind.

#4 Tel selle met veelvuldige kriteria – tussen twee waardes

Om die telling van waardes tussen twee waardes te kry, moet ons verskeie voorwaardes in die COUNTIF-funksie gebruik.

Hier is twee maniere om dit te doen:

Metode XNUMX: Gebruik die COUNTIFS-funksie

Die COUNTIFS-funksie kan veelvuldige toestande as argumente hanteer en selle tel slegs as alle voorwaardes WAAR is.Om selle te tel met waardes tussen twee gespesifiseerde waardes, soos 5 en 10, kan ons die volgende COUNTIFS-funksie gebruik:

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)

Let wel: Die formule hierbo tel nie selle wat 5 of 10 bevat nie.As jy hierdie selle wil insluit, gebruik die groter as of gelyk aan (>=) en kleiner as of gelyk aan (<=) operateurs.Hier is die formule:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

Jy kan ook hierdie toestande in selle insluit en selverwysings as voorwaardes gebruik.In hierdie geval hoef jy nie die standaard in dubbele aanhalingstekens te plaas nie (sien prent hieronder):

Jy kan ook 'n kombinasie van selverwysings en operateurs gebruik (deur operateurs direk in formules in te voer).Wanneer operateurs en selverwysings gekombineer word, word die operateurs altyd in dubbele aanhalings ingesluit.Operateurs en selverwysings word met 'n ampersand (&) verbind.

Metode 2: Gebruik twee COUNTIF-funksies

As jy veelvuldige voorwaardes het, kan jy COUNTIFS gebruik of 'n kombinasie van COUNTIF-funksies skep.Die volgende formule sal dieselfde ding doen:

=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)

In die formule hierbo vind ons eers die aantal selle met 'n waarde groter as 5 en trek dan die aantal selle met 'n waarde groter as 10 af.Dit sal vir ons 'n resultaat van 5 gee (dws met waardes groter as 5 en minder as of gelyk aan 10).

As jy wil hê die formule moet beide 5 en 10 bevat, gebruik eerder die volgende formule:

=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)

As jy wil hê dat die formule "5" en "10" van die telling moet uitsluit, gebruik die volgende formule:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

Jy kan hierdie toestande in selle insluit en selverwysings gebruik, of jy kan 'n kombinasie van operateurs en selverwysings gebruik.

Gebruik TEKST voorwaardes in Excel funksies

#1 Tel selle wanneer toestand gelyk is aan gespesifiseerde teks

Om selle te tel wat 'n presiese passing met 'n gespesifiseerde teks bevat, kan ons eenvoudig daardie teks as 'n voorwaarde gebruik.Byvoorbeeld, in 'n datastel (hieronder getoon), as ek alle selle met die naam Joe wil tel, kan ek die volgende formule gebruik:

=COUNTIF($B$2:$B$11,"Joe")

Aangesien dit 'n teksstring is, moet ek die teksvoorwaarde tussen dubbele aanhalingstekens plaas.

Jy kan ook 'n toestand in 'n sel insluit en dan daardie selverwysing gebruik (soos hieronder getoon):

=COUNTIF($B$2:$B$11,E3)

Let wel: Jy kan verkeerde resultate kry as daar voor-/volgende spasies in die toestand- of toestandreeks is.Maak seker dat u die data skoonmaak voordat u hierdie formules gebruik.

#2 Tel selle wanneer toestand nie gelyk is aan gespesifiseerde teks nie

Soortgelyk aan wat ons in die voorbeeld hierbo gesien het, kan jy ook selle tel wat nie gespesifiseerde teks bevat nie.Om dit te doen, moet ons die nie-gelyk-operateur (<>) gebruik.

Gestel jy wil alle selle tel wat nie die naam JOE bevat nie, hier is 'n formule wat dit kan doen:

=COUNTIF($B$2:$B$11,"<>Joe")

Jy kan ook toestande in selle insluit en selverwysings as voorwaardes gebruik.In hierdie geval hoef jy nie die standaard in dubbele aanhalingstekens te plaas nie (sien prent hieronder):

=COUNTIF($B$2:$B$11,E3)

Dit kan ook die geval wees wanneer jy wil hê dat 'n toestand in 'n sel moet wees, maar dit nie saam met 'n operateur wil gebruik nie.Byvoorbeeld, jy wil dalk hê dat sel D3 die naam Joe moet hê in plaas van <>Joe.

In hierdie geval moet jy 'n voorwaardelike parameter skep, wat 'n kombinasie van operateur en selverwysing is (sien prent hieronder):

=COUNTIF($B$2:$B$11,"<>"&E3)

Wanneer operateurs en selverwysings gekombineer word, word die operateurs altyd in dubbele aanhalings ingesluit.Operateurs en selverwysings word met 'n ampersand (&) verbind.

Gebruik DATUM-voorwaardes in Excel COUNTIF- en COUNTIFS-funksies

Excel stoor datums en tye as getalle.So ons kan dit soos 'n nommer gebruik.

#1 Tel selle wanneer toestand gelyk is aan gespesifiseerde datum

Om die telling van selle te kry wat 'n gespesifiseerde datum bevat, sal ons die gelyke operateur (=) saam met die datum gebruik.

Om met datums te werk, beveel ek aan om die DATUM-funksie te gebruik, aangesien dit enige kans uitskakel dat die datumwaarde verkeerd is.So, byvoorbeeld, as ek die datum 2015 September 9 wil gebruik, kan ek die DATUM-funksie soos volg gebruik:

=DATE(2015,9,1;XNUMX;XNUMX)

Ten spyte van die streeksverskille, sal hierdie formule dieselfde datum terugkeer.Byvoorbeeld, 01-09-2015 sou 2015 September 9 onder Amerikaanse datumsintaksis wees, en 1 Januarie 2015 onder VK-datumsintaksis.Hierdie formule sal egter altyd 2105 September 9 terugkeer.

Hier is die formule om die aantal selle te tel wat die datum 02-09-2015 bevat:

=COUNTIF($A$2:$A$11,DATUM(2015,9,2;XNUMX))

#2 Tel selle wanneer toestand voor of na die gespesifiseerde datum is

Om selle te tel wat datums voor of na 'n gespesifiseerde datum bevat, kan ons minder as/groter as operateurs gebruik.

As ek byvoorbeeld alle selle wat datums na 2015 September 9 bevat wil tel, kan ek die volgende formule gebruik:

=COUNTIF($A$2:$A$11,">"&DATE(2015,9,2;XNUMX))

Net so kan jy ook die aantal selle voor 'n gespesifiseerde datum tel.As jy datums by die telling wil insluit, gebruik die "gelyk aan" operateur saam met die "groter as/minder as" operateurs.

Jy kan ook selverwysings gebruik wat datums bevat.In hierdie geval moet jy 'n ampersand (&) gebruik om die operateur (binne dubbele aanhalingstekens) met die datum te kombineer.

Sien die voorbeeld hieronder:

=COUNTIF($A$2:$A$11,">"&F3)

#3 Tel selle met veelvuldige kriteria – tussen twee datums

Om die telling van waardes tussen twee waardes te kry, moet ons verskeie voorwaardes in die COUNTIF-funksie gebruik.

Ons kan dit op twee maniere doen - 'n enkele COUNTIFS-funksie of twee COUNTIFS-funksies.

Metode XNUMX: Gebruik die COUNTIFS-funksie

Die COUNTIFS-funksie kan veelvuldige voorwaardes as argumente neem en selle tel slegs as alle voorwaardes WAAR is.Om selle te tel met waardes tussen twee gespesifiseerde datums, soos 9 September en 2 September, kan ons die volgende COUNTIFS-funksie gebruik:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

Die formule hierbo tel nie die selle wat die gespesifiseerde datum bevat nie.As jy ook hierdie datums wil insluit, gebruik die groter as of gelyk aan (>=) en kleiner as of gelyk aan (<=) operateurs.Hier is die formule:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

Jy kan ook datums in selle insluit en selverwysings as kriteria gebruik.In hierdie geval kan jy nie die operateur die datum in die sel laat insluit nie.Jy moet operateurs handmatig by die formule voeg (in dubbele aanhalingstekens) en 'n ampersand (&) gebruik om selverwysings by te voeg.Sien onder:

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)

Metode 2: Gebruik die COUNTIF-funksie

As jy veelvuldige voorwaardes het, kan jy 'n COUNTIFS-funksie gebruik of 'n kombinasie van twee COUNTIFS-funksies skep.Die volgende formule sal ook die truuk doen:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

In die formule hierbo vind ons eers die aantal selle waarvan die datum na 9 September is, en trek dan die aantal selle af waarvan die datum na 2 September is.Dit sal 'n resultaat van 9 gee (dit is die aantal selle met datums na 7 September en voor of op 7 September).

As jy nie wil hê dat die formule beide 9 en 2 September moet bereken nie, gebruik eerder die volgende formule:

=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

As jy twee datums van die telling wil uitsluit, gebruik die volgende formule:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))

Daarbenewens kan jy 'n standaarddatum in 'n sel stel en selverwysings gebruik (en operateurs in dubbele aanhalingstekens met ampersands).

Gebruik jokertekens in die voorwaardes van die COUNTIF- en COUNTIFS-funksies

Daar is drie jokertekens in Excel:

1. *(Asterisk) - dit verteenwoordig enige aantal karakters.Byvoorbeeld, ex* kan uitblink, uitblink, voorbeeld, deskundige, ens.
2. ? (vraagteken) - Dit verteenwoordig 'n enkele karakter.Tromp kan byvoorbeeld Trump of Tramp beteken.
3. ~ (tilde) - Word gebruik om jokertekens (~, *, ?) in teks te identifiseer.

Jy kan die COUNTIF-funksie met jokertekens gebruik om selle te tel wanneer ander ingeboude telfunksies misluk.Gestel jy het byvoorbeeld 'n datastel wat soos volg lyk:

Kom ons neem nou 'n paar voorbeelde:

#1 Tel selle wat teks bevat

Om selle met teks daarin te tel, kan ons die jokerteken * (asterisk) gebruik.Aangesien sterretjies enige aantal karakters verteenwoordig, sal dit alle selle tel wat enige teks bevat.Hier is die formule:

=COUNTIFS($C$2:$C$11,"*")

Let wel: Die formule hierbo ignoreer selle wat getalle, leë selle en logiese waardes bevat, maar tel selle wat apostrofe bevat (en dus leeg lyk) of wat moontlik as deel van die formule ingesluit is.

Hier is 'n gedetailleerde handleiding oor die hantering van sake met leë stringe of apostrofe.

#2 Tel nie-leë selle

As jy dit oorweeg om die COUNTA-funksie te gebruik, dink weer.

Probeer dit, dit kan jou teleurstel.COUNTA sal ook selle tel wat leë stringe bevat (gewoonlik teruggestuur deur formules as ="", of wanneer mense net apostrofe in selle invoer).Selle wat leë stringe bevat blyk leeg te wees, maar is nie, so word getel deur die COUNTA-funksie.

COUNTA sal ook selle tel wat leë stringe bevat (gewoonlik teruggestuur deur formules as ="", of wanneer mense net apostrofe in selle invoer).Selle wat leë stringe bevat blyk leeg te wees, maar is nie, so word getel deur die COUNTA-funksie.

So as jy die formule = gebruik COUNTA (A1:A11), dit gee 11 terug wanneer dit 10 behoort terug te gee.

Hier is die regstelling:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

Kom ons verstaan ​​hierdie formule deur dit af te breek:

#3 Tel selle wat spesifieke teks bevat

Gestel ons wil alle selle tel waar die verkoopsverteenwoordigernaam met J begin.Dit kan maklik bereik word deur wildcards in die COUNTIF-funksie te gebruik.Hier is die formule:

=COUNTIFS($C$2:$C$11,"J*") 

Die voorwaarde J* spesifiseer dat die teks in die sel met J moet begin en enige aantal karakters kan bevat.

As jy selle wil tel wat letters op enige plek in die teks bevat, omring hulle met 'n asterisk.Byvoorbeeld, as jy selle wil tel wat die letter "a" bevat, gebruik *a* as die voorwaarde.

In vergelyking met my ander artikels is hierdie een buitengewoon lank.Hoop jy hou daarvan.Laat weet my wat jy dink deur 'n opmerking te los.