SOM.ALS en AANTAL.ALS in Excel
SOM.ALS telt de waarden op die aan een voorwaarde voldoen: SOM.ALS(bereik; criterium; optelbereik), bijvoorbeeld =SOM.ALS(B2:B8; "Noord"; C2:C8) voor de omzet van Noord. AANTAL.ALS telt hoeveel cellen aan de voorwaarde voldoen: AANTAL.ALS(bereik; criterium), bijvoorbeeld =AANTAL.ALS(B2:B8; "Noord") voor het aantal orders uit Noord.
Een uitgewerkt voorbeeld
Zeven orders met de regio in kolom B en het bedrag in kolom C. In F2 komt de omzet van regio Noord.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Regio | Bedrag | Grens |
| 2 | 1001 | Noord | 120 | 95 |
| 3 | 1002 | Zuid | 80 | |
| 4 | 1003 | Noord | 45 | |
| 5 | 1004 | Oost | 200 | |
| 6 | 1005 | zuid | 150 | |
| 7 | 1006 | Noord | 95 | |
| 8 | 1007 | West | 60 |
=SOM.ALS(B2:B8;"Noord";C2:C8)
De formule zoekt in B2:B8 naar Noord en vindt het in B2, B4 en B7. Uit dezelfde rijen van kolom C telt hij de bedragen op: 120 + 45 + 95 = 260. F2 toont 260.
=AANTAL.ALS(B2:B8;"Noord")
Wil je weten hoeveel orders er uit Noord kwamen, dan telt AANTAL.ALS de cellen met Noord: 3. Er is geen optelbereik, want er valt niets op te tellen.
Bewaar je uitslagen
Met een gratis account staan de uitslagen van je korte tests op één plek, met de datum erbij.
Zo is SOM.ALS opgebouwd
SOM.ALS heeft drie delen, gescheiden door puntkomma's. Het eerste is het bereik waarin je zoekt, het tweede het criterium en het derde het bereik dat je optelt. Laat je het derde deel weg, dan telt SOM.ALS de cellen uit het eerste bereik zelf op. =SOM.ALS(C2:C8;">100") geeft zo de som van alle bedragen boven 100: 470.
Het criterium kan tekst zijn ("Noord"), een getal (100) of een vergelijking tussen aanhalingstekens (">100", "<>Noord"). Op hoofdletters let SOM.ALS niet: zuid en Zuid tellen allebei mee.
Zo is AANTAL.ALS opgebouwd
AANTAL.ALS heeft twee delen: het bereik en het criterium. De functie telt de cellen die voldoen, wat er ook in staat. =AANTAL.ALS(C2:C8;">100") geeft 3, want drie bedragen liggen boven de 100. Het criterium werkt precies als bij SOM.ALS.
Meer dan één voorwaarde: SOMMEN.ALS
Wil je de omzet van Noord boven de 100, dan heb je twee voorwaarden en is SOM.ALS niet genoeg. Daarvoor is SOMMEN.ALS, met een andere volgorde: eerst het bereik dat je optelt, daarna per voorwaarde een bereik en een criterium. =SOMMEN.ALS(C2:C8;B2:B8;"Noord";C2:C8;">100") geeft 120, de enige order uit Noord boven 100. Voor tellen met meer voorwaarden bestaat AANTALLEN.ALS.
Drie fouten die het vaakst voorkomen
De vergelijking zonder aanhalingstekens
=SOM.ALS(C2:C8;>100) accepteert Excel niet. Een vergelijking in het criterium is tekst en staat tussen aanhalingstekens: ">100". Staat de grens in een cel, dan plak je het teken eraan met &: ">="&D2.
Bereiken van verschillende lengte
Het zoekbereik en het optelbereik lopen rij voor rij gelijk op. Begint het ene op rij 2 en het andere op rij 3, dan telt SOM.ALS bij Noord in B2 het bedrag uit C3 op. Er komt geen foutmelding, alleen een verkeerd getal. Laat beide bereiken daarom op dezelfde rij beginnen en eindigen.
Op de grens: < of <=
"<80" telt een order van precies 80 niet mee, "<=80" wel. Op het blad hierboven scheelt dat 80 euro in de som. Lees de afspraak woord voor woord: bij "tot en met 80" hoort <=.
Oefen SOM.ALS en AANTAL.ALS
Tien opgaven, zes over SOM.ALS en vier over AANTAL.ALS, van een regio optellen naar een criterium met een grens uit een cel. De formules gebruiken de puntkomma als scheidingsteken, zoals de Nederlandse Excel.
Bij elke opgave staat een formule in F2. Kies wat de cel toont. Alle tien rekenen op dezelfde zeven orders: kolom B is de regio, kolom C het bedrag en D2 een grens.
Opgave 1
De formule staat in F2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Regio | Bedrag | Grens |
| 2 | 1001 | Noord | 120 | 95 |
| 3 | 1002 | Zuid | 80 | |
| 4 | 1003 | Noord | 45 | |
| 5 | 1004 | Oost | 200 | |
| 6 | 1005 | zuid | 150 | |
| 7 | 1006 | Noord | 95 | |
| 8 | 1007 | West | 60 |
=SOM.ALS(B2:B8;"Noord";C2:C8)
Wat toont F2?
Opgave 2
De formule staat in F2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Regio | Bedrag | Grens |
| 2 | 1001 | Noord | 120 | 95 |
| 3 | 1002 | Zuid | 80 | |
| 4 | 1003 | Noord | 45 | |
| 5 | 1004 | Oost | 200 | |
| 6 | 1005 | zuid | 150 | |
| 7 | 1006 | Noord | 95 | |
| 8 | 1007 | West | 60 |
=AANTAL.ALS(B2:B8;"Noord")
Wat toont F2?
Opgave 3
De formule staat in F2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Regio | Bedrag | Grens |
| 2 | 1001 | Noord | 120 | 95 |
| 3 | 1002 | Zuid | 80 | |
| 4 | 1003 | Noord | 45 | |
| 5 | 1004 | Oost | 200 | |
| 6 | 1005 | zuid | 150 | |
| 7 | 1006 | Noord | 95 | |
| 8 | 1007 | West | 60 |
=SOM.ALS(B2:B8;"Oost";C2:C8)
Wat toont F2?
Opgave 4
De formule staat in F2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Regio | Bedrag | Grens |
| 2 | 1001 | Noord | 120 | 95 |
| 3 | 1002 | Zuid | 80 | |
| 4 | 1003 | Noord | 45 | |
| 5 | 1004 | Oost | 200 | |
| 6 | 1005 | zuid | 150 | |
| 7 | 1006 | Noord | 95 | |
| 8 | 1007 | West | 60 |
=SOM.ALS(B2:B8;"Zuid";C2:C8)
Wat toont F2?
Opgave 5
De formule staat in F2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Regio | Bedrag | Grens |
| 2 | 1001 | Noord | 120 | 95 |
| 3 | 1002 | Zuid | 80 | |
| 4 | 1003 | Noord | 45 | |
| 5 | 1004 | Oost | 200 | |
| 6 | 1005 | zuid | 150 | |
| 7 | 1006 | Noord | 95 | |
| 8 | 1007 | West | 60 |
=AANTAL.ALS(C2:C8;">100")
Wat toont F2?
Opgave 6
De formule staat in F2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Regio | Bedrag | Grens |
| 2 | 1001 | Noord | 120 | 95 |
| 3 | 1002 | Zuid | 80 | |
| 4 | 1003 | Noord | 45 | |
| 5 | 1004 | Oost | 200 | |
| 6 | 1005 | zuid | 150 | |
| 7 | 1006 | Noord | 95 | |
| 8 | 1007 | West | 60 |
=SOM.ALS(C2:C8;">100")
Wat toont F2?
Opgave 7
De formule staat in F2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Regio | Bedrag | Grens |
| 2 | 1001 | Noord | 120 | 95 |
| 3 | 1002 | Zuid | 80 | |
| 4 | 1003 | Noord | 45 | |
| 5 | 1004 | Oost | 200 | |
| 6 | 1005 | zuid | 150 | |
| 7 | 1006 | Noord | 95 | |
| 8 | 1007 | West | 60 |
=AANTAL.ALS(B2:B8;"<>Noord")
Wat toont F2?
Opgave 8
De formule staat in F2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Regio | Bedrag | Grens |
| 2 | 1001 | Noord | 120 | 95 |
| 3 | 1002 | Zuid | 80 | |
| 4 | 1003 | Noord | 45 | |
| 5 | 1004 | Oost | 200 | |
| 6 | 1005 | zuid | 150 | |
| 7 | 1006 | Noord | 95 | |
| 8 | 1007 | West | 60 |
=SOM.ALS(B2:B8;"<>Noord";C2:C8)
Wat toont F2?
Opgave 9
De formule staat in F2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Regio | Bedrag | Grens |
| 2 | 1001 | Noord | 120 | 95 |
| 3 | 1002 | Zuid | 80 | |
| 4 | 1003 | Noord | 45 | |
| 5 | 1004 | Oost | 200 | |
| 6 | 1005 | zuid | 150 | |
| 7 | 1006 | Noord | 95 | |
| 8 | 1007 | West | 60 |
=SOM.ALS(C2:C8;"<=80")
Wat toont F2?
Opgave 10
De formule staat in F2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Regio | Bedrag | Grens |
| 2 | 1001 | Noord | 120 | 95 |
| 3 | 1002 | Zuid | 80 | |
| 4 | 1003 | Noord | 45 | |
| 5 | 1004 | Oost | 200 | |
| 6 | 1005 | zuid | 150 | |
| 7 | 1006 | Noord | 95 | |
| 8 | 1007 | West | 60 |
=AANTAL.ALS(C2:C8;">="&D2)
Wat toont F2?
0 van de 10 opgaven gedaan.
In de gratis test zitten vragen over verwijzingen, ALS, opzoeken, draaitabellen en foutmeldingen.
Doe de gratis testVerder oefenen
Een voorwaarde die een tekst of bedrag in de cel zelf kiest, staat op De ALS-functie uitgelegd, met EN en OF. Optellen en tellen per regio zonder formule kan ook met een draaitabel; hoe dat gaat staat op Een draaitabel maken en lezen, en het verschil tussen Som en Aantal daar op Som of Aantal in een draaitabel. In de gratis test zitten vragen over verwijzingen, ALS, opzoeken, draaitabellen en foutmeldingen. Wat er in de training van dertig dagen zit, lees je bij Spreadsheets: formules en draaitabellen.
Veelgestelde vragen
- Wat is het verschil tussen SOM.ALS en AANTAL.ALS?
- SOM.ALS telt de bedragen op van de rijen die aan het criterium voldoen, AANTAL.ALS telt alleen hoeveel rijen dat zijn. Op het blad hierboven geeft SOM.ALS voor Noord 260, de omzet, en AANTAL.ALS 3, het aantal orders.
- Hoe heten SOM.ALS en AANTAL.ALS in de Engelse Excel?
- SUMIF en COUNTIF, en SOMMEN.ALS heet SUMIFS. In de Engelse Excel scheid je de delen met een komma in plaats van een puntkomma: =SUMIF(B2:B8,"Noord",C2:C8).
Microsoft Excel is een merk van Microsoft. Skillido is niet verbonden aan Microsoft.