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.

Een eenvoudige manier om meerdere G...
Een eenvoudige manier om meerdere Google Maps locaties toe te voegen [wordpress]

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.

130JaExcelweb
143Nee
121Nee
128Ja
149JaExcelweb

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:

Voorbeeld sommen als gebruik

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:

sommen als formule kopieren

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)Verschoven optelbereik in sommen als formule
  • 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”:Celverwijzing in sommen als formule
  • 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.

3 replies on “SOMMEN.ALS | Eenvoudige uitleg & geavanceerde toepassingen”

  • Is het ook mogelijk het optelbereik over meerdere kolommen (bijvoorbeeld januari tot en met juni) op te geven?
    Bijvoorbeeld optelbereik b2:g28

  • 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

  • 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!

Leave a Comment

Het e-mailadres wordt niet gepubliceerd.