Getallen optellen op basis van meerdere voorwaarden
September 2nd, 2007
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’.
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.
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”.
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).
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:
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
October 29th, 2007 at 7:49 am
[…] exacte werking van SOMPRODUCT heb ik beschreven in het artikel ‘Getallen optellen op basis van meerdere voorwaarden’. Dit is een ander voorbeeld waarbij deze formule mooi van pas komt. Door nu in cel C3 de huidige […]
November 21st, 2007 at 10:39 pm
Voorbeeld:
In de kolom a1:a10 staan bijvoorbeeld zes getallen rood gekleurd (NIET de celkleur!) en vier getallen zwart. Hoe kan ik de rood gekleurde getallen door de functie ’somproduct’ laten totaliseren?
December 3rd, 2007 at 5:32 am
[…] aanleiding van mijn artikel over de werking van de SOMPRODUCT functie (Getallen optellen op basis van meerdere voorwaarden), ontving ik van iemand de vraag of het mogelijk was om met behulp van deze functie getallen met […]
December 4th, 2007 at 1:01 pm
@Frank:
In het artikel Rekenen met kleur vind je een uitgebreide uitleg over hoe je met behulp van een eigengemaakte functie de kleurenindex van het lettertype van een cel kunt bepalen. Met behulp van deze kleurenindex kun je vervolgens rekenen (met bijvoorbeeld SOMPRODUCT of SOM.ALS)
February 6th, 2008 at 9:10 pm
[…] Ik heb jaren met Excel gewerkt zonder gebruik te maken van draaitabellen (engels: pivot table). Totdat iemand mij liet zien hoe het werkte. Vanaf dat moment is het voor mij één van de meest gebruikte functies. In dit artikel zal ik kort uitleggen hoe je een draaitabel maakt en wat je er vervolgens mee kunt doen. We gebruiken hiervoor de volgende gegevens (geleend uit het artikel : Getallen optellen op basis van meerdere voorwaarden). […]
June 16th, 2008 at 10:03 pm
Thanks a lot ! ! ! works great
September 7th, 2009 at 5:11 am
[…] het artikel “Getallen optellen op basis van meerdere voorwaarden“hebben we gezien hoe je met de functie SOMPRODUCT getallen kunt optellen op basis van […]
September 15th, 2010 at 1:47 pm
Hallo,
Ik weet dat dit onderwerp een beetje veroudert is. Maar ik heb een probleem met de functie som.als in excel. ik heb uit een grote database eerst handmatig alle voorwaarden gekopieerd met een filter. Nu heb ik som met de hele kolom gedaan. maar als ik dat met de “hand” na wil doen komt hier iets anders uit. maar wanneer ik de zelfde kolom met criterea gebruik en een andere kolom met een optelbereik dan komt wel het juiste getal er uit.
Nu is mijn vraag, wat doe ik fout..
Mijn cellen heb ik allemaal al aangepast van standaard, naar tekst, naar getal. maar dit helpt allemaal niet..
groeten
Anne
April 26th, 2011 at 11:14 pm
[…] niks van excel,maar heb het volgende artikel gevonden.Ik hoop dat deze je het antwoord verschaft. http://www.zowerktexcel.nl/functies/…e-voorwaarden/ Antwoord met Citaat + Plaats een Reactie « Vorig Onderwerp | […]
July 16th, 2011 at 3:37 pm
[…] op deze link In deze link staat onderstaande tekst. […]
November 29th, 2011 at 12:34 am
[…] –: zie http://www.xldynamic.com/source/xld….T.html#explain (zoek op — op die pagina) of: Zo werkt Excel
January 4th, 2012 at 4:35 pm
ik wou het per artikelcode groep optellen nu had ik dan alles geselecteerd en als criteruim A4000 maar dan geeft hij 0