Excel heeft veel functies waarbij een gebruiker een enkele of meerdere criteria moet opgeven om het resultaat te krijgen. Als u bijvoorbeeld cellen wilt tellen op basis van meerdere criteria, kunt u de aantal.als of aantal. alss functies in Excel gebruiken.
deze zelfstudie behandelt verschillende manieren om een enkele of meerdere criteria te gebruiken in COUNTIF en COUNTIFS-functie in Excel.,
hoewel ik me in deze tutorial voornamelijk zal richten op aantal.als en aantal. als functies, kunnen al deze voorbeelden ook worden gebruikt in andere Excel functies die meerdere criteria als input gebruiken (zoals SUMIF, SUMIFS, AVERAGEIF, en AVERAGEIFS).
Deze Tutorial behandelt:
een inleiding tot Excel COUNTIF en COUNTIFS functies
laten we eerst grip krijgen op het gebruik van COUNTIF en COUNTIFS functies in Excel.,
Excel aantal. als-functie (neemt enkele Criteria)
Excel aantal. als-functie is het meest geschikt voor situaties waarin u cellen wilt tellen op basis van een enkel criterium. Als u op basis van meerdere criteria wilt tellen, gebruikt u de functie Aantal.alss.
syntaxis
=aantal. als (bereik, criteria)
Invoerargumenten
- bereik – het cellenbereik dat u wilt tellen.
- criteria-de criteria die moeten worden beoordeeld aan de hand van het cellenbereik om een cel te laten tellen.,
Excel COUNTIFS functie (neemt meerdere Criteria)
Excel COUNTIFS functie is het meest geschikt voor situaties waarin u cellen wilt tellen op basis van meerdere criteria.
syntaxis
=COUNTIFS (criteria_range1, criteria1, …)
Invoerargumenten
- criteria_range1 – het cellenbereik waarvoor u wilt evalueren aan de hand van criteria1.
- criteria1 – de criteria die u wilt evalueren voor criteria_range1 om te bepalen welke cellen moeten worden geteld.
- – het cellenbereik waarvoor u wilt evalueren aan de hand van criteria2.,
- – de criteria die u wilt evalueren voor criteria_range2 om te bepalen welke cellen moeten worden geteld.
laten we nu eens kijken naar enkele voorbeelden van het gebruik van meerdere criteria in COUNTIF-functies in Excel.
met behulp van GETALCRITERIA in Excel COUNTIF-functies
#1 Tel cellen wanneer Criteria gelijk zijn aan een waarde
om het aantal cellen te krijgen waarbij het criterium-argument gelijk is aan een opgegeven waarde, kunt u direct de criteria invoeren of de celverwijzing gebruiken die de criteria bevat.,
Hieronder is een voorbeeld waarin we de cellen tellen die het getal 9 bevatten (wat betekent dat het criterium gelijk is aan 9). Hier is de formule:
=COUNTIF($B$2:$B$11,D3)
in het bovenstaande voorbeeld (in de pic) staan de criteria in cel D3. U kunt de criteria ook rechtstreeks in de formule invoeren., U kunt bijvoorbeeld ook gebruik maken van:
=COUNTIF ($B $ 2:$B$11,9)
#2 Tel cellen wanneer Criteria groter zijn dan een waarde
om het aantal cellen te krijgen met een waarde groter dan een opgegeven waarde, gebruiken we de operator groter dan (“>”). We kunnen het direct in de formule gebruiken of een celverwijzing gebruiken die de criteria heeft.
wanneer we een operator gebruiken in criteria in Excel, moeten we deze binnen dubbele aanhalingstekens plaatsen., Bijvoorbeeld, als de criteria groter zijn dan 10, dan moeten we “>10” invoeren als de criteria (zie pic hieronder):
Hier is de formule:
=COUNTIF($B$2:$B$11,”>10″)
u kunt ook de criteria in een cel hebben en de celverwijzing als criteria gebruiken. In dit geval hoeft u de criteria niet tussen dubbele aanhalingstekens te plaatsen:
=COUNTIF($B$2:$B$11, D3)
Er kan ook een geval zijn wanneer u wilt dat de criteria in een cel staan,maar niet met de operator wilt., U wilt bijvoorbeeld dat de cel D3 het nummer 10 heeft en niet >10.
in dat geval moet u een criteria argument aanmaken dat een combinatie is van operator en celverwijzing (zie pic hieronder):
=COUNTIF($B$2:$B$11,”>”&D3)
NOTE: wanneer u een operator en een celverwijzing combineert, staat de operator altijd tussen dubbele aanhalingstekens. De operator en celverwijzing worden samengevoegd door een ampersand (&).,
#3 Tel cellen wanneer Criteria kleiner zijn dan een waarde
om het aantal cellen te krijgen met een waarde kleiner dan een opgegeven waarde, gebruiken we de operator minder dan (“<“). We kunnen het direct in de formule gebruiken of een celverwijzing gebruiken die de criteria heeft.
wanneer we een operator gebruiken in criteria in Excel, moeten we deze binnen dubbele aanhalingstekens plaatsen., Bijvoorbeeld, als het criterium is dat het getal kleiner moet zijn dan 5, dan moeten we “<5” invoeren als de criteria (zie pic hieronder):
=COUNTIF($B$2:$B$11,”<5″)
u kunt ook de criteria in een cel hebben en de celverwijzing als criteria gebruiken., In dit geval hoeft u de criteria niet tussen dubbele aanhalingstekens te plaatsen (zie de foto hieronder):
=COUNTIF($B$2:$B$11, D3)
ook kan er een geval zijn wanneer u wilt dat de criteria in een cel staan,maar niet met de operator. U wilt bijvoorbeeld dat de cel D3 het nummer 5 heeft en niet <5.,
In dat geval moet u een criterium argument aanmaken dat een combinatie is van operator en celverwijzing:
=COUNTIF($B$2:$B$11,”<“&D3)
opmerking: wanneer u een operator en een celverwijzing combineert, staat de operator altijd tussen dubbele aanhalingstekens. De operator en celverwijzing worden samengevoegd door een ampersand (&).,
#4 Tel cellen met meerdere Criteria-tussen twee waarden
om een aantal waarden tussen twee waarden te krijgen, moeten we meerdere criteria gebruiken in de COUNTIF-functie.
Hier zijn twee methoden om dit te doen:
methode 1: gebruik COUNTIFS functie
COUNTIFS functie kan meerdere criteria als argumenten verwerken en telt de cellen alleen als alle criteria waar zijn., Om cellen te tellen met waarden tussen twee opgegeven waarden (bijvoorbeeld 5 en 10), kunnen we de volgende COUNTIFS functie gebruiken:
=COUNTIFS($B$2:$B$11,”>5″,$B$2:$b$11,”<10″)
opmerking: de bovenstaande formule telt geen cellen die 5 of 10 bevatten. Als u deze cellen wilt opnemen, gebruik dan groter dan gelijk aan (>=) en kleiner dan gelijk aan (<=) operators., Hier is de formule:
=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″)
u kunt deze criteria ook in cellen hebben en de celverwijzing als criteria gebruiken. In dit geval hoeft u de criteria niet tussen dubbele aanhalingstekens te plaatsen (zie pic hieronder):
u kunt ook een combinatie van cellenverwijzingen en operators gebruiken (waarbij de operator direct in de formule wordt ingevoerd). Wanneer u een operator en een celverwijzing combineert, staat de operator altijd tussen dubbele aanhalingstekens., De operator en celverwijzing worden samengevoegd door een ampersand (&).
methode 2: Gebruik twee COUNTIF-functies
Als u meerdere criteria hebt, kunt u COUNTIFS gebruiken of een combinatie van COUNTIF-functies maken., De onderstaande formule zou ook hetzelfde doen:
=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)
In de bovenstaande formule vinden we eerst het aantal cellen dat een waarde groter dan 5 heeft en we trekken de telling van cellen met een waarde groter dan 10. Dit zou ons het resultaat geven als 5 (dat is het aantal cellen dat waarden meer dan 5 heeft en minder dan 10).,3c219a21b4″>
Als u wilt dat de formule om zowel de 5 en de 10, gebruikt u de volgende formule in de plaats:
=AANTAL.ALS($B$2:$B$11,”>=5″)-AANTAL.ALS($B$2:$B$11,”>10″)
Als u wilt dat de formule uit te sluiten van zowel de ‘5’ en ’10’ van het tellen, de volgende formule gebruiken:
=AANTAL.ALS($B$2:$B$11,”>=5″)-AANTAL.ALS($B$2:$B$11,”>10″)-AANTAL.ALS($B$2:$B$11,10)
U kunt deze criteria in de cellen en het gebruik van de cellen verwijzingen, of u kunt gebruik maken van een combinatie van operatoren en cellen verwijzingen.,
met behulp van TEKSTCRITERIA in Excel-functies
# 1 Tel cellen wanneer Criteria gelijk zijn aan een opgegeven tekst
om cellen te tellen die een exacte overeenkomst met de opgegeven tekst bevatten, kunnen we die tekst gewoon als criteria gebruiken. Bijvoorbeeld, in de dataset (hieronder getoond in de pic), als ik alle cellen met de naam Joe erin wil tellen, kan ik de onderstaande formule gebruiken:
=COUNTIF($B$2:$B$11,”Joe”)
aangezien dit een tekstreeks is, moet ik de tekstcriteria tussen dubbele aanhalingstekens plaatsen.,
U kunt ook de criteria in een cel hebben en dan die celverwijzing gebruiken (zoals hieronder getoond):
=COUNTIF($B$2:$B$11,E3)
Opmerking: U kunt verkeerde resultaten krijgen als er een/spaties aan het einde van het criteria-of criteriumbereik. Zorg ervoor dat u de gegevens schoonmaakt voordat u deze formules gebruikt.
#2 Tel cellen wanneer Criteria niet gelijk zijn aan een opgegeven tekst
vergelijkbaar met wat we in het bovenstaande voorbeeld zagen, kunt u ook cellen tellen die geen opgegeven tekst bevatten., Hiervoor moeten we de not equal to operator gebruiken (<>).
stel dat u alle cellen wilt tellen die de naam JOE niet bevatten, hier is de formule die het zal doen:
=COUNTIF($B$2:$B$11,”<>Joe”)
U kunt ook de criteria in een cel hebben en de celverwijzing als criteria gebruiken., In dit geval hoeft u de criteria niet tussen dubbele aanhalingstekens te plaatsen (zie de foto hieronder):
=COUNTIF($B$2:$B$11, E3)
Er kan ook een geval zijn wanneer u wilt dat de criteria in een cel staan maar niet met de operator wilt. U wilt bijvoorbeeld dat de cel D3 de naam Joe heeft en niet <>Joe.,
in dat geval moet u een criteria argument aanmaken dat een combinatie is van operator en celverwijzing (zie pic hieronder):
=COUNTIF($B$2:$B$11,”<>”&E3)
wanneer u een operator en een celverwijzing combineert, staat de operator altijd tussen dubbele aanhalingstekens. De operator en celverwijzing worden samengevoegd door een ampersand (&).
Datumcriteria gebruiken in Excel COUNTIF-en COUNTIFS-functies
Excel slaat datum en tijd op als getallen., Dus we kunnen het gebruiken op dezelfde manier als we getallen gebruiken.
#1 Tel cellen als Criteria gelijk zijn aan een opgegeven datum
om het aantal cellen te krijgen dat de opgegeven datum bevat, gebruiken we de equal to operator (=) samen met de datum.
om de datum te gebruiken, raad ik aan om de functie datum te gebruiken, omdat het elke mogelijkheid van fouten in de datumwaarde elimineert. Dus als ik bijvoorbeeld de datum 1 September 2015 wil gebruiken, kan ik de functie Datum gebruiken zoals hieronder getoond:
=DATE(2015,9,1)
deze formule zou dezelfde datum retourneren ondanks regionale verschillen., Bijvoorbeeld, 01-09-2015 zou 1 September 2015 volgens de Amerikaanse datum syntaxis en 09 januari 2015 volgens de Britse datum syntaxis. Echter, deze formule zou altijd terugkeren 1 September 2105.
Hier is de formule om het aantal cellen te tellen dat de datum 02-09-2015 bevat:
=COUNTIF($A$2:$A$11,DATE(2015,9,2))
#2 Tel cellen wanneer Criteria voor of na een opgegeven datum liggen
om cellen te tellen die datum vóór of na een opgegeven datum bevatten, kunnen we de minder dan/groter dan operators gebruiken.,
als ik bijvoorbeeld alle cellen wil tellen die een datum na 02 September 2015 bevatten, kan ik de formule gebruiken:
=COUNTIF($A$2:$a$11,”>”&DATE(2015,9,2))
op dezelfde manier kunt u ook het aantal cellen vóór een opgegeven datum tellen. Als u een datum wilt opnemen in de telling, gebruik en ‘gelijk aan’ operator samen met ‘groter dan/kleiner dan’ operator.
u kunt ook een celverwijzing gebruiken die een datum bevat., In dit geval moet u de operator (Binnen dubbele aanhalingstekens) combineren met de datum met behulp van een ampersand (&).
Zie voorbeeld hieronder:
=COUNTIF($A$2:$A$11,”>”&F3)
#3 Tel cellen met meerdere Criteria – tussen twee datums
om een aantal waarden tussen twee waarden te krijgen, moeten we meerdere criteria gebruiken in de COUNTIF-functie.
we kunnen dit doen met behulp van twee methoden – een enkele functie Aantal.als of twee functies.,
methode 1: gebruik COUNTIFS functie
COUNTIFS functie kan meerdere criteria als argumenten nemen en telt de cellen alleen als alle criteria waar zijn. Cellen tellen, met waarden tussen twee opgegeven data (zeg 2 September en 7 September), kunnen we gebruik maken van de volgende COUNTIFS functie:
=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2);$A$2:$A$11,”<“&DATE(2015,9,7))
De bovenstaande formule tellen niet mee voor de cellen met de opgegeven data., Als u deze datums ook wilt opnemen, gebruik dan groter dan gelijk aan (>=) en kleiner dan gelijk aan (<=) operators. Hier is de formule:
=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$a$2:$a$11,”<=”&date(2015,9,7))
U kunt ook de datums in een cel hebben en de celverwijzing als criteria gebruiken. In dit geval kunt u de operator met de datum niet in de cellen hebben., U moet handmatig operators in de formule toevoegen (in dubbele aanhalingstekens) en celverwijzing toevoegen met behulp van een ampersand (&). Zie de foto hieronder:
=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)
METHODE 2: Gebruik de functie AANTAL.ALS functies
Als u meerdere criteria, kunt u gebruik maken van een COUNTIFS functie of maak een combinatie van twee AANTAL.ALS functies., De onderstaande formule zou ook de truc doen:
=AANTAL.ALS($A$2:$A$11,”>”&DATE(2015,9,2))-AANTAL.ALS($A$2:$A$11,”>”&DATE(2015,9,7))
In de bovenstaande formule, die we eerst het aantal cellen die zijn voorzien van een datum na 2 September en we trekken het tellen van cellen met een datum na 7 September. Dit zou ons het resultaat geven als 7 (dat is het aantal cellen dat Data heeft na 2 September en op of voor 7 September).,>DATUM(2015,9,2))-AANTAL.ALS($A$2:$A$11,”>”&DATE(2015,9,7))
Als u wilt uitsluiten zowel de data van het tellen, gebruik je de volgende formule:
=AANTAL.ALS($A$2:$A$11,”>”&DATE(2015,9,2))-AANTAL.ALS($A$2:$A$11,”>”&DATE(2015,9,7)-AANTAL.ALS($A$2:$A$11,DATUM(2015,9,7)))
u kunt Ook de criteria voor data in de cellen en het gebruik van de cellen verwijzingen (samen met de operators in dubbele aanhalingstekens met behulp van het en-teken).,
jokertekens gebruiken in Criteria in aantal. als & COUNTIFS functies
Er zijn drie jokertekens in Excel:
U kunt de COUNTIF-functie met jokertekens gebruiken om cellen te tellen wanneer andere ingebouwde count-functie mislukt. Stel dat u bijvoorbeeld een gegevensset hebt zoals hieronder getoond:
laten we nu verschillende voorbeelden nemen:
#1 Tel cellen die tekst bevatten
om cellen met tekst te tellen, kunnen we het jokerteken * (sterretje) gebruiken., Aangezien asterisk een willekeurig aantal tekens vertegenwoordigt, zou het alle cellen tellen die tekst bevatten. Hier is de formule:
=COUNTIFS($C$2:$C$11,”*”)
opmerking: de bovenstaande formule negeert cellen die getallen, lege cellen en logische waarden bevatten, maar zou tellen dat de cellen een apostrof bevatten (en dus leeg lijken) of cellen die lege tekenreeks bevatten (=””) die mogelijk zijn geretourneerd als onderdeel van een formule.
Hier is een gedetailleerde tutorial over het afhandelen van gevallen waarin er een lege string of apostrof is.,
Hier is een gedetailleerde handleiding over het afhandelen van gevallen waarin er lege tekenreeksen of apostrofen zijn.
Hieronder is een video die verschillende scenario ‘ s uitlegt van het tellen van cellen met tekst erin.
#2 Tel niet-lege cellen
Als u de COUNTA-functie wilt gebruiken, denk dan opnieuw.
probeer het en het kan mislukken. COUNTA zal ook een cel tellen die een lege tekenreeks bevat (vaak geretourneerd door formules als =”” of wanneer mensen alleen een apostrof in een cel invoeren). Cellen die lege tekenreeksen bevatten, zien er leeg uit, maar zijn dat niet, en worden dus geteld door de COUNTA-functie.,
COUNTA zal ook een cel tellen die een lege tekenreeks bevat (vaak geretourneerd door formules als =”” of wanneer mensen alleen een apostrof in een cel invoeren). Cellen die lege tekenreeksen bevatten, zien er leeg uit, maar zijn dat niet, en worden dus geteld door de COUNTA-functie.
dus als je de formule =COUNTA(A1:A11) gebruikt, geeft het 11 terug, terwijl het 10 zou moeten teruggeven.
Hier is de oplossing:
=COUNTIF ($A$1:$A $ 11,”?*”) + COUNT ($A$1:$A$11)+SUMPRODUCT (- ISLOGICAL ($A$1:$A$11))
laten we deze formule begrijpen door het op te splitsen:
- COUNTIF($n$8:$n$18,”?,* “)- Dit deel van de formule geeft 5 terug. Dit geldt ook voor elke cel met een tekstkarakter erin. A ? vertegenwoordigt één teken en * vertegenwoordigt een willekeurig aantal tekens. Vandaar de combinatie ?* in de criteria dwingt excel om cellen te tellen die ten minste één tekstteken bevatten.
- aantal($A$1:$A$11) – dit telt alle cellen die getallen bevatten. In het bovenstaande voorbeeld geeft het 3 terug.
- SUMPRODUCT(–ISLOGICAL($a$1:$A$11) – dit telt alle cellen die logische waarden bevatten. In het bovenstaande voorbeeld geeft het 2 terug.,
#3 Tel cellen die specifieke tekst bevatten
laten we zeggen dat we alle cellen willen tellen waar de naam van de vertegenwoordiger begint met J. Dit kan gemakkelijk worden bereikt door een jokerteken te gebruiken in COUNTIF-functie. Hier is de formule:
=COUNTIFS($C$2:$C$11,”J*”)
de criteria J* geeft aan dat de tekst in een cel moet beginnen met J en kan elk aantal tekens bevatten.
Als u cellen wilt tellen die het alfabet ergens in de tekst bevatten, moet u aan beide zijden een sterretje aanbrengen., Als u bijvoorbeeld cellen wilt tellen die het alfabet “a” bevatten, gebruikt u *a* als criteria.