Meerdere criteria gebruiken in Excel AANTAL.ALS en AANTAL.ALS

Excel heeft veel functies waarbij een gebruiker een of meerdere criteria moet specificeren om het resultaat te krijgen. Als u bijvoorbeeld cellen wilt tellen op basis van meerdere criteria, kunt u de functies AANTAL.ALS of AANTAL.ALS in Excel gebruiken.

Deze zelfstudie behandelt verschillende manieren om een ​​​​enkele of meerdere criteria te gebruiken in de AANTAL.ALS- en AANTALLEN.ALS-functie in Excel.

Hoewel ik me in deze tutorial voornamelijk zal concentreren op de functies AANTAL.ALS en AANTAL.ALS, kunnen al deze voorbeelden ook worden gebruikt in andere Excel-functies die meerdere criteria als invoer gebruiken (zoals SUMIF, SUMIFS, AVERAGEIF en AVERAGEIFS).

Een inleiding tot de Excel-functies AANTAL.ALS en AANTAL.ALS

Laten we eerst grip krijgen op het gebruik van AANTAL.ALS en AANTAL.ALS-functies in Excel.

Excel AANTAL.ALS-functie (hiervoor zijn enkele criteria nodig)

Excel AANTAL.ALS-functie is het meest geschikt voor situaties waarin u cellen wilt tellen op basis van een enkel criterium. Als u wilt tellen op basis van meerdere criteria, gebruikt u de AANTAL.ALS-functie.

Syntaxis

=AANTAL.ALS(bereik, criteria)

Invoerargumenten

• bereik - het celbereik dat u wilt tellen.
• criteria - de criteria die moeten worden beoordeeld aan de hand van het cellenbereik voordat een cel kan worden geteld.

Excel COUNTIFS-functie (hiervoor zijn meerdere criteria nodig)

Excel COUNTIFS-functie is het meest geschikt voor situaties waarin u cellen wilt tellen op basis van meerdere criteria.

Syntaxis

=AANTAL.ALS(criteria_bereik1, criteria1, [criteria_bereik2, criteria2]… )

Invoerargumenten

• criteria_bereik1 - Het cellenbereik waarvoor u wilt evalueren op basis van criteria1.
• criteria1 - de criteria die u wilt evalueren voor criteria_bereik1 om te bepalen welke cellen moeten worden geteld.
• [criteria_bereik2] - Het cellenbereik waarvoor u wilt evalueren op basis van criteria2.
• [criteria2] - de criteria die u wilt evalueren voor criteria_bereik2 om te bepalen welke cellen moeten worden geteld.

Laten we nu eens kijken naar enkele voorbeelden van het gebruik van meerdere criteria in AANTAL.ALS-functies in Excel.

NUMBER criteria gebruiken in Excel AANTAL.ALS-functies

#1 Tel cellen wanneer criteria GELIJK zijn aan een waarde

Om het aantal cellen te krijgen waarbij het criteriaargument gelijk is aan een opgegeven waarde, kunt u de criteria rechtstreeks invoeren of de celverwijzing gebruiken die de criteria bevat.

Hieronder ziet u een voorbeeld waarin we de cellen tellen die het getal 9 bevatten (wat betekent dat het criteriumargument gelijk is aan 9). Hier is de formule:

=AANTAL.ALS($B$2:$B$11,D3)

In het bovenstaande voorbeeld (in de foto) staan ​​de criteria in cel D3. U kunt de criteria ook rechtstreeks in de formule invoeren. U kunt bijvoorbeeld ook gebruiken:

=AANTAL.ALS($B$2:$B$11,9)

#2 Tel cellen wanneer criteria GROTER zijn dan een waarde

Om het aantal cellen te krijgen met een waarde die groter is dan een opgegeven waarde, gebruiken we de operator groter dan (">"). We kunnen het rechtstreeks in de formule gebruiken of een celverwijzing gebruiken die de criteria heeft.

Telkens wanneer we een operator gebruiken in criteria in Excel, moeten we deze tussen dubbele aanhalingstekens plaatsen. Als de criteria bijvoorbeeld groter zijn dan 10, moeten we "> 10" invoeren als de criteria (zie onderstaande afbeelding):

Hier is de formule:

=AANTAL.ALS($B$2:$B$11,”>10″)

U kunt de criteria ook in een cel plaatsen en de celverwijzing als de criteria gebruiken. In dit geval hoeft u de criteria NIET tussen dubbele aanhalingstekens te plaatsen:

=AANTAL.ALS($B$2:$B$11,D3)

Er kan ook een geval zijn waarin u wilt dat de criteria in een cel staan, maar niet bij de operator. U wilt bijvoorbeeld dat cel D3 het getal 10 heeft en niet >10.

In dat geval moet u een criteriumargument maken dat een combinatie is van operator en celverwijzing (zie onderstaande afbeelding):

=AANTAL.ALS($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 vergezeld door een ampersand (&).

#3 Tel cellen wanneer criteria MINDER DAN een waarde zijn

Om het aantal cellen te krijgen met een waarde die kleiner is dan een opgegeven waarde, gebruiken we de operator kleiner dan ("<"). We kunnen het rechtstreeks in de formule gebruiken of een celverwijzing gebruiken die de criteria heeft.

Telkens wanneer we een operator gebruiken in criteria in Excel, moeten we deze tussen dubbele aanhalingstekens plaatsen. Als het criterium bijvoorbeeld is dat het aantal kleiner moet zijn dan 5, dan moeten we "<5" invoeren als het criterium (zie onderstaande afbeelding):

=AANTAL.ALS($B$2:$B$11,”<5″)

U kunt de criteria ook in een cel plaatsen en de celverwijzing als de criteria gebruiken. In dit geval hoeft u de criteria NIET tussen dubbele aanhalingstekens te plaatsen (zie onderstaande afbeelding):

=AANTAL.ALS($B$2:$B$11,D3)

Er kan ook een geval zijn waarin u wilt dat de criteria in een cel staan, maar niet bij de operator. U wilt bijvoorbeeld dat cel D3 het getal 5 heeft en niet <5.

In dat geval moet u een criteriumargument maken dat een combinatie is van operator en celverwijzing:

=AANTAL.ALS($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 vergezeld door een ampersand (&).

#4 Cellen tellen met meerdere criteria - tussen twee waarden

Om een ​​telling van waarden tussen twee waarden te krijgen, moeten we meerdere criteria gebruiken in de AANTAL.ALS-functie.

Hier zijn twee methoden om dit te doen:

METHODE 1: De functie AANTALLEN.ALS gebruiken:

De functie AANTAL.ALS 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 (zeg 5 en 10), kunnen we de volgende AANTAL.ALS-functie gebruiken:

=AANTALLEN.($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, gebruikt u de operatoren groter dan gelijk aan (>=) en kleiner dan gelijk aan (<=). Hier is de formule:

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

U kunt deze criteria ook in cellen hebben en de celverwijzing als de criteria gebruiken. In dit geval hoeft u de criteria NIET tussen dubbele aanhalingstekens te plaatsen (zie onderstaande afbeelding):

U kunt ook een combinatie van celverwijzingen 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 vergezeld door een ampersand (&).

METHODE 2: Twee AANTAL.ALS-functies gebruiken

Als u meerdere criteria heeft, kunt u AANTAL.ALS gebruiken of een combinatie van AANTAL.ALS-functies maken. De onderstaande formule zou ook hetzelfde doen:

=AANTAL.ALS($B$2:$B$11,”>5″)-AANTAL.ALS($B$2:$B$11,”>10″)

In de bovenstaande formule vinden we eerst het aantal cellen met een waarde groter dan 5 en trekken we het aantal cellen af ​​met een waarde groter dan 10. Dit zou ons het resultaat geven als 5 (wat het aantal cellen is dat waarden groter dan 5 en kleiner dan gelijk aan 10).

Als u wilt dat de formule zowel 5 als 10 bevat, gebruikt u in plaats daarvan de volgende formule:

=AANTAL.ALS($B$2:$B$11,”>=5″)-AANTAL.ALS($B$2:$B$11,”>10″)

Als u wilt dat de formule zowel '5' als '10' van de telling uitsluit, gebruikt u de volgende formule:

=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 cellen hebben en de celverwijzingen gebruiken, of u kunt een combinatie van operators en celverwijzingen gebruiken.

TEKST-criteria gebruiken in Excel-functies

#1 Tel cellen wanneer criteria GELIJK zijn aan een gespecificeerde tekst

Om cellen te tellen die een exacte overeenkomst met de opgegeven tekst bevatten, kunnen we die tekst gewoon als de criteria gebruiken. Als ik bijvoorbeeld in de dataset (hieronder in de afbeelding weergegeven) alle cellen met de naam Joe erin wil tellen, kan ik de onderstaande formule gebruiken:

=AANTAL.ALS($B$2:$B$11,”Joe”)

Aangezien dit een tekenreeks is, moet ik de tekstcriteria tussen dubbele aanhalingstekens plaatsen.

U kunt de criteria ook in een cel plaatsen en vervolgens die celverwijzing gebruiken (zoals hieronder weergegeven):

=AANTAL.ALS($B$2:$B$11,E3)

OPMERKING: U kunt verkeerde resultaten krijgen als er voorloop-/naloopspaties in het criteria- of criteriabereik zijn. Zorg ervoor dat u de gegevens opschoont voordat u deze formules gebruikt.

#2 Tel cellen wanneer criteria NIET GELIJK zijn aan een gespecificeerde tekst

Net als wat we in het bovenstaande voorbeeld zagen, kun je ook cellen tellen die geen specifieke tekst bevatten. Om dit te doen, moeten we de operator niet gelijk aan () gebruiken.

Stel dat je alle cellen wilt tellen die niet de naam JOE bevatten, dan is hier de formule die het zal doen:

=AANTAL.ALS($B$2:$B$11,”Joe”)

U kunt de criteria ook in een cel plaatsen en de celverwijzing als de criteria gebruiken. In dit geval hoeft u de criteria NIET tussen dubbele aanhalingstekens te plaatsen (zie onderstaande afbeelding):

=AANTAL.ALS($B$2:$B$11,E3)

Er kan ook een geval zijn waarin u wilt dat de criteria in een cel staan, maar niet bij de operator. U wilt bijvoorbeeld dat de cel D3 de naam Joe heeft en niet Joe.

In dat geval moet u een criteriumargument maken dat een combinatie is van operator en celverwijzing (zie onderstaande afbeelding):

=AANTAL.ALS($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 vergezeld door een ampersand (&).

DATE-criteria gebruiken in Excel COUNTIF- en COUNTIFS-functies

Excel slaat datum en tijd op als getallen. Dus we kunnen het op dezelfde manier gebruiken als getallen.

#1 Tel cellen wanneer criteria GELIJK zijn aan een gespecificeerde datum

Om het aantal cellen te krijgen die de opgegeven datum bevatten, gebruiken we de operator gelijk aan (=) samen met de datum.

Om de datum te gebruiken, raad ik aan om de DATE-functie te gebruiken, omdat het elke mogelijkheid van fouten in de datumwaarde wegneemt. Dus als ik bijvoorbeeld de datum 1 september 2015 wil gebruiken, kan ik de DATE-functie gebruiken zoals hieronder weergegeven:

=DATUM(2015,9,1)

Deze formule zou dezelfde datum retourneren ondanks regionale verschillen. 01-09-2015 zou bijvoorbeeld 1 september 2015 zijn volgens de Amerikaanse datumsyntaxis en 09 januari 2015 volgens de Britse datumsyntaxis. Deze formule zou echter altijd 1 september 2105 retourneren.

Hier is de formule om het aantal cellen te tellen met de datum 02-09-2015:

=AANTAL.ALS($A$2:$A$11,DATUM(2015,9,2))

#2 Tel cellen wanneer criteria VOOR of NA zijn tot een gespecificeerde datum

Om cellen te tellen die een datum voor of na een bepaalde datum bevatten, kunnen we de operatoren kleiner dan/groter dan gebruiken.

Als ik bijvoorbeeld alle cellen wil tellen die een datum bevatten die na 2 september 2015 valt, kan ik de formule gebruiken:

=AANTAL.ALS($A$2:$A$11,”>”&DATUM(2015,9,2))

Op dezelfde manier kunt u ook het aantal cellen tellen vóór een bepaalde datum. Als u een datum in de telling wilt opnemen, gebruikt u 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 (tussen dubbele aanhalingstekens) combineren met de datum met een ampersand (&).

Zie voorbeeld hieronder:

=AANTAL.ALS($A$2:$A$11,”>”&F3)

#3 Tel cellen met meerdere criteria - tussen twee datums

Om een ​​telling van waarden tussen twee waarden te krijgen, moeten we meerdere criteria gebruiken in de AANTAL.ALS-functie.

We kunnen dit op twee manieren doen: één enkele AANTAL.ALS-functie of twee AANTAL.ALS-functies.

METHODE 1: De functie AANTALLEN.ALS gebruiken:

De functie AANTAL.ALS kan meerdere criteria als argumenten gebruiken en telt de cellen alleen als alle criteria WAAR zijn. Om cellen te tellen met waarden tussen twee gespecificeerde datums (zeg 2 september en 7 september), kunnen we de volgende AANTALLEN.ALS-functie gebruiken:

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

De bovenstaande formule telt geen cellen die de opgegeven datums bevatten. Als u deze datums ook wilt opnemen, gebruikt u de operatoren groter dan gelijk aan (>=) en kleiner dan gelijk aan (<=). Hier is de formule:

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

U kunt de datums ook in een cel plaatsen en de celverwijzing als criterium gebruiken. In dit geval kunt u de operator met de datum niet in de cellen hebben. U moet handmatig operators toevoegen aan de formule (tussen dubbele aanhalingstekens) en celverwijzingen toevoegen met een ampersand (&). Zie de foto hieronder:

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

METHODE 2: AANTAL.ALS-functies gebruiken

Als u meerdere criteria heeft, kunt u één AANTAL.ALS-functie gebruiken of een combinatie van twee AANTAL.ALS-functies maken. 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 vinden we eerst het aantal cellen met een datum na 2 september en trekken we het aantal cellen af ​​met datums na 7 september. Dit zou ons het resultaat geven als 7 (wat het aantal cellen is met datums na 2 september en op of voor 7 september).

Als u niet wilt dat de formule zowel op 2 september als op 7 september meetelt, gebruikt u in plaats daarvan de volgende formule:

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

Als u beide datums wilt uitsluiten van het tellen, gebruikt u de volgende formule:

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

U kunt ook de datums van de criteria in cellen hebben en de celverwijzingen gebruiken (samen met operators tussen dubbele aanhalingstekens die worden samengevoegd met ampersand).

WILDCARD-tekens gebruiken in criteria in AANTAL.ALS & AANTAL.ALS-functies

Er zijn drie jokertekens in Excel:

1. * (sterretje) - Het vertegenwoordigt een willekeurig aantal tekens. Ex* kan bijvoorbeeld betekenen excel, excels, example, expert, etc.
2. ? (vraagteken) - Het vertegenwoordigt één enkel teken. Tr?mp kan bijvoorbeeld Trump of Vagebond betekenen.
3. ~ (tilde) - Het wordt gebruikt om een ​​jokerteken (~, *, ?) in de tekst te identificeren.

U kunt de AANTAL.ALS-functie gebruiken met jokertekens om cellen te tellen wanneer een andere ingebouwde telfunctie faalt. Stel dat u een gegevensset heeft zoals hieronder weergegeven:

Laten we nu verschillende voorbeelden nemen:

#1 Tel cellen die tekst bevatten

Om cellen met tekst erin te tellen, kunnen we het jokerteken * (sterretje) gebruiken. Aangezien een asterisk een willekeurig aantal tekens vertegenwoordigt, telt het alle cellen met tekst erin. Hier is de formule:

=AANTALLEN.ALS($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 een lege tekenreeks (=””) bevatten die mogelijk is geretourneerd als een onderdeel van een formule.

Hier is een gedetailleerde zelfstudie over het omgaan met gevallen waarin er een lege tekenreeks of apostrof is.

Hier is een gedetailleerde tutorial over het omgaan met gevallen waarin er lege strings of apostrofs zijn.

Hieronder vindt u een video waarin verschillende scenario's worden uitgelegd voor het tellen van cellen met tekst erin.

#2 Tel niet-lege cellen

Als u overweegt om de AANTAL-functie te gebruiken, denk dan nog eens goed na.

Probeer het en het kan je mislukken. COUNTA telt ook een cel 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 blanco uit, maar zijn dat niet, en worden dus geteld door de functie AANTALLEN.

COUNTA telt ook een cel 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 blanco uit, maar zijn dat niet, en worden dus geteld door de functie AANTALLEN.

Dus als u de formule =COUNTA(A1:A11) gebruikt, geeft deze 11 terug, terwijl deze 10 zou moeten retourneren.

Hier is de oplossing:

=AANTAL.ALS($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SOMPRODUCT(-ISLOGICAL($A$1:$A$11))

Laten we deze formule begrijpen door hem op te splitsen:

• AANTAL.ALS($N$8:$N$18,”?*”) - Dit deel van de formule retourneert 5. Dit omvat elke cel met een tekstteken erin. EEN ? staat voor één teken en * staat voor een willekeurig aantal tekens. Vandaar dat de combinatie ?* in de criteria dwingt uit te blinken om cellen te tellen die ten minste één tekstteken bevatten.
• COUNT($A$1:$A$11) - Dit telt alle cellen die getallen bevatten. In het bovenstaande voorbeeld retourneert het 3.
• SOMPRODUCT(-ISLOGISCH($A$1:$A$11) - Dit telt alle cellen die logische waarden bevatten. In het bovenstaande voorbeeld wordt 2 geretourneerd.

#3 Tel cellen die specifieke tekst bevatten

Laten we zeggen dat we alle cellen willen tellen waar de naam van de verkoper begint met J. Dit kan eenvoudig worden bereikt door een jokerteken te gebruiken in de AANTAL.ALS-functie. Hier is de formule:

=AANTALLEN.ALS($C$2:$C$11,”J*”)

Het criterium J* geeft aan dat de tekst in een cel moet beginnen met J en een willekeurig aantal tekens mag bevatten.

Als u cellen wilt tellen die het alfabet ergens in de tekst bevatten, flankeer deze dan met een asterisk aan beide kanten. Als u bijvoorbeeld cellen wilt tellen die het alfabet "a" bevatten, gebruikt u *a* als criterium.

Dit artikel is ongewoon lang in vergelijking met mijn andere artikelen. Hoop dat je ervan genoten hebt. Laat me je mening weten door een reactie achter te laten.

Mogelijk vindt u de volgende Excel-zelfstudies ook nuttig:

• Tel het aantal woorden in Excel.
• Tel cellen op basis van achtergrondkleur in Excel.
• Een kolom optellen in Excel (5 heel eenvoudige manieren)