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.

Het gebruik van SUMIFS

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”)

Een voorbeeld  van de SOMMEN.ALS formule

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.

SOMMEN.ALS formule

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.

Commentaren

  1. de Kwant

    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.

    1. Excellent Artikel auteur

      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.

  2. JdB

    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?

    1. Excellent Artikel auteur

      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!

  3. Gert

    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.

    1. Excellent Artikel auteur

      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

  4. Aaron

    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

    1. Excellent Artikel auteur

      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)

Geef een antwoord

Het e-mailadres wordt niet gepubliceerd.

Deze website gebruikt Akismet om spam te verminderen. Bekijk hoe je reactie-gegevens worden verwerkt.