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

• ALS combineren met AANTAL.ALS
• ALS combineren met SOM.ALS
• Extra aantekening voor Excel 2013

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.

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.


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

• Excel formules: AANTAL.ALS en SOM.ALS
• Excel: afdrukken, rijen en kolommen vastzetten
• Word: Brief samenvoegen met lijst in Excel
• Word: Etiketten maken
• Excel: keuzelijst maken