Getallen optellen op basis van meerdere voorwaarden

Update: Voor gebruikers van Excel 2007 is er een alternatief voor het gebruik van SOMPRODUCT: Getallen optellen op basis van meerdere voorwaarden met SOMMEN.ALS

Bij het maken van rapportages in Excel komt het regelmatig voor dat je getallen wil optellen op basis van een voorwaarde. In het onderstaande overzicht zie je voor iedere productgroep de omzet per regio en per kwartaal. Het overzicht sluit af met de totale omzet voor de productgroep ‘Groente’.

som.als

De formule die we hiervoor kunnen gebruiken is SOM.ALS (Engels: SUMIF). Deze functie heeft de volgende argumenten: SOM.ALS(bereik; criterium; [optelbereik]). De functie telt de getallen in het optelbereik op die voldoen aan het criterium. In het voorbeeld kijkt de functie naar het bereik C2:C9. Voor ieder regel die voldoet aan het Criterium “Groente” (hier hadden we ook cel B11 voor kunnen opgeven) wordt de omzet opgeteld. Het optelbereik is hier de omzetkolom, D2:D9.

De functie SOM.ALS werkt prima als je wil optellen op basis van één criterium. Echter de vraag wat de totale omzet is van Fruit in de regio zuid, kan niet op deze manier worden opgelost. Deze vraag kan op verschillende manieren worden opgelost. Eén van de meest charmante oplossingen vind ik het gebruik van de functie SOMPRODUCT (Engels: SUMPRODUCT). Deze functie vermenigvuldigt twee of meer matrices met elkaar en berekent de som van de producten. Kijk eens naar het onderstaande voorbeeld.

somproduct

In kolom C staat het product van de cellen in kolom A en B. In cel C8 worden deze resultaten met een SOM functie bij elkaar opgeteld. In cel C10 berekenen we hetzelfde in één stap met de functie =SOMPRODUCT(A2:A6;B2:B6).

Nu weer terug naar ons oorspronkelijke voorbeeld. We willen de totale omzet van de productgroep Fruit in de regio Zuid weten. Dit betekent dat we die getallen willen optellen waar de waarde van kolom A gelijk is aan “Zuid” en kolom C gelijk is aan “Fruit”.

somproduct-ipv-som.als

In de regels 12 t/m 19 heb ik deze formules ingevoerd. Kolom E is de vermenigvuldiging van kolom A, C en D. Excel interpreteert WAAR als 1 en ONWAAR als 0. Deze opstelling lijkt veel op ons somproduct voorbeeld. De totale omzet voor de productgroep Fruit in de regio Zuid is gelijk aan het somproduct van deze drie kolommen. De functie ziet er dan als volgt uit: =SOMPRODUCT(A2:A9=”Zuid”; C2:C9=”Fruit”; D2:D9). Helaas werkt deze functie nog niet helemaal. Het resultaat is 0. De oorzaak hiervoor is te zien in het dialoogvenster functie invoegen (te bereiken via de knop fx).

functiedialoog-somproduct-1

Hieruit blijkt dat Excel de waarden ONWAAR en WAAR nog niet vertaald heeft door 0 en 1. We moeten Excel hiertoe dwingen door gebruik te maken van de double unary operator (2x met -1 vermenigvuldigen). De functie wordt dan: =SOMPRODUCT(- -(A2:A9=”Zuid”);- -(C2:C9=”Fruit”);D2:D9). Het bijbehorende functie dialoogvenster:

functiedialoog-somproduct-2

Je ziet dat Excel nu wel de vertaling naar 0 en 1 heeft gemaakt en dat het correcte resultaat van 13000 wordt berekend. Op deze manier kun je de functie SOMPRODUCT gebruiken om getallen op te tellen op basis van meerdere criteria. De functie werkt met maximaal 30 matrices. Dit betekent dat je een som kunt berekenen op basis van maximaal 29 criteria.

Update: Voor gebruikers van Excel 2007 is er een alternatief voor het gebruik van SOMPRODUCT: Getallen optellen op basis van meerdere voorwaarden met SOMMEN.ALS

BewarenBewaren