InfoNu.nl > Pc en Internet > Tips en tricks > Excel formules: ALS combineren met AANTAL.ALS en SOM.ALS

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 - 2019 Juliejulie, het auteursrecht (tenzij anders vermeld) van dit artikel ligt bij de infoteur. Zonder toestemming van de infoteur is vermenigvuldiging verboden.
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…
De functies in ExcelDe functies in ExcelHierin zullen verschillende functies die in Excel gebruikt kunnen worden naar boven komen. Deze zullen uitgelegd worden…
Cursus: het aanbod van Excel-cursussenCursus: het aanbod van Excel-cursussenExcel is een programma dat veel gebruikt wordt. Het programma heeft veel mogelijkheden voor iemand die "iets" met cijfer…
Berekeningen maken in ExcelExcel maakt deel uit van MS Office. Met Excel kunt u uw berekeningen maken. Daarnaast kunt ook op verschillende manieren…
Bronnen en referenties
  • http://office.microsoft.com/

Reageer op het artikel "Excel formules: ALS combineren met AANTAL.ALS en SOM.ALS"

Plaats een reactie, vraag of opmerking bij dit artikel. Reacties moeten voldoen aan de huisregels van InfoNu.
Meld mij aan voor de tweewekelijkse InfoNu nieuwsbrief
Ik ga akkoord met de privacyverklaring en ben bekend met de inhoud hiervan
Reacties

Tim, 17-05-2019 11:26 #10
Ik zit met een probleem. Ik heb formule staan in excel maar ik wil iets toevoegen. Ik heb een aantal uitkomsten maar moet tussen de 0.1 en 1 moet de uitkomst 0.1 worden hoe kan dat in voeren in mijn formule.

Het is al een aardige formule die ik nu heb staan.

Gr

Coen, 09-02-2016 15:27 #9
Beste Richard,
je zou je formule werkend kunnen krijgen door er de verschillen ALS-functies die je gebruikt op te tellen en een 0 weer te geven als derde parameter in de als functie. Echter er is een elegantere oplossing met VERT.ZOEKEN.
Voer een referentietabel op waar je het bedrag uit kan halen in excel zoals in onderstaande voorbeeld. Deze tabel bestaat uit de kolommen 'min. aantal' en 'bedrag', met daaronder de bijbehorende waarden. Door op basis van het aantal te leden te zoeken in deze referentietabel met VERT.ZOEKEN kan in 1 keer de juiste waarde worden opgehaald.
Gegeven dat het leden aantal in H4 staat en het tabelbereik van de eerder genoemde referentietabel "$A$1:$B$5" is, wordt de formule dan: =VERT.ZOEKEN(H4;$A$1:$B$5;2;WAAR). Dan krijg je de uitkomsten zoals in onderstaand voorbeeld onder 'gevonden bedrag'.

min. Aantal bedrag #leden gevonden bedrag
0 25 23 25
26 50 29 50
51 75 99 100
76 100
101 150

Richard, 29-01-2016 13:08 #8
Ik ben bezig met een berekening op basis van het aantal leden en de prijs die daaraan verbonden is.

De verdeling zit als volgt:
0-25 leden = 25 euro (B91)
26-50 leden = 50 euro (B92)
51-75 leden = 75 euro (B93)
76-100 leden = 100 euro (B94)
>100= 150 euro (B95)

Nu heb ik in excel het volgende staan:
=ALS(H4<26;B$91) ALS(H4<51>25;B$92) ALS(H4<76>50;B$93) ALS(H4<101>75;B$94) ALS(H4>100;B$95)

H4=kolom leden (zijn meerdere groepen dus ik wil die som doortrekken naar 20 verschillende groepen). Zo is H5 een ander leden aantal en H6 weer een ander leden aantal.

B91 t/m B95 zijn respectievelijk de bedragen 25 t/m 100 euro.

Hoe krijg ik deze formule correct?

Alvast bedankt.

Van der Sijde, 10-12-2015 09:56 #7
Ik ben een planning aan het maken waarbij ik klussen (250 stuks) van verschillende tijden heb. Nu wil ik zoveel mogelijk dagen van 8 uur maken. Kan ik excel laten berekenen welke klussen ik moet combineren om de meest efficiënte indeling te hebben? Zo ja weet u hoe dit in zijn werk gaat?

Alain, 21-10-2015 13:49 #6
Goede middag,
Kan iemand mij helpen aub? Ik wil twee formules in één cel krijgen.
vb; als cel d7 gelijk is aan 0, dan is e7 ook gelijk aan 0, als cel d7 niet gelijk is aan 0 dan is cel e7 de waarde van d7 min de waarde van p7.

Volgende cellen hebben reeds een formule;
cel d7 is de waarde van a7 plus b7
cel e7 is de waarde van d7-p7

Waarschijnlijk niet te duidelijke uitgelegd…
Hartelijke dankt alvast
Alain

Kees, 10-02-2015 14:38 #5
Hallo Geert,

Dit is misschien geen sjieke oplossing, maar werkt wel:
ALS(A1="geert";ALS(B1<=6;"7,5";ALS(B1>12;"17,5";ALS(B1<>12>6;"12,5"))))

Geert, 05-02-2015 09:36 #4
Graag een antwoord op het volgende probleem:

Als a1 = geert en b1=<6 dan moet c1 7,5 worden
Als a1 = geert en b1=>6 en <12dan moet c1 12,5 worden
Als a1 = geert en b1=>12 dan moet c1 17,5 worden
Hoe krijg ik deze formule geformuleerd
Ik kom er niet uit

Groeten Geert

Rob, 18-01-2015 14:04 #3
En als ik nu tekst in de kolom A en B heb staan. Hoe dien ik dan de formule te gebruiken. In kolom A staan bij mij voornamen en in kolom B staat "man"of "vrouw". Nu wil ik per voornaam weten hoeveel mannen en hoeveel vrouwen die voornaam hebben. Ik denk dat ik met deze formule een heel eind ben maar hij werkt niet.

Jeroen van W., 28-02-2013 15:01 #2
Zeer fijne uitleg van de formules. Echter moet zeer duidelijk zijn dat dit ALLEEN voor Microsoft Office Excel 2000 is. In de versies vanaf 2007 hoeven deze formules niet gecombineerd te worden. In de nieuwere versie kan voor "Als + Som.Als" de volgende formule gebruikt worden: SOMMEN.ALS(optelbereik, criterium_bereik1, criterium1, [criterium_bereik2, criterium2], …) In de nieuwe versie zou de bovenstaande formule dan ook als volgt uitzien: =SOMMEN.ALS(B2:B6;A2:A6;"2012";B2:B6;"25")

Tim, 22-01-2013 10:53 #1
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.

Infoteur: Juliejulie
Laatste update: 20-06-2017
Rubriek: Pc en Internet
Subrubriek: Tips en tricks
Bronnen en referenties: 1
Reacties: 10
Schrijf mee!