Cellen tellen met AANTAL.ALS

Tel cellen die aan bepaalde criteria voldoen

Wanneer u te maken heeft met lijsten lijkt het alsof u altijd op zoek bent om te tellen hoeveel van een bepaald item in de lijst staat. Misschien wilt u tellen hoeveel auto’s er door een bepaalde verkoper zijn verkocht of hoe vaak een werknemer zich ziek heeft gemeld. Gelukkig maakt Excel het eenvoudig om cellen te tellen die aan bepaalde criteria voldoen, net als deze.

Tel cellen die aan bepaalde criteria voldoen met Excel’s AANTAL.ALS -functie

Laten we zeggen dat we een lijst met datums hebben waarop auto’s werden verkocht en ingeruild op een gebruikte auto, de verkoper die de auto verkocht, en het merk van de auto. Deze lijst is geweldige informatie, maar we willen weten wie de meeste auto’s heeft verkocht. Zie de een afbeelding van deze lijst hier links.

Met de functie AANTAL.ALS van Excel kunnen we cellen tellen die aan bepaalde criteria voldoen om erachter te komen wie het meeste heeft verkocht.

Om het gemakkelijker te maken, maken we een lijst van de namen van elk lid van het verkoopteam aan de zijkant van deze verkooplijst of op een ander blad. Henry,Bert,Eilt en Sander. In mijn blad heb ik deze lijst in de cellen E2: E5.

In cel F2 heb ik de volgende formule:

Nederlandse versie:  =AANTAL.ALS($B$2:$B$14;E2)

Engelse versie: =COUNTIF($B$2:$B$14,E2)

Deze formule neemt het bereik B2: B14 waar we de namen hebben van de verkopers die de auto’s hebben verkocht, en telt hoe vaak ze in dat bereik worden weergegeven. E2 bevat de naam van een verkoper, zodat deze de cel gebruikt als de criteria die moeten overeenkomen. We kunnen net zo gemakkelijk de naam van de verkoper invoeren in plaats van E2 in de formule, maar omdat we verschillende namen hebben om op te zoeken, kunnen we de celverwijzing gebruiken om te voorkomen dat u opnieuw typt.

Vervolgens kopieert u de formule naar beneden om in te vullen naast de resterende verkopers om de resultaten te krijgen. Als u dit moet visualiseren, Bekijk dan de screenshot aan het einde van dit artikel.

 

De resultaten op basis van de lijst in de screenshot zijn Henry: 3 Bert: 4, Eilt: 3, Sander: 3.

Dit is geweldig, maar kijkend naar de lijst lijkt het erop dat Bert stopte met het verkopen van zoveel auto’s na mei.

 

Tel cellen die aan meerdere criteria voldoen met de functie AANTALLEN.ALS 

Wat als we wilden weten wie de meeste auto’s in een bepaalde tijdsperiode verkocht? We kunnen de functie AANTALLEN.ALS van Excel gebruiken om cellen te tellen die aan meerdere criteria voldoen.

Ik wil weten wie de meeste auto’s elke maand heeft verkocht, maar hetzelfde concept dat we gebruiken, kan gelden voor elke periode – wekelijks, driemaandelijks, jaarlijks of elke andere aangepaste periode die u zou moeten opzoeken.

We gaan kolommen toevoegen voor elke maand naast het totaal dat we hebben ingesteld in kolom F. Dus, kolom G zou januari, H zou zijn februari, enz.

Nu, aangezien Excel verwijst naar datums met behulp van een getal in plaats van het normale maand/dag/jaar-formaat, zijn we misschien gewend om het datumbereik in te voeren waarin we geïnteresseerd zijn om naar te kijken. Dit is misschien geen ideale oplossing, maar wel voor aangepaste datumbereiken.

Onder onze tabel met verkopers en maanden laten we de eerste dag van de maand en de laatste dag van de maand toevoegen, zodat we die datums in onze formule kunnen gebruiken.

In mijn werkblad heb ik de eerste dag van de maand in rij 7 van de kolom van de maand en de laatste dag eronder in rij 8 ingevoerd.

In cel G2, die in mijn spreadsheet de cel is waarin de verkoop van Henry in januari wordt weergegeven, voert u de volgende formule in:

Nederlandse versie: =AANTALLEN.ALS($A$2:$A$14;”>=”&G$7;$A$2:$A$14;”<=”&G$8;$B$2:$B$14;$E2)

Engelse versie: =COUNTIFS($A$2:$A$14,”>=”&G$7,$A$2:$A$14,”<=”&G$8,$B$2:$B$14,$E2)

Deze formule is vergelijkbaar met de AANTAL.ALS -formule, behalve dat u met AANTALLEN.ALS meerdere criteria kunt opgeven om de resultaten te verfijnen.

U wilt deze formule ook kopiëren en plakken naar de andere verkopers, en naar de andere maanden.

We breken deze formule af op elke set individuele criteria.

$A$2:$A$14;”>=”&G$7

Dit refereer naar de cellen A2: A14 waar onze data is die groter of gelijk is aan cel G7, waar ik de eerste dag van de maand vermeld.

Houd er rekening mee dat de datum die wordt gebruikt in de G7 gewoon de begindatum is van de periode waarin we geïnteresseerd zijn. Het hoeft niet de eerste dag van een maand te zijn. Dit kan de eerste dag van het kwartaal, week of een andere aangepaste tijdsperiode zijn. Hetzelfde idee geldt voor de laatste dag van de maand. Gebruik alle begin-en einddatums die zinvol zijn voor uw situatie.

$A$2:$A$14;”<=”&G$8

Dit maakt in principe gebruik van dezelfde logica als het vorige criterium, behalve dat het op zoek is naar datums die kleiner zijn dan of gelijk zijn aan de laatste dag van de maand, die in Cell G8 zijn.

 $B$2:$B$14;$E2

Ten slotte gebruiken we dezelfde logica die we gebruikten in de vorige AANTAL.ALS-formule om de resultaten te beperken tot slechts één verkoper. De cellen B2: B14 bevatten de naam van de verkoper die een auto heeft verkocht, terwijl E2 de verkoper bevat die we opzoeken.

Al die logica samengevat, vragen we met AANTALLEN.ALS om cellen te tellen waar de datum groter is dan of gelijk is aan de eerste dag van de maand en kleiner dan of gelijk aan de laatste dag van de maand, met de verkoper aan het begin van de rij.

Als we ook willen weten wie het grootste deel van een bepaalde auto heeft verkocht, kunnen we de voorwaarde toevoegen $C$2: $C$14;”Honda”. Dit zou alle Honda’s laten zien die door een bepaalde verkoper in een bepaalde maand werd verkocht.

Download aantallenals.xlsx

Succes met het toepassen van deze functies.

Paul van de Laar