Sommeren over meerdere criteria

Voorbeeldbestanden bij dit artikel:
  • Sommeren over meerdere criteria
  • Inleiding

    Over dit onderwerp is al veel geschreven, er zijn in Excel namelijk veel verschillende manieren om sommen te maken waarbij je meerdere criteria moet toepassen:

    • =SOM() / SUM() en zelf de juiste selectie maken
    • =DBSOM() / DSUM()
    • =SOMPRODUCT / SUMPRODUCT()
    • =SOM() / SUM() als matrixfunctie, …
    • =SOMMEN.ALS() / SUMIFS() (vanaf Excel 2007)
    • =PRODUCTMAT() / MMULT()
    • een draaitabel gebruiken, al dan niet met slicers en timelines
    • een draaitabel gebruiken, met SQL expressies (forumlink)
    Zelfs relatief eenvoudige lijstjes in Excel en wat eenvoudige bewerkingen lijken te zijn, kunnen in Excel aardig lastig uitdraaien, als je manueel werk wil vermijden. Er zit daarbij vaak een spanningsveld tussen "een mooie handige layout" en "eenvoudige Excel formules": je maakt Excel formules lastiger te schrijven wanneer de input eerder gericht is op presentatie en navigatie, eerder dan te dienen voor eenvoudigere Excel formules. Ik verduidelijk mezelf. Heel vaak hebben we in Excel gewone simpele sommen nodig, zoals de bierconsumptie van 30 november tot en met 6 december:

    In de tabel worden kolommen gebruikt om de drankconsumptie per dag bij te houden: de tabel breidt dus uit aan de rechterkant. Willen we de consumptie kennen van 30 november tot en met 6 december dan sommeren we gewoon de gele cellen, of de vetgedrukte dagtotalen van kolommen E tot en met J. Als je zelf de bereiken in deze formule wil aanpassen, stop dan maar met lezen: dat is uiteraard de eenvoudigste oplossing. Voor alle andere personen zal dit artikel de formules tonen om een dynamisch bereik te nemen van datums. Bovendien, wat als we enkel de consumptie willen kennen van 1 biersoort, zoals La Chouffe ? Blijkt uit de tabel dat de input voor 1 biersoort kan gebeuren op meerdere rijen (zie rijen 3 en 5).

    Afhankelijk van de input en hoe je die structureert (of niet structureert) kost het jezelf weinig of veel moeite om tot werkende formules te komen: een platte tabel met kolom A: Biersoort, kolom B: Datum, kolom C: Consumptie werkt volgens mij het handigste. De rijen breiden onderaan de tabel uit maar de tabel zal steeds dezelfde 3 kolommen bevatten.

    Sinds Excel 2013 hebben we ook timelines / tijdslijnen die je kan inzetten bij een draaitabel. Inderdaad, je leest het goed: zonder enige formule kan je ook hetzelfde resultaat bewerkstelligen. Dat is een van de grote voordelen van draaitabellen, je moet er geen enkele formule voor maken. Baseer je de draaitabel op een tabel dan breidt de draaitabel zich automatisch uit wanneer je hem ververst. Sinds Excel 2010 hadden we al slicers voor draaitabellen, die tevens toelaten om draaitabellen te filteren.

    Hoe sommeren we cellen op een snelle manier

    Sinds Excel 2010 hebben we ook timelines / tijdslijnen die je kan inzetten bij een draaitabel. Inderdaad, je leest het goed: zonder enige formule kan je ook hetzelfde resultaat bewerkstelligen. Dat is een van de grote voordelen van draaitabellen, je moet er geen enkele formule voor maken. Baseer je de draaitabel op een tabel dan breidt de draaitabel zich automatisch uit wanneer je hem ververst.

    Alle opgesomde formulevarianten worden getoond in het bestand bovenaan de pagina, evenals de draaitabel met tijdslijn. Dit bestand is een must-have als je wil sommeren over meerdere criteria !




    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links