De SOM.ALS formule wordt gebruikt om getallen in een kolom bij elkaar op te tellen wanneer in een andere kolom aan één specifieke voorwaarde wordt voldaan. Dit heb ik uitgelegd in een eerder artikel. Maar getallen optellen op basis van meerdere criteria met SOMMEN.ALS is nog krachtiger. In het Engels is dit de SUMSIF formule. Optellen op basis van meerdere criteria met SOMMEN.ALS. In dit artikel leg ik uit hoe dit werkt.
Een eenvoudig voorbeeld
Ik heb een fictief voorbeeld gemaakt waar twee fruitsoorten worden verkocht, namelijk peren en mandarijnen. Per fruitsoort worden meerdere rassen verkocht. Per combinatie van fruitsoort -en ras zijn de omzetcijfers over 2018, 2019 en 2020 gegeven.
De SOMMEN.ALS formule wordt gebruikt om getallen in een kolom bij elkaar op te tellen wanneer in meerdere andere kolommen aan specifieke voorwaarden wordt voldaan. In dit voorbeeld wil ik de totale omzet uitrekenen over de drie jaren voor een specifieke fruitsoort -en ras combinatie.
SOMMEN.ALS formule in de praktijk
In dit geval willen we specifiek weten wat de totale omzet over de drie jaren is geweest van Mineola mandarijnen. In cel D26 plaatsen daarom we de volgende formule:
=SOMMEN.ALS(D1:D25;A1:A25;”Mandarijnen”;B1:B25;”Mineola”)
De formule bestaat uit meerdere delen. Dit voorbeeld bestaat uit drie delen omdat we in dit voorbeeld slechts twee criteria uitvragen maar het aantal criteria kan bijna eindeloos worden uitgebreid.
=SOMMEN.ALS(D1:D25;A1:A25;”Mandarijnen”;B1:B25;”Mineola”)
- In het eerste deel van de formule wordt bepaald welke waarden uit welke kolom bij elkaar opgeteld moeten worden. In dit geval de waarden in cellen D1 tot en met D25.
- Op de plaats van tweede deel van de formule (A1:A25) plaatsen we het eerste criteria waaraan in het bereik aan voldaan moet worden. In dit geval “Mandarijnen”. Let op! De waarde moet tussenaanhalingstekens worden geplaatst.
- In het derde deel van de formule (B1:B25) plaatsen we het tweede criteria waaraan in het bereik voldaan moet worden. In dit geval “Mineola”
Het resultaat is in dit geval EUR 56.000
Verander eventueel het criterium “Mandarijnen” in “Peren” en bekijk wat het resultaat wordt. Selecteer in plaats van het selectiecriterium “Ras” (B1:B25) eens de kolom “jaar” (C1:C25). Daarmee wordt nu het totaal per fruitsoort per jaar voor alle rassen berekend.
De SOMMEN.ALS formule met een absolute verwijzing
Het kan heel handig zijn om in de SOMMEN.ALS formule niet te werken met vaste waarden zoals “Peren” of “Mandarijnen” maar met een dynamische selectie. Er wordt een totaal berekend op basis van de selectie afhankelijk van de waarde die we invoeren. In het voorbeeld SOM.ALS is al uitgelegd hoe dit werkt. Voor SOMMEN.ALS werkt dit precies op dezelfde wijze.
Optellen op basis van meerdere criteria met SOMMEN.ALS
Optellen op basis van meerdere criteria met SOMMEN.ALS. In dit artikel heb ik uitgelegd hoe dit werkt. Het is eenvoudig en zeer krachtig. Doe er je voordeel mee.
Succes met excelleren! Krijg je geen genoeg van Excel? Ga dan aan de slag met onze leerzame en handige Excel sjablonen
-
Sjabloon voor het vastleggen van fietsprestaties€6,95 incl. BTW -
Sjabloon voor het vastleggen van hardloopprestaties€6,95 incl. BTW -
Excel jaarkalender sjabloon – tot in lengte van jaren€4,95 incl. BTW -
Sjabloon huishoudboekje – weet wat je nog te besteden hebt€4,95 incl. BTW -
Sjabloon voor aanwezigheidsregistratie van team of afdeling€4,95 incl. BTW -
Sjabloon voor het gestructureerd vastleggen van taken€4,95 incl. BTW -
Sjabloon voor het maken van een projectplanning€4,95 incl. BTW -
Sjabloon paklijst ter voorbereiding op de reis of vakantie€4,95 incl. BTW -
Sjabloon voor het berekenen van eindkapitaal€4,95 incl. BTW
Beste Excelleren,
Bedankt voor de nuttige info. Ik heb een vraag over de sommen.als functie. Als ik in een tabel een datum reeks heb van begin 2010 tot nu, hoe kan ik alleen de datums in 2019 als criteria nemen? Zodra ik als criteria 2019 of -19 neem krijg ik een eind waarde van 0.
Alvast bedankt.
Hoi, ik adviseer hier een matrix formule. Die ziet er als volgt uit. =SOM((JAAR(A1:A4)=2019)*(B1:B4))
Hierbij wordt in feite de gehele tabel in één keer berekend. In dit geval staan in velden A1 t/m A4 de datums en wordt bepaald welke datums in het jaar 2019 vallen. Van die regels worden de waarden in cellen B1:B4 opgeteld. LET OP! Een matrix formule voeg je niet toe door enter maar door CTRL+SHIFT+ENTER. in de formule balk wordt de formule tussen accolades geplaatst.
Zeer handige tip en heldere uitleg.
Wat ik nog niet kan vinden is optellen van getallen in een kolom ontstaan door een formule.
EEN VOORBEELD
In cel B1 t/m B10 staat formule
ALS(A1=>0;”1”)
En dit doortrekken naar B10
In B11
SOM(B1:B10)
Blijft bij mij in deze combinatie staan op ”nul”.
Misschien kan dit helemaal niet?
Hi,
Dat kan wel en je bent er bijna. In de ALS formule heb je nu de 1 tussen aanhalingstekens staan waardoor Excel de waarde als tekst ziet. Die kun je inderdaad niet optellen. Maar als je de aanhalingstekens verwijderd wordt de uitkomst als getal opgenomen. Die kun je wel optellen.
Succes met Excelleren!
Beste Excelleren,
Ik zoek een mogelijkheid om met “SOMMEN.ALS” een deel op te tellen wat groter is dan een bepaalde datum. In dit geval groter dan 1 november, datumgetal 94490.
Mijn formule is:
=SOMMEN.ALS(‘Totaal’!G:G;’Totaal’!A:A;”>94490″;’COVID-19_aantallen’!B:B;Blad1!A17)
Ook al geprobeerd met de waarde “94490” in een cel te zetten en daar naar te verwijzen.
Beste Gert,
Dank voor je vraag. De formule ziet er als volgt uit =SOMMEN.ALS(D1:D4;C1:C4;”>30-09-2020″) waarbij in dit voorbeeld:
– De getallen worden opgeteld in de cellen D1:D4
– Als in de cellen C1:C4 de datum groter is dan 30-09-2020
Je kunt dus de Sommen.als formule gewoon gebruiken met normale datumnotaties. Dus in jouw voorbeeld:
=SOMMEN.ALS(‘Totaal’!G:G;’Totaal!A:A;”>01-11-2020″)
– De getallen worden opgeteld in de cellen van kolom G
– Als in de cellen van kolom A de datum groter is dan 01-11-2020
Ik denk dat je er zo wel uit komt. Succes met Excelleren
Hi,
Ik zit met de volgende vraagstelling. Heb een excelexport met 3 kolommen:
A datum (vb 2-2-2021)
B tijdslot (per 5 min) (vb 07:00:00), soms staat er 14x 07:05:00 in en 2x 07:10:00 in
C omzet per tijdslot. (vb 3928)
Per tijdslot verschilt de omzet.
Nu wil ik weten wat de omzet is per tijdslot van 5 min. Bijvoorbeeld 08:05:00 – 08:10:00. Hoe voeg ik de tijdsloten samen zodat ik de omzet per tijdslot van 5 minuten zie?
Ik hoor het graag.
Aaron
Hi Aaron, je kunt dit op meerdere manieren oplossen. Ik zou hier persoonlijk niet de sommen.als formule voor gebruiken.
1. Door het gebruik van een draaitabel. Daarin kun je vrij eenvoudig de bedragen optellen per tijdslot
2. Door het gebruik van een subtotaal (gegevens – subtotaal)
Complimenten voor deze uitleg van sommen.als. Mijn aandelen portfolio tracker maakt nu gebruik van deze formule: op basis van de transacties wordt per aandeel de aantallen, aankoopprijs eta over tijd in een tabel getoond 🙂
Beste Excelleren,
Ik kom er niet uit. Ik probeer de volgende formule:
=SOMMEN.ALS(Bron!$C:$C;Bron!$B:$B;”wk 02-2022″;Bron!$D:$D;”A”)
Mijn antwoord komt steeds op nul uit. Terwijl dit 911 moet zijn.
Ik moet de som van mijn optelbereik, als het voldoet aan voorwaarde “wk 02-2022” in kolom B en voorwaarde “A” in kolom D.
Help! Alvast bedankt!
Hallo,
Ik ben op zoek naar een formule die een som maakt van meerdere kolommen (som.als) én meerdere criteria (sommen.als). Is dit mogelijk? som.als telt wel meerdere kolommen op maar uitsluitend obv 1 criteria. Sommenals telt op in 1 kolom met meerdere criteria maar heeft geen optelbereik van meerdere kolommen. Heb je een oplossing?
Ja, het is mogelijk om zowel meerdere kolommen als meerdere criteria op te tellen in Excel met behulp van de functie SOMMEN.ALS. Hieronder vind je een voorbeeld van hoe je deze formule kunt gebruiken:
=SOMMEN.ALS(bereik1; criteria1; bereik2; criteria2) Dit betekent dat je meerdere bereiken en criteria kunt opgeven om op te tellen. In dit voorbeeld worden twee bereiken (bereik1 en bereik2) en twee criteria (criteria1 en criteria2) gebruikt om de som te bepalen.
Als je bijvoorbeeld de waarden wilt optellen in kolom B en C, maar alleen als de waarde in kolom A “Ja” is en de waarde in kolom D “X”, dan kun je de volgende formule gebruiken:
=SOMMEN.ALS(B1:C10; A1:A10; “Ja”; D1:D10; “X”)
De formule telt de waarden op in bereik B1:C10, maar alleen als de bijbehorende waarden in A1:A10 “Ja” zijn en de bijbehorende waarden in D1:D10 “X” zijn. Let erop dat je het bereik en het criterium voor elk argument correct opgeeft en dat het aantal opgegeven bereiken en criteria overeenkomt.
Hallo,
Wanneer ik een rij getallen heb, laat ik dit optellen door de AutoSom, maar ik wil de som uitbreiden. De laagste 2 getallen worden niet meegerekend (inclusief een leeg veld).
Is het mogelijk om dit in 1x te laten berekenen?
(uiteraard is er sprake van meerdere rijen)
Hi, ja dit is zeker mogelijk. In de som formule kun je aangeven welke cellen (kolom/ rij combinaties) je mee neemt in je berekening. Stel je hebt een optelling van de waarden in de cellen A1 t/m E1 dan ziet de formule er als volgt uit =SOM(A1:E1). Wanneer je vervolgens meerdere cellen in de berekening mee wilt nemen dan breid je het bereik uit. Bijvoorbeeld =SOM(A1:H1). Let op dat je voor de cel met de formule een andere cel kiest dan de reeks waarden die je optelt.