SOMMEN.ALS | Eenvoudige uitleg & geavanceerde toepassingen
De formule SOMMEN.ALS telt meerdere waarden in een bereik op die aan meerdere opgegeven voorwaarden of criteria voldoen.
Als je een optelling wilt doen met slechts één voorwaarde, dan gebruik je deze functie SOM.ALS.
SOMMEN.ALS is een functie om cellen uit een tabel op te tellen die aan meerdere criteria voldoen. SOMMEN.ALS kunnen worden gebruikt om waarden op te tellen wanneer cellen in andere kolommen uit dezelfde rij voldoen aan criteria op basis van datums, getallen en tekst.
SOMMEN.ALS, of in het Engels SUMIFS, ondersteunt ook logische operatoren (>, <, <>, =) en jokertekens (* ,?) voor gedeeltelijke matching.
Het is daarmee dus heel erg flexibel in het gebruik en het is een van de functies die ik het meeste gebruik in Excel.
Hoe gebruik je SOMMEN.ALS?
De formule ziet er als volgt uit:
=SOMMEN.ALS(optelbereik;criteriumbereik1;criterium1;[criteriumbereik2; criterium2])
- Optelbereik: De cellen die je wilt optellen als aan de criteria wordt voldaan.
- Criteriumbereik: Het bereik dat moet voldoen aan de opgegeven criteria.
- Criterium: De opgegeven criteria waaraan voldaan moet worden.
Ik merk dat veel mensen aan wie ik de formule uitleg nu nog niet goed snappen wat ze nou precies waar in moeten vullen. Criteriumbereik? Wat?!?
Wat eenvoudiger uitgelegd is Optelbereik de kolom met de waarde die je als resultaat wilt berekenen, mits er in een andere kolom (criteriumbereik) een bepaalde waarde (criterium) voorkomt.
Voorbeeld van SOMMEN.ALS
Kopieer onderstaande tabel naar Excel om het voorbeeld te volgen.
| 130 | Ja | Excelweb |
| 143 | Nee | |
| 121 | Nee | |
| 128 | Ja | |
| 149 | Ja | Excelweb |
We willen alleen de getallen optellen met Ja en Excelweb erachter. Daarom gebruiken we onderstaande SOMMEN.ALS formule:
=SOMMEN.ALS(A1:A5;B1:B5;”Ja”;C1:C5;”Excelweb”)
Je kunt die formule waar dan ook in je Excel sheet plakken. Het resultaat is: 279:
Gebruiksnotities
De functie SOMMEN.ALS van Excel somt cellen in een bereik op met behulp van de opgegeven criteria. In tegenstelling tot de SUMIF-functie (of SOM.ALS) kan SUMIFS en SOMMEN.ALS meer dan één set criteria toepassen, met meer dan één bereik.
Het eerste bereik is het bereik dat moet worden opgeteld. De criteria worden geleverd in paren (bereik / criteria) en alleen het eerste paar is vereist, maar je kunt er dus meer toevoegen als je wilt.
Geef voor extra criteria een extra bereik / criteria-paar. Tot 127 bereik / criteria-paren zijn toegestaan.
SOMMEN.ALS formule kopiëren met celverwijzingen
Als pro tip hier nog een gebruik voor SOMMEN.ALS die ik heel vaak toepas en waardoor het enorm flexibel wordt om te gebruiken in je Excel berekeningen:
Je kunt de SOMMEN.ALS formule gemakkelijk kopiëren van de ene cel naar de andere om verschillende inzichten te berekenen, mits je goed gebruikt maakt van rijen en kolommen vastzetten met het $ teken.
In het voorbeeld van hierboven heb ik een tabelletje gemaakt die in één keer een snelle berekening kan maken van alle mogelijkheden, zonder dat je telkens de formule opnieuw hoeft te typen:
Je mag dit vergeten als het te diep in de materie gaat hoor, maar het helpt je wel te begrijpen wat er gebeurt en om formules in Excel, elke oort formules, erg flexibel in te zetten en te kopiëren:
- In cel H2 heb ik de formule getypt en deze gekopieerd en geplakt naar cellen H2:I3
- Gelijk maakt de formule de juiste berekeningen in de tabel voor Excelweb of leeg in kolom C in combinatie met Ja of Nee in kolom B
- Wanneer je geen $ tekens gebruikt en je kopieert een formule naar een andere cel, dan verschuift Excel automatisch alle verwijzingen mee. Dat kan handig zijn maar in ons geval willen we dat niet. Anders zou het kopiëren van de formule in H2 naar I2 er voor zorgen dat ons optelbereik ineens kolom B is geworden en de criteriumbereiken naar C en D (waar niets eens iets staat)
- Om ervoor te zorgen dat de boel niet mee verschuift gebruiken we $ tekens, voor het gehele optelbereik en de twee criteriumbereiken: =SOMMEN.ALS($A$1:$A$5;$B$1:$B$5;”Ja”;$C$1:$C$5;”Excelweb”)
- Dat betekent een $ voor elke kolomletter, én een $ teken voor elk rij cijfer
- Nu kijkt de formule nog altijd naar een hard ingevoerde “Ja” in kolom B en een hard ingevoerde “Excelweb” in kolom C. Om de formule flexibel te maken kunnen we deze hard ingevoerde teksten omzetten in een celverwijzing: =SOMMEN.ALS($A$1:$A$5;$B$1:$B$5;H1;$C$1:$C$5;G2)
- De formule zoekt nu naar wat er in cel H1 staat in kolom B (“Ja”) en wat er in cel G2 staat in kolom C (“Excelweb”)
- Wanneer we de formule nu kopiëren van cel H2 naar cel I2, dan blijven de kolommen in de tabel netjes staan, want die hadden we vastgezet, maar verschuift de celverwijzing in de formule wel nog gewoon mee, en dit is ook niet helemaal wat we willen. Hij kijkt nu wel naar “Nee”, maar niet meer naar “Excelweb”:
- De laatste stap om SOMMEN.ALS flexibel te maken met kopiëren is om de celverwijzing op de juiste manier vast te zetten met $ tekens. Je wilt dat het criterium voor kolom B nog wel naar beneden kan schuiven wanneer je naar beneden toe kopieert, maar niet naar rechts. En je wilt dat het criterium voor kolom C nog wel naar rechts kan schuiven maar niet naar beneden. De formule pas je aan met een $ voor het rijcijfer van het eerste criterium, en een $ teken voor de kolomletter van het tweede criterium. Dat geeft: =SOMMEN.ALS($A$1:$A$5;$B$1:$B$5;H$1;$C$1:$C$5;$G2)
Nogmaals, mag je allemaal weer vergeten als het voor nu te ver gaat of experimenteer er wat mee en kom later nog eens terug naar dit artikel om het weer eens door te nemen.
Opmerkingen
- Je kan wel tot 127 paren van criteriumbereik en criterium hebben.
- Je moet evenveel keer criteriumbereik als criterium invullen, anders kan je de formule niet invoeren.
- #WAARDE! wordt weergegeven als je tekst in de in bepaalde cellen in het optelbereik zet in plaats van getallen.
- Elk extra bereik moet hetzelfde aantal rijen en kolommen hebben als het sombereik.
- Niet-numerieke criteria moeten tussen dubbele aanhalingstekens staan, maar voor numerieke criteria zijn geen aanhalingstekens nodig, behalve bij operatoren, d.w.z. “> 32”
- De jokertekens? en * kunnen in criteria worden gebruikt. Een vraagteken komt overeen met elk teken en een asterisk komt overeen met elke reeks tekens.
- Om een letterlijk vraagteken of asterisk te vinden, gebruikt u een tilde (~) voor het vraagteken of asterisk (d.w.z. ~ ?, ~ *).
- SUMIF en SUMIFS kunnen reeksen verwerken, maar geen arrays. Dit betekent dat u geen andere functies zoals JAAR op het criteriabereik kunt gebruiken, omdat het resultaat een array is. Als u deze functionaliteit nodig hebt, gebruikt u de functie SOMPRODUCT.
- De volgorde van argumenten is verschillend tussen de functies SOMMEN en SOMMEN.ALS wat veel gebruikers verwarrend vinden. Sum_range is het eerste argument in SUMIFS, maar het derde argument in SUMIF.
Is het ook mogelijk het optelbereik over meerdere kolommen (bijvoorbeeld januari tot en met juni) op te geven?
Bijvoorbeeld optelbereik b2:g28
In jouw geval, waarbij je de som wilt berekenen over meerdere kolommen (bijvoorbeeld van januari tot en met juni), kun je het som_bereik opgeven als “B2:G28”.
Hier is een voorbeeld van hoe je dit zou kunnen doen:
Stel dat je een tabel hebt waarin kolom B tot en met G de cijfers voor de maanden januari tot en met juni bevatten, en je wilt de som berekenen van de waarden die voldoen aan een bepaald criterium in kolom A.
Formule voor het totaal van januari tot en met juni:
=SOMMEN.ALS(B2:G28, A2, B2:G2)
In deze formule wordt `B2:G28` opgegeven als het bereik waarin wordt gezocht naar het criterium (de waarde in cel A2). Als het criterium in cel A2 wordt gevonden in een cel in het bereik B2:G2, wordt de som berekend over de overeenkomstige cellen in het bereik B2:G28.
Pas de formule aan op basis van je specifieke gegevens en criteria, en je kunt de som berekenen over meerdere kolommen met de functie SOMMEN.ALS.
ik heb een verlof kaart
dit is het hele gebied =SOM(B11:M41)
ik wil alleen de sommen als formule weten dat alle getallen groter zijn dan 0 in hok o 22 bij elkaar worden opgeteld stel ik heb 1 uur extra gewerkt en een andere dag 5 uur extra dan heb ik 6 uur gewerkt etc etc zonder dat exel andere cellen moet controleren is nu niet nodig
Jan Feb mrt apr mei juni juli aug sept okt nov dec
1 -8
2 -4
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 -8
22 -8
23 -8
24 -8
25 -4
26
27 -8
28 -8
29 -8 -8
30 -8 -4
31 8 -8
Om de som te berekenen van alle getallen groter dan 0 in het gebied B11:M41 in Excel en dit resultaat in cel O22 te plaatsen, kun je de volgende formule gebruiken:
=SUMIF(B11:M41,”>0″)
Deze formule zal alle waarden in het opgegeven gebied B11:M41 controleren en alleen de waarden groter dan 0 optellen.
Het resultaat zal in cel O22 verschijnen. Houd er rekening mee dat als er in het bereik negatieve getallen zijn, deze niet worden opgeteld, zoals aangegeven in jouw gegevens.
De formule houdt alleen rekening met getallen groter dan 0.
Hi,
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!
Lijkt een probleem met de formule.
Het gebruik van de functie `SOMMEN.ALS` is correct, maar het lijkt een kleine fout is in de manier waarop de voorwaarden worden opgegeven.
Probeer de formule als volgt aan te passen:
=SOMMEN.ALS(Bron!$C:$C; Bron!$B:$B; “wk 02-2022”; Bron!$D:$D; “A”)
Zorg ervoor dat je de juiste tekstwaarden “wk 02-2022” en “A” gebruikt in de voorwaarden en dat deze precies overeenkomen met wat er in de kolommen B en D staat in het werkblad “Bron”.
Als de gegevens correct zijn en de voorwaarden overeenkomen met de waarden in de genoemde kolommen, zou deze formule de som van de overeenkomende waarden in kolom C moeten geven.
Let op dat Excel gevoelig is voor de gebruikte taal en regiogegevens. In sommige gevallen moet je mogelijk de puntkomma’s (;) in de formule vervangen door komma’s (,).
Als je nog steeds nul krijgt als resultaat, controleer dan de gegevens in de bronkolommen B en D om ervoor te zorgen dat ze exact overeenkomen met de voorwaarden in de formule.
Hoe kan je waardes van cellen, in 1 kolom, bij elkaar optellen met een voorwaarde (de eerste 5 karakters zijn gelijk) in een andere cel/kolom.
Toelichting:
Ik wil de waardes van de cellen in kolom B bij elkaar optellen maar dit mag alleen als de eerste 5 karakters van de waardes in de cellen van kolom A aan elkaar gelijk zijn.
De waardes in kolom B zijn getallen.
De waardes in kolom A zijn waardes met letters en cijfers door elkaar. De waarde begint bijvoorbeeld met C0052 waarna er ook nog meer karakters kunnen volgen. De waardes in kolom A kunnen dus bijvoorbeeld zijn C0052, C0052X, C0052XX etc. Maar ook D0041 etc. Zolang de eerste 5 karakters maar gelijk zijn aan elkaar.
Hoe krijg je de voorwaarde [als de eerste 5 karakters van de waarde in kolom A aan elkaar gelijk zijn, dan mogen deze bij elkaar opgeteld worden] in een formule?
En dan wil ik eigenlijk ook nog de voorwaarde toevoegen dat als kolom A dezelfde datum heeft dat ze dan pas bij elkaar opgeteld mogen worden.
Alvast bedankt!
Je kunt onderstaande eens proberen, stel, je hebt de volgende gegevens in Excel:
– Kolom A bevat de waarden met een mix van letters en cijfers.
– Kolom B bevat de bijbehorende getallen.
– Kolom Z bevat de datums.
Je wilt de getallen in kolom B optellen wanneer de eerste 5 karakters in kolom A gelijk zijn en de datums in kolom Z ook gelijk zijn.
1. In kolom C, gebruik de formule `=LINKS(A2,5)` om de eerste 5 karakters van de waarden in kolom A te extraheren. Pas deze formule toe op alle relevante rijen.
2. In cel D2 (of een andere lege cel waar je de som wilt berekenen), gebruik de volgende formule:
=SOMMEN.ALS(B:B, C:C, C2, Z:Z, Z2)
Deze formule telt de waarden in kolom B op als de eerste 5 karakters in kolom A (uit kolom C) gelijk zijn aan het unieke 5-karakterpatroon in cel C2, en als de datum in kolom Z (uit cel Z2) gelijk is aan de datum in dezelfde rij.
ik krijg iedere keer een 0 als resultaat. Ik heb exact hetgeen gedaan wat jij aangeeft. Ik had het ook bij een andere formule. Heb jij een idee hoe dit komt?
Zorg ervoor dat de criteria in de formule exact overeenkomen met de gegevens in de tabel.
“Ja” en “Excelweb” moeten precies hetzelfde zijn als in de tabel, inclusief eventuele spaties of hoofdletters.
Controleer ook of er geen verborgen spaties of onzichtbare tekens zitten in de gegevens uit de tabel.
Deze kunnen soms problemen veroorzaken bij het vergelijken van tekstwaarden.