Excel formules: ALS combineren met AANTAL.ALS en SOM.ALS

Excel formules: ALS combineren met AANTAL.ALS en SOM.ALS Formules nesten, oftewel met elkaar combineren, is één van de meest ingewikkelde onderdelen van Excel. In dit voorbeeld tel je hoe vaak een gegeven voorkomt wanneer deze voldoet aan twee specifieke voorwaarden. Stap voor stap laat ik zien hoe je de volgende formules combineert: ALS met AANTAL.ALS en ALS met SOM.ALS.

Excel formules nesten in Excel 2000


Toch is het combineren van formules niet heel moeilijk als je eenmaal weet hoe ze zijn opgebouwd. In de voorbeelden gaan wij uit van de volgende tabel.

A B
1 JAAR AANTAL
2 201125
3 201220
4 201225
5 201210
6 201225

ALS combineren met AANTAL.ALS

  • Met de formule ALS kun je een bepaald resultaat tonen wanneer wel of niet aan jouw voorwaarde wordt voldaan. Deze is altijd als volgt opgebouwd: =ALS(Voorwaarde;Resultaat bij waar;Resultaat bij onwaar)
  • De formule AANTAL.ALS telt automatisch het aantal keer dat één bepaalde waarde voorkomt en is als volgt opgebouwd: = AANTAL.ALS(Bereik;Criterium)

met

  • Voorwaarde = zoek in een bepaald bereik naar een bepaalde waarde
  • Resultaat bij waar = als de waarde WEL wordt gevonden, welke handeling dient dan te gebeuren?
  • Resultaat bij onwaar = als de waarde NIET wordt gevonden, welke handeling dient dan te gebeuren?
  • Bereik = in welke lijst wil je zoeken
  • Criterium = welke waarde wil je zoeken

Wanneer je deze twee functies combineert, kun je het het aantal keren tellen dat aan twee voorwaarden wordt voldaan. Stel, je wilt het aantal keren tellen dat in het jaar 2012 de waarde 25 voorkomt. Bouw de formule dan als volgt op:

  • =ALS(A2:A6=2012);Resultaat bij waar;Resultaat bij onwaar) - zoek in het bereik A2:A6 naar de waarde 2012
  • Invullen als Resultaat bij waar: AANTAL.ALS(B2:B6;25) - als de waarde WEL is gevonden, tel je het aantal keren dat de waarde 25 voorkomt in het bereik B2:B6
  • Invullen als Resultaat bij onwaar: "Komt niet voor" - als de waarde NIET is gevonden, geef je aan Komt niet voor
  • De uitkomst zal 2 zijn als de uiteindelijke formule er als volgt uitziet: =ALS(A2:A6=2012;AANTAL.ALS(B2:B6;25);"Komt niet voor") en is afgesloten met Ctrl-Shift-Enter

Nogmaals, deze formule zegt niets anders dan: Als in het bereik A2:A6 WEL de waarde 2012 voorkomt, dan moet het aantal keren worden geteld dat de waarde 25 voorkomt in het bereik B2:B6. En als de waarde 2012 NIET kan worden gevonden in combinatie met de waarde 25, geeft de formule automatisch aan Komt niet voor.

Heel belangrijk is dat je de formule afsluit met Ctrl-Shift-Enter in plaats van alleen Enter.

Stel, je wilt het aantal keren tellen dat in het jaar 2012 de waarde 50 voorkomt, ziet de formule er zo uit:
=ALS(A2:A6=2012;AANTAL.ALS(B2:B6;50);"Komt niet voor")
Je zult dan zien dat de uitkomst Komt niet voor zal zijn.

ALS combineren met SOM.ALS

Met het combineren van ALS met SOM.ALS kun je het somtotaal berekenen wanneer aan twee voorwaarden wordt voldaan. Deze combinatie is op dezelfde manier opgebouwd als de functie ALS met AANTAL.ALS. Alleen is de AANTAL.ALS formule vervangen door de SOM.ALS formule, die er altijd als volgt uitziet: =SOM.ALS(Bereik;Criterium;Optelbereik)

  • =ALS(A2:A6=2012;Resultaat bij waar;Resultaat bij onwaar) - zoek in het bereik A2:A6 naar de waarde 2012
  • Invullen als Resultaat bij waar: SOM.ALS(B2:B6;25) - als de waarde WEL is gevonden, wordt het somtotaal berekend van alle waardes 25 in het bereik B2:B6
  • Invullen als Resultaat bij onwaar: "Komt niet voor" - als de waarde NIET is gevonden, zal er Komt niet voor worden vermeld
  • Sluit de formule af met Ctrl-Shrift-Enter
  • De uitkomst zal 50 zijn als de uiteindelijke formule er als volgt uitziet: =ALS(A2:A6=2012;SOM.ALS(B2:B6;25);"Komt niet voor")

Nogmaals, deze formule zegt: Als in het bereik A2:A6 WEL de waarde 2012 voorkomt, dan moet het somtotaal worden geteld van alles waardes 25 in het bereik B2:B6. En als de waarde 2012 NIET kan worden gevonden in combinatie met de waarde 25, geeft de formule automatisch aan Komt niet voor.

In het deel SOM.ALS ontbreekt het Optelbereik, omdat deze hetzelfde is als het Bereik.

Extra aantekening voor Excel 2013

Het optellen van een bereik, dat aan meerdere voorwaarden voldoet is een stuk eenvoudiger geworden met de functie SOMMEN.ALS.

SOMMEN.ALS(optelbereik;criteriumbereik1;criterium1;[criteriumbereik2; criterium2];...)
NB. Er zijn maximaal 127 voorwaarden mogelijk.

Bij het voorbeeld van ALS combineren met SOM.ALS zijn er twee voorwaarden: kolom A moet voldoen aan de voorwaarde 2012 en kolom B aan de voorwaarde 25. Dan ziet de formule er als volgt uit.

=SOMMEN.ALS(B2:B6;A2:A6;2012;B2:B6;25)

De uitkomst is dan 50.

Lees verder

© 2012 - 2022 Juliejulie, het auteursrecht van dit artikel ligt bij de infoteur. Zonder toestemming is vermenigvuldiging verboden. Per 2021 gaat InfoNu verder als archief, artikelen worden nog maar beperkt geactualiseerd.
Gerelateerde artikelen
Een cursus Excel volgenEen cursus Excel volgenExcel is een spreadsheetprogramma dat onderdeel uitmaakt van het pakket Windows MS Office. Excel wordt ook wel MS Excel…
Excel Formules - Verticaal ZoekenExcel Formules - Verticaal ZoekenMicrosoft Excel is een software programma dat binnen bedrijven, scholen en/of privé huishouding gebruikt wordt om dageli…
Excel-formules: Eenvoudige voorbeelden & tips voor beginnersExcel-formules: Eenvoudige voorbeelden & tips voor beginnersExcel is een handige softwareapplicatie om gegevens en berekeningen in- en uit te voeren. Het programma zorgt ervoor dat…
De functies in ExcelDe functies in ExcelHierin zullen verschillende functies die in Excel gebruikt kunnen worden naar boven komen. Deze zullen uitgelegd worden…

Microsoft Outlook  meer dan alleen email!Microsoft Outlook meer dan alleen email!Veel particulieren en bedrijven werken met Windows en gebruiken Microsoft Office. Dit onder andere voor email beheer dat…
Prijsdiscriminatie op het internetPrijsdiscriminatie op het internetHoe bescherm je jezelf tegen internetwinkels die jou extra willen laten betalen op basis van je gebruikersprofiel? Dit v…
Bronnen en referenties
  • http://office.microsoft.com/
Reactie

Tim, 22-01-2013
Hallo,

Na het overnemen van hetzelfde voorbeeld en dezelfde formules, kwamen er bij mij andere cijfers uit dan staat beschreven. Bij de Als/aantal.als combinatie kwam er 3 uit en bij de als/som.als combinatie kwam er 75 uit. Ik heb echter alles overgenomen zoals het staat in het voorbeeld. Hopelijk weet iemand hierop een antwoord, zodat alles net zo gaat als in het voorbeeld.

Groeten,

Tim Reactie infoteur, 22-01-2013
Hoi, Tim. Deze voorbeelden gelden in Excel 2000. In de nieuwere versies van Excel werkt het allemaal een stuk gebruiksvriendelijker. Je kunt dan resp. de formules AANTALLEN.ALS en SOMMEN.ALS gebruiken. Mocht je hulp nodig hebben bij deze formules, dan hoor ik het graag.

Juliejulie (24 artikelen)
Laatste update: 20-06-2017
Rubriek: Pc en Internet
Subrubriek: Tips en tricks
Bronnen en referenties: 1
Per 2021 gaat InfoNu verder als archief. Het grote aanbod van artikelen blijft beschikbaar maar er worden geen nieuwe artikelen meer gepubliceerd en nog maar beperkt geactualiseerd, daardoor kunnen artikelen op bepaalde punten verouderd zijn. Reacties plaatsen bij artikelen is niet meer mogelijk.