Het Excel bestand dat hoort bij deze pagina over SOMPRODUCT en SOM(MEN).ALS staat onderaan de tekst en mag gratis worden gedownload voor eigen gebruik.
Inleiding
Ik kom SOMPRODUCT in de praktijk maar weinig tegen; Excel gebruikers lijken liever te werken met de SOM(MEN).ALS functie. Toch is SOMPRODUCT in veel gevallen effectiever. Je kunt er zonder tussenberekeningen direct getallen mee vermenigvuldigen én optellen. Ook kun je (tekstuele) voorwaarden toepassen zonder de IF functie te gebruiken.
Functie-opbouw
De SOMPRODUCT functie is als volgt opgebouwd:
SOMPRODUCT(matrix1;matrix2;matrix3;...)
In gewone woorden staat hier dat je celreeksen (‘matrix’) van gelijke grootte kunt aanwijzen, met elkaar vermenigvuldigen (PRODUCT) en vervolgens optellen (SOM).
SOMPRODUCT basis
Stel je hebt een IT-bedrijf en zet projectleiders, programmeurs en testers aan het werk bij klanten. In Excel houd je medewerkers, functies, commerciële uurtarieven en marges bij. Voor de maanden januari, februari en maart registreer je vervolgens het aantal facturabele uren per medewerker. Al deze gegevens staan in de grijze kolommen A tot en met G.
Voorbeeld 1 – Tab ‘SOMPRODUCT basis’
Ik wil in de gele cellen E14:G14 de totale omzet per maand tonen. In de gele cellen E15:G15 wil ik de totale marge per maand tonen.
Formule E14
=SOMPRODUCT($C$2:$C$12;E2:E12)
Formule E15
=SOMPRODUCT($C$2:$C$12;E2:E12;$D$2:$D$12)
De formule in cel E14 kun je als volgt ontleden:
- Excel selecteert celreeks C2:C12 (‘matrix’) met uurtarieven. De $-tekens zorgen ervoor dat deze celreeks vast blijft staan bij het kopiëren van de formule naar F14 en G14.
- Excel selecteert vervolgens celreeks E2:E12 met gewerkte uren.
- De 2 geselecteerde celreeksen van gelijke grootte – namelijk 11 cellen elk – worden met elkaar vermenigvuldigd (PRODUCT) en opgeteld (SOM).
De omzet-berekening in cel E14 ziet er in getallen zo uit:
(85*120) + (100*120) + (95*140) + (100*120) + (100*120) + (110*120) + (75*160) + (95*140) + (80*120) + (90*140) + (80*120) = 129.800
De formule in cel E15 is gelijk aan de formule in cel E14, met toevoeging van de celreeks ‘Marge’: =SOMPRODUCT($C$2:$C$12;E2:E12;$D$2:$D$12) Immers, om de marge te berekenen dien je de omzet te vermenigvuldigen met het marge-percentage. De marge-berekening in cel E15 ziet er in getallen zo uit:
(85*120*80%) + (100*120*75%) + …etc… + (80*120*75%) = 100.345
Kan ik de SOM functie gebruiken?
Om een eenvoudige SOM formule te kunnen gebruiken, moet ik eerst uitrekenen wat de omzet en marge per medewerker per maand is. Ik voeg daartoe de rode kolommen I tot en met N toe en maak in de cellen I2:N12 de benodigde berekeningen:
Omzet = Uurtarief * Uren
Bijvoorbeeld: Cel I2 = C2*E2
Marge = Uurtarief * Marge * Uren
Bijvoorbeeld: Cel L2 = C2*D2*E2
In de cellen I14:K14 en L15:N15 reken ik met de SOM functie vervolgens de totalen per maand uit.
Conclusie 1: Het gebruiken van een SOM functie leidt hier tot extra werk en een groter Excel bestand.
SOMPRODUCT en 1 voorwaarde
Ik breid het eerste voorbeeld uit met één voorwaarde: In regel 14 en 15 wil ik de omzet en marge zien van de programmeurs.
Voorbeeld 2 – Tab ‘SOMPRODUCT 1 voorwaarde’
Ik wil in de gele cellen E14:G14 de totale omzet per maand van de programmeurs tonen. In de gele cellen E15:G15 wil ik de totale marge per maand van de programmeurs tonen.
Formule E14
=SOMPRODUCT(($A$2:$A$12=$A$13)*$C$2:$C$12*E2:E12)
Formule E15
=SOMPRODUCT(($A$2:$A$12=$A$13)*$C$2:$C$12*E2:E12*$D$2:$D$12)
De formule in cel E14 kun je als volgt ontleden:
- In celreeks A2:A12 krijgen de cellen waarvoor geldt dat de inhoud gelijk is aan A13, namelijk ‘Programmeur’, de waarde 1. Andere cellen die niet voldoen aan deze voorwaarde krijgen de waarde 0.
- Excel selecteert celreeks C2:C12 met uurtarieven.
- Excel selecteert celreeks E2:E12 met gewerkte uren.
- De 3 geselecteerde celreeksen van gelijke grootte worden met elkaar vermenigvuldigd (PRODUCT) en opgeteld (SOM).
Onthoud: Zodra je één (of meer) voorwaarde(n) in een SOMPRODUCT formule gebruikt, moet(en) deze voorwaarde(n) tussen haakjes ( ) staan en moet je alle puntkomma’s vervangen door * tekens.
De * tekens werken overigens altijd en kan je ook in de standaardfunctie zonder voorwaarde(n) gebruiken. Wellicht handig om jezelf dit aan te leren.
De omzetberekening in cel E14 ziet er in getallen zo uit:
(0*85*120) + (1*100*120) + (0*95*140) + (0*100*120) + (1*100*120) + (1*110*120) + (0*75*160) + (0*95*140) + (0*80*120) + (1*90*140) + (0*80*120) = 49.800
De formule in cel E15 voor de margeberekening is gelijk aan de formule in cel E14, met toevoeging van de celreeks ‘Marge’: =SOMPRODUCT(($A$2:$A$12=$A$13)*$C$2:$C$12*E2:E12*$D$2:$D$12)
Kan ik de SOM.ALS functie gebruiken?
Als je de SOM.ALS formule voor bovenstaande berekeningen wilt gebruiken, heb je nog steeds de rode kolommen I tot en met N en cellen I2:N12 nodig met daarin de tussenberekeningen. In de cellen I14:K14 en L15:N15 reken je met SOM.ALS vervolgens de totalen per maand uit (zie voor meer uitleg Excel tip 4).
De formule in cel I14 luidt: =SOM.ALS($A$2:$A$12;$A$13;I2:I12).
De formule in cel L15 luidt: =SOM.ALS($A$2:$A$12;$A$13;L2:L12)
Conclusie 2: Deze methode is enigszins verwarrend omdat er onder de kolommen I tot en met N optellingen worden getoond van maar een paar kolomwaarden (in rood en vet gedrukt) in plaats van alle kolomwaarden.
SOMPRODUCT en 2 voorwaarden (of meer)
Tenslotte geef ik een voorbeeld met twee voorwaarden: In regel 14 en 15 wil ik de omzet en marge zien van de projectleiders (voorwaarde 1) die een marge van 90% of meer maken (voorwaarde 2).
Voorbeeld 3 – Tab ‘SOMPRODUCT 2 voorwaarden’
Ik wil in de gele cellen E14:G14 de totale omzet per maand van de projectleiders tonen, wiens marge >= 90%. In de gele cellen E15:G15 wil ik de totale marge per maand van de programmeurs tonen, wiens marge >= 90%.
Formule E14
=SOMPRODUCT(($A$2:$A$12=$A$13)*($D$2:$D$12>=$D$13)*$C$2:$C$12*E2:E12)
Formule E15
=SOMPRODUCT(($A$2:$A$12=$A$13)*($D$2:$D$12>=$D$13)*$C$2:$C$12*E2:E12*$D$2:$D$12)
De formule in cel E14 kun je als volgt ontleden:
- In celreeks A2:A12 krijgen de cellen waarvoor geldt dat de inhoud gelijk is aan A13, namelijk ‘Programmeur’, de waarde 1. Andere cellen die niet voldoen aan deze voorwaarde krijgen de waarde 0.
- In celreeks D2:D12 krijgen de cellen waarvoor geldt dat de inhoud groter of gelijk is aan D13, namelijk ‘90%’, de waarde 1. Andere cellen die niet voldoen aan deze voorwaarde krijgen de waarde 0.
- Excel selecteert celreeks C2:C12 met uurtarieven.
- Excel selecteert celreeks E2:E12 met gewerkte uren.
- De 4 geselecteerde celreeksen van gelijke grootte worden met elkaar vermenigvuldigd (PRODUCT) en opgeteld (SOM).
In de formule staan de twee voorwaarden tussen haakjes ( ) en zijn alle puntkomma’s vervangen door * tekens. De omzetberekening in cel E14 ziet er in getallen zo uit:
(1*0*85*120) + (0*0*100*120) + (1*1*95*140) + (1*1*100*120) + (0*0*100*120) + (0*0*110*120) + (0*0*75*160) + (1*0*95*140) + (0*0*80*120) + (0*0*90*140) + (0*0*80*120) = 25.300
De formule in cel E15 is gelijk aan de formule in cel E14, met toevoeging van de celreeks ‘Marge’.
Kan ik de SOMMEN.ALS functie gebruiken?
Ook in dit geval heb je de rode kolommen I tot en met N en cellen I2:N12 nodig met daarin de tussenberekeningen. In de cellen I14:K14 en L15:N15 reken je met SOMMEN.ALS vervolgens de totalen per maand uit (zie Excel tip 4 voor meer uitleg).
De formule in cel I14 luidt: =SOMMEN.ALS(I2:I12;$A$2:$A$12;$A$13;$D$2:$D$12;”>=”&$D$13)
De formule in cel L15 luidt: =SOMMEN.ALS(L2:L12;$A$2:$A$12;$A$13;$D$2:$D$12;”>=”&$D$13)
Conclusie 3: De nadelen van deze methode zijn reeds behandeld. Tevens moet je er goed op letten dat je de >=D13 voorwaarde op de juiste manier verwerkt, namelijk “>=”&D13
Tot slot een aantal tips
- Oefen eerst met SOMPRODUCT en getallen, zonder toevoeging van voorwaarden.
- Oefen daarna met het toevoegen van één of meer tekstuele voorwaarden. Vergeet niet * tekens in plaats van ; tekens te gebruiken.
- Oefen vervolgens met het toevoegen van één of meer getalsvoorwaarden. Let op de speciale syntax (zoals “>=”&D13).
- Combineer tenslotte zowel tekstuele als getalsvoorwaarden en de SOMPRODUCT functie in één formule.