Het Excel bestand dat hoort bij deze pagina over SOM.ALS en SOMMEN.ALS staat onderaan de tekst en mag gratis worden gedownload voor eigen gebruik. Onder de video tref je een geschreven les over dit onderwerp aan. Deze geschreven les is een goede aanvulling op de video.
Inleiding
SOM.ALS is een veelgebruikte Excel functie. Je kunt er getallen mee optellen die voldoen aan één bepaalde voorwaarde (‘criterium’). De algemene opbouw van deze functie is als volgt.
SOM.ALS(bereik;criterium;optelbereik)
In gewone woorden staat hier dat Excel naar een opgegeven reeks cellen kijkt (‘bereik’). Vervolgens wordt vastgesteld of er in die reeks ook cellen zijn die voldoen aan één bepaalde voorwaarde (‘criterium’). Als dat zo is, zal Excel de waarden in een andere opgegeven reeks cellen bij elkaar optellen (‘optelbereik’). Het klinkt wellicht abstract, maar de volgende voorbeelden maken hopelijk veel duidelijk.
De autodealer
In deze blog gebruik ik als voorbeeld een tabel waarin de verkopen van een Renault dealer worden getoond. Kolom A toont een datum in week 6 van het jaar 2020. Kolom B toont de verkoper. In kolom C, D en E vind je terug welke auto hij/zij heeft verkocht, welke kleur de auto heeft en tegen welke prijs de auto is weggegaan.
Één voorwaarde
Stel je voor, ik ben geïnteresseerd in de gerealiseerde omzet van één bepaalde verkoper. Dit betekent het volgende:
- Ik richt me op de verkopers in kolom B (‘bereik’)
- Mijn enige voorwaarde is dat ik de omzet van één bepaalde verkoper wil optellen (‘criterium’)
- De op te tellen omzetten van de betreffende verkoper staan in kolom E (‘optelbereik’)
Voorbeeld 1 – Tab ‘Enkele Voorwaarde’
Ik wil in gele cel H3 de gerealiseerde omzet van de specifieke verkoper tonen.In cel H2 heb ik eerst een dropdown lijst met verkopers gemaakt. Als je uit die lijst een verkoper kiest, wordt in cel H3 de omzet van deze verkoper in week 6 berekend.
Formule H3
=SOM.ALS(B3:B22;H2;E3:E22)
De in H3 gebruikte formule kun je als volgt ontleden:
- Excel richt zich op de verkopers in cel B3 tot en met B22 (‘bereik’)
- Voor elk cel in dat ‘bereik’ bepaalt Excel of de gekozen naam uit cel H2 (‘criterium’) er mee overeenkomt
- Is aan het ‘criterium’ voldaan dan telt Excel de bijbehorende omzet in cel E3 tot en met E22 (‘optelbereik’) op bij het totaal
In het ‘bereik’ treft Excel het ‘criterium’ Marijke aan in cel B6, B7, B8, B12 en B17. Excel telt vervolgens de bijbehorende waarden uit het ‘optelbereik’ bij elkaar op (namelijk E6, E7, E8, E12 en E17).
LET OP: Het ‘bereik’ en ‘optelbereik’ moeten altijd even groot zijn. In dit voorbeeld bestaan zowel het opgegeven ‘bereik’ (kolom B) als ‘optelbereik’ (kolom E) uit 20 cellen.
De EN-functie
Stel ik ben geïnteresseerd in de gerealiseerde omzet van één bepaalde verkoper (‘criterium 1’) op één bepaald autotype (‘criterium 2’). Aangezien ik nu twee voorwaarden stel aan de omzetberekening, kan ik niet zomaar gebruik maken van de SOM.ALS functie. Echter, door het aanmaken van een hulpkolom met de EN-functie wordt dat wel mogelijk. De algemene opbouw van de EN-functie is als volgt.
EN(logisch1;logisch2,logisch3)
In gewone woorden staat hier dat Excel een aantal tests gaat uitvoeren. In dit voorbeeld zijn dat er drie (‘logisch1’, ‘logisch2’ en ‘logisch3’) maar het kunnen er oneindig veel zijn. Als de uitkomst van alle tests positief is, dan meldt Excel ‘WAAR’. Zodra de uitkomst van één (of meer) tests negatief is, dan meldt Excel ‘ONWAAR’.
Twee voorwaarden met een hulpkolom
Ik ben dus geïnteresseerd in de gerealiseerde omzet van één bepaalde verkoper op één bepaald autotype. Ik ga een hulpkolom aanmaken in kolom F met de EN-functie. Deze EN-functie laat ik testen of aan mijn twee voorwaarden is voldaan (‘WAAR’) of niet (‘ONWAAR’). Werken met een hulpkolom maakt letterlijk zichtbaar welke omzetten wel en niet aan de gestelde voorwaarden voldoen.
Voorbeeld 2 – Tab ‘Meer Voorwaarden Hulpkolom’
In hulpkolom F ga ik twee dingen testen. (1) Is de verkoper in kolom B gelijk aan de verkoper die in cel I2 is opgegeven? EN (2) Is de auto in kolom C gelijk aan de auto die in cel I3 is opgegeven? De EN-formules in kolom F luiden nu als volgt:
Formule F3
=EN(B3=$I$2;C3=$I$3)
Formule F4
=EN(B4=$I$2;C4=$I$3)
…
Formule F22
=EN(B22=$I$2;C22=$I$3)
Excel geeft in kolom F aan waar wel (‘WAAR’) en niet (‘ONWAAR’) volledig wordt voldaan aan de twee gestelde voorwaarden uit mijn test.
Het enige dat ik nu nog hoef te doen, is omzetten optellen in gele cel I4 als aan de twee voorwaarden is voldaan. Oftewel, waar in hulpkolom F ‘WAAR’ staat. De formule in I4 luidt:
Formule I4
=SOM.ALS(F3:F22;WAAR;E3:E22)
De in I4 gebruikte formule kun je als volgt ontleden:
- Excel richt zich op de waarden in cel F3 tot en met F22 (‘bereik’)
- Voor elk cel in dat ‘bereik’ bepaalt Excel of er ‘WAAR’ staat (‘criterium’)
- Is aan het ‘criterium’ voldaan dan telt Excel de bijbehorende omzet in cel E3 tot en met E22 (‘optelbereik’) op bij het totaal
In het ‘bereik’ treft Excel het ‘criterium’ WAAR in cel F4, F5, en F15 aan. Excel telt vervolgens de bijbehorende waarden uit het ‘optelbereik’ bij elkaar op (namelijk E4, E5, en E15).
Meer voorwaarden met SOMMEN.ALS
Als je geen behoefte hebt aan extra inzicht op basis van een hulpkolom, kan je de SOMMEN.ALS functie gebruiken. Met de SOMMEN.ALS functie kun je getallen optellen die voldoen aan diverse voorwaarden (‘criteria’). De algemene opbouw van deze functie is als volgt.
SOMMEN.ALS(optelbereik;criteriumbereik1;criteria1;criteriumbereik2;criteria2,...)
In gewone woorden staat hier dat Excel de waarden in een bepaalde reeks cellen bij elkaar optelt (‘optelbereik’). Daartoe zoekt Excel in een opgegeven reeks cellen (‘criteriumbereik1’) naar cellen die voldoen aan de eerste specifieke voorwaarde (‘criteria1’). Vervolgens zoekt Excel in een opgegeven reeks cellen (‘criteriumbereik2’) naar cellen die voldoen aan de tweede specifieke voorwaarde (‘criteria2’). Etcetera.
LET OP: Bij de SOMMEN.ALS formule staat het ‘optelbereik’ vooraan in plaats van achteraan in de formule. Ook wordt eerst gevraagd om het ‘criteriumbereik’ en pas daarna om het ‘criterium’ in plaats van andersom zoals in de SOM.ALS formule.
Voorbeeld 3 – Tab ‘Meer voorwaarden SOMMEN.ALS’
In gele cel H5 wil ik omzetten optellen als aan 3 voorwaarden is voldaan. (1) gekozen verkoper EN (2) gekozen autotype EN (3) omzet van op/na een gekozen datum. De gebruikte formule luidt als volgt.
Formule H5
=SOMMEN.ALS(E3:E22;B3:B22;H2;C3:C22;H3;A3:A22;”>=”&H4)
De formule ontleden
De in H5 gebruikte formule kun je als volgt ontleden:
- Excel telt de omzetten in cel E3 tot en met E22 op (‘optelbereik’) als is voldaan aan alle voorwaarden
- In cel B3 tot en met B22 (‘criteriumbereik1’) zoekt Excel naar de verkoper uit cel H2 (‘criteria1’)
- In cel C3 tot en met C22 (‘criteriumbereik2’) zoekt Excel naar de auto uit cel H3 (‘criteria2’)
- In cel A3 tot en met A22 (‘criteriumbereik3’) zoekt Excel naar een datum die gelijk is aan of later in de tijd ligt dan de datum uit cel H4 (‘criteria3’)
In het ‘optelbereik’ treft Excel de combinatie van de drie ‘criteria’ aan in de cellen A22-B22-C22. Excel telt vervolgens de bijbehorende waarden uit het ‘optelbereik’ bij elkaar op (namelijk E22).
Tot slot een aantal tips
- Oefen eerste met de SOM.ALS functie en één voorwaarde.
- Verdiep je in de EN-functie zodat je begrijpt hoe tests in Excel werken.
- Maak complexere optellingen met behulp van de EN-functie en een hulpkolom. Zo leer je goed wat Excel doet en worden je eigen fouten snel zichtbaar.
- Experimenteer met de SOMMEN.ALS functie. Oefen daarbij ook met ‘>’ en ‘<‘ tekens want het gebruik daarvan vergt ervaring.