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

Excel heeft veel functies waarbij de gebruiker een of meer voorwaarden 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 enkele of meerdere criteria te gebruiken in de functies AANTAL.ALS en AANTAL.ALS.

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

Inleiding tot de Excel-functies AANTAL.ALS en AANTAL.ALS

Laten we eerst begrijpen hoe we de functies AANTAL.ALS en AANTAL.ALS in Excel kunnen gebruiken.

Excel AANTAL.ALS-functie (met een enkel criterium)

De Excel AANTAL.ALS-functie werkt het beste als u cellen wilt tellen op basis van één criterium.Als u wilt tellen op basis van meerdere criteria, gebruikt u de AANTAL.ALS-functie.

syntaxis

=AANTAL.ALS(bereik, voorwaarde)

Invoerparameters

• domein -  Het cellenbereik dat u wilt berekenen.
• voorwaarde -Criteria die moeten worden beoordeeld op basis van het aantal te tellen cellen.

Excel AANTAL.ALS-functie (met meerdere criteria)

De Excel AANTAL.ALS-functie werkt het beste als u cellen wilt tellen op basis van meerdere criteria.

syntaxis

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

Invoerparameters

• criteria_bereik1 – Het cellenbereik dat u wilt evalueren op basis van criterium 1.
• criteria1 – Het criterium dat u wilt evalueren is criterium_bereik1 om te bepalen welke cellen moeten worden geëvalueerd.
• [criteria_bereik2] – Het cellenbereik dat u wilt evalueren op basis van criterium 2.
• [criterium2] – Het criterium dat u wilt evalueren, is criteriumbereik2 om te bepalen welke cellen moeten worden geteld.

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

Gebruik de NUMBER-voorwaarde in de Excel AANTAL.ALS-functie

#1 Tel cellen wanneer criterium gelijk is aan een waarde

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

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

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

In het bovenstaande voorbeeld (in de afbeelding) staat het criterium in cel D3.U kunt voorwaarden ook rechtstreeks in formules invoeren.U kunt bijvoorbeeld ook gebruiken:

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

#2 Tel cellen wanneer de voorwaarde groter is dan de 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 formules gebruiken of we kunnen celverwijzingen met voorwaarden gebruiken.

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

Hier is de formule:

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

U kunt ook voorwaarden in cellen opnemen en celverwijzingen als voorwaarden gebruiken.In dit geval hoeft u de voorwaarde niet tussen dubbele aanhalingstekens te zetten:

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

Dit kan ook het geval zijn wanneer u een voorwaarde in een cel wilt hebben, maar deze niet met een operator wilt gebruiken.U wilt bijvoorbeeld dat het getal in cel D3 10 is in plaats van >10.

In dit geval moet u een voorwaardelijke parameter maken, die een combinatie is van operator en celverwijzing (zie onderstaande afbeelding):

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

Opmerking: Wanneer u operators en celverwijzingen combineert, worden de operators altijd tussen dubbele aanhalingstekens geplaatst.Operators en celverwijzingen zijn verbonden door een ampersand (&).

#3 Tel cellen wanneer criterium kleiner is dan een waarde

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 formules gebruiken of we kunnen celverwijzingen met voorwaarden gebruiken.

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

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

U kunt ook voorwaarden in cellen opnemen en celverwijzingen als voorwaarden gebruiken.In dit geval hoef je de standaard niet tussen dubbele aanhalingstekens te zetten (zie onderstaande afbeelding):

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

U wilt mogelijk ook dat de voorwaarde in de cel staat, maar niet bij de operator.U wilt bijvoorbeeld dat cel D3 het getal 5 heeft in plaats van <5.

In dit geval moet u een voorwaardelijke parameter maken die een combinatie is van operator en celverwijzing:

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

Opmerking: Wanneer u operators en celverwijzingen combineert, worden de operators altijd tussen dubbele aanhalingstekens geplaatst.Operators en celverwijzingen zijn verbonden door een ampersand (&).

#4 Tel cellen met meerdere criteria – tussen twee waarden

Om het aantal waarden tussen twee waarden te krijgen, moeten we meerdere voorwaarden gebruiken in de AANTAL.ALS-functie.

Hier zijn twee manieren om dit te doen:

Methode XNUMX: Gebruik de AANTAL.ALS-functie

De functie AANTAL.ALS kan meerdere voorwaarden als argumenten verwerken en cellen tellen alleen als alle voorwaarden WAAR zijn.Om cellen te tellen met waarden tussen twee opgegeven waarden, zoals 5 en 10, kunnen we de volgende AANTAL.ALS-functie gebruiken:

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

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

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

U kunt deze voorwaarden ook in cellen opnemen en celverwijzingen als voorwaarden gebruiken.In dit geval hoef je de standaard niet tussen dubbele aanhalingstekens te zetten (zie onderstaande afbeelding):

U kunt ook een combinatie van celverwijzingen en operators gebruiken (operators rechtstreeks in formules invoeren).Bij het combineren van operatoren en celverwijzingen worden de operatoren altijd tussen dubbele aanhalingstekens geplaatst.Operators en celverwijzingen zijn verbonden door een ampersand (&).

Methode 2: Gebruik twee AANTAL.ALS-functies

Als u meerdere voorwaarden heeft, kunt u AANTAL.ALS gebruiken of een combinatie van AANTAL.ALS-functies maken.De volgende formule doet hetzelfde:

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

In de bovenstaande formule vinden we eerst het aantal cellen met een waarde groter dan 5 en trekken we vervolgens het aantal cellen af ​​met een waarde groter dan 10.Dit geeft ons een resultaat van 5 (dwz met waarden groter dan 5 en kleiner dan of gelijk aan 10).

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

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

Als u wilt dat de formule "5" en "10" uitsluit van de telling, gebruikt u de volgende formule:

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

U kunt deze voorwaarden in cellen opnemen en celverwijzingen gebruiken, of u kunt een combinatie van operators en celverwijzingen gebruiken.

TEKST-voorwaarden gebruiken in Excel-functies

#1 Tel cellen wanneer de voorwaarde gelijk is aan de opgegeven tekst

Om cellen te tellen die exact overeenkomen met een opgegeven tekst, kunnen we die tekst gewoon als voorwaarde gebruiken.Als ik bijvoorbeeld in een dataset (hieronder weergegeven) alle cellen met de naam Joe wilde tellen, zou ik de volgende formule kunnen gebruiken:

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

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

U kunt ook een voorwaarde in een cel opnemen 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 de voorwaarde of het voorwaardebereik zijn.Zorg ervoor dat u de gegevens opschoont voordat u deze formules gebruikt.

#2 Tel cellen wanneer de voorwaarde niet gelijk is aan de opgegeven tekst

Net als wat we in het bovenstaande voorbeeld zagen, kun je ook cellen tellen die geen gespecificeerde tekst bevatten.Om dit te doen, moeten we de not equals-operator (<>) gebruiken.

Ervan uitgaande dat u alle cellen wilt tellen die niet de naam JOE bevatten, is hier een formule die dit kan:

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

U kunt ook voorwaarden in cellen opnemen en celverwijzingen als voorwaarden gebruiken.In dit geval hoef je de standaard niet tussen dubbele aanhalingstekens te zetten (zie onderstaande afbeelding):

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

Dit kan ook het geval zijn wanneer u een voorwaarde in een cel wilt hebben, maar deze niet met een operator wilt gebruiken.U wilt bijvoorbeeld dat cel D3 de naam Joe heeft in plaats van <>Joe.

In dit geval moet u een voorwaardelijke parameter maken, die een combinatie is van operator en celverwijzing (zie onderstaande afbeelding):

=AANTAL.ALS($B$2:$B$11,”<>”&E3)

Bij het combineren van operatoren en celverwijzingen worden de operatoren altijd tussen dubbele aanhalingstekens geplaatst.Operators en celverwijzingen zijn verbonden door een ampersand (&).

DATE-voorwaarden gebruiken in Excel AANTAL.ALS- en AANTAL.ALS-functies

Excel slaat datums en tijden op als getallen.Dus we kunnen het als een getal gebruiken.

#1 Tel cellen wanneer de voorwaarde gelijk is aan de opgegeven datum

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

Om met datums te werken, raad ik aan om de DATE-functie te gebruiken, omdat dit de kans elimineert dat de datumwaarde verkeerd is.Dus als ik bijvoorbeeld de datum 2015 september 9 zou willen gebruiken, zou ik de DATE-functie als volgt kunnen gebruiken:

= DATUM (2015,9,1)

Ondanks de regionale verschillen zal deze formule dezelfde datum retourneren.01-09-2015 zou bijvoorbeeld 2015 september 9 zijn onder de Amerikaanse datumsyntaxis en 1 januari 2015 onder de Britse datumsyntaxis.Deze formule retourneert echter altijd 2105 september 9.

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 de voorwaarde voor of na de opgegeven datum is

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

Als ik bijvoorbeeld alle cellen met datums na 2015 september 9 zou willen tellen, zou ik de volgende formule kunnen gebruiken:

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

Op dezelfde manier kunt u ook het aantal cellen tellen vóór een bepaalde datum.Als u datums in de telling wilt opnemen, gebruikt u de operator "is gelijk aan" samen met de operatoren "groter dan/kleiner dan".

U kunt ook celverwijzingen gebruiken die datums bevatten.In dit geval moet u een ampersand (&) gebruiken om de operator (tussen dubbele aanhalingstekens) te combineren met de datum.

Zie onderstaand voorbeeld:

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

#3 Tel cellen met meerdere criteria – tussen twee datums

Om het aantal waarden tussen twee waarden te krijgen, moeten we meerdere voorwaarden gebruiken in de AANTAL.ALS-functie.

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

Methode XNUMX: Gebruik de AANTAL.ALS-functie

De functie AANTAL.ALS kan meerdere voorwaarden als argumenten gebruiken en cellen alleen tellen als alle voorwaarden WAAR zijn.Om cellen te tellen met waarden tussen twee opgegeven datums, zoals 9 september en 2 september, kunnen we de volgende AANTALLEN.ALS-functie gebruiken:

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

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

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

U kunt ook datums in cellen opnemen en celverwijzingen als criteria gebruiken.In dit geval kunt u de operator de datum niet in de cel laten opnemen.U moet handmatig operators aan de formule toevoegen (tussen dubbele aanhalingstekens) en een ampersand (&) gebruiken om celverwijzingen toe te voegen.Zie onder:

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

Methode 2: Gebruik de AANTAL.ALS-functie

Als u meerdere voorwaarden heeft, kunt u een AANTAL.ALS-functie gebruiken of een combinatie van twee AANTAL.ALS-functies maken.De volgende formule zal ook werken:

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

In de bovenstaande formule vinden we eerst het aantal cellen waarvan de datum na 9 september is en trekken vervolgens het aantal cellen af ​​waarvan de datum na 2 september is.Dit geeft een resultaat van 9 (dat wil zeggen, het aantal cellen met datums na 7 september en op of vóór 7 september).

Als u niet wilt dat de formule zowel 9 september als 2 september berekent, gebruikt u in plaats daarvan de volgende formule:

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

Als u twee datums van de telling wilt uitsluiten, gebruikt u de 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)))

Bovendien kunt u standaarddatums in cellen instellen en celverwijzingen gebruiken (en operators tussen dubbele aanhalingstekens met ampersands).

Jokertekens gebruiken in de voorwaarden van de functies AANTAL.ALS en AANTAL.ALS

Er zijn drie jokertekens in Excel:

1. *(Asterisk) - het vertegenwoordigt een willekeurig aantal tekens.Ex* kan bijvoorbeeld betekenen excel, excels, example, expert, etc.
2. ? (vraagteken) - Het vertegenwoordigt een enkel teken.Trömp kan bijvoorbeeld Trump of Vagebond betekenen.
3. ~ (tilde) - Wordt gebruikt om jokertekens (~, *, ?) in tekst te identificeren.

U kunt de AANTAL.ALS-functie met jokertekens gebruiken om cellen te tellen wanneer andere ingebouwde telfuncties niet werken.Stel dat u een dataset heeft die er als volgt uitziet:

Laten we nu een paar voorbeelden nemen:

#1 Tel cellen die tekst bevatten

Om cellen met tekst erin te tellen, kunnen we het jokerteken * (sterretje) gebruiken.Aangezien sterretjes een willekeurig aantal tekens vertegenwoordigen, telt het alle cellen die tekst bevatten.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 telt cellen die apostrofs bevatten (en daarom leeg lijken) of die mogelijk als onderdeel van de formule zijn opgenomen.

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

#2 Tel niet-lege cellen

Denk nog eens goed na als u overweegt de COUNTA-functie te gebruiken.

Probeer het eens, het kan u teleurstellen.COUNTA telt ook cellen die lege tekenreeksen bevatten (meestal geretourneerd door formules als ="", of wanneer mensen alleen apostrofs in cellen invoeren).Cellen die lege tekenreeksen bevatten, lijken leeg, maar zijn dat niet, dus worden ze geteld door de functie AANTALLEN.

COUNTA telt ook cellen die lege tekenreeksen bevatten (meestal geretourneerd door formules als ="", of wanneer mensen alleen apostrofs in cellen invoeren).Cellen die lege tekenreeksen bevatten, lijken leeg, maar zijn dat niet, dus worden ze geteld door de functie AANTALLEN.

Dus als je de formule = . gebruikt COUNTA (A1:A11), het retourneert 11 terwijl het 10 zou moeten retourneren.

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 hem op te splitsen:

#3 Tel cellen met specifieke tekst

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

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

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

Als u cellen wilt tellen die letters ergens in de tekst bevatten, omring ze dan met een asterisk.Als u bijvoorbeeld cellen wilt tellen die de letter "a" bevatten, gebruikt u *a* als voorwaarde.

In vergelijking met mijn andere artikelen is deze ongewoon lang.Hoop dat je het leuk vindt.Laat me weten wat je ervan vindt door een reactie achter te laten.