Standaard gebruik van SUMPRODUCT
In zijn klassieke vorm vermenigvuldigt SUMPRODUCT elke waarde in een array met de overeenkomstige waarde in een andere array en retourneert het gesommeerde resultaat. Als de cellen A1: A3 de waarden 1, 2, 3 bevatten en B1: B3 de waarden 10, 20, 30 bevatten
| A | B | |
| 1 | 1 | 10 |
| 2 | 2 | 20 |
| 3 | 3 | 30 |
Dan geeft:
= SUMPRODUCT (A1: A3;B1: B3)
140 oftewel (1 * 10) + (2 * 20) + (3 * 30) = 10 + 40 + 90 = 140.
SUMPRODUCT is een handige functie, maar niet meer dan dat. Gelukkig zijn er een boel nerds die heel creatief zijn en het gebruik van SUMPRODUCT hebben ontwikkeld en nog steeds ontwikkelen en telkens met nieuwe mogelijkheden komen. Dit soort slimme lieden vind je vooral in Microsoft Excel-nieuwsgroepen en fora. Zij hebben de bruikbaarheid van SUMPRODUCT aanzienlijk verbeterd.
Enkele voorbeelden van SUMPRODUCT
CSE betekent formule invoeren met Ctrl+Shift+Enter
E2 =COUNTIF(A2:A10;"Audi")
E3 =SUMIF(A2:A10;"Audi";C2:C10)
E4 =SUM(IF(A2:A10="Audi";IF(B2:B10="Mei";1;0);0)) Voer in met Ctrl+Shift+Enter
E5 =SUM(IF(A2:A10="Audi";IF(B2:B10="Mei";C2:C10;0);0)) Voer in met Ctrl+Shift+Enter
E6 =SUMPRODUCT((A2:A10="Audi")*(B2:B10="Mei"))
E7 =SUMPRODUCT((A2:A10="Audi")*(B2:B10="Mei")*(C2:C10))
E8 =SUMPRODUCT(--(A2:A10={"Audi"\"Porsche"})) is hetzelfde . . .
E9 =SUMPRODUCT((A1:A10="Audi")+(A1:A10="Porsche")) . . . als deze formule
De Asterisk * betekent EN
De Plus + betekent OF
Voordelen van SUMPRODUCT
– Werkt ook in combinatie met gesloten excel bestanden.
– Kan overweg met numerieke- en tekstwaarden.
Formule in A2 een willekeurig Excel bestand:
A2 =SUMPRODUCT(--([Vaxhaul.xlsx]Sheet1!$A$2:$A$4>=1);--([Vaxhaul.xlsx]Sheet1!$B$2:$B$4))
Waarbij de formule verwijst naar [Vaxhaul.xlsx]Sheet1!. Dat is het bestand Vaxhaul.xlsx én Sheet1, hieronder weergegeven.
De formule resulteert in:
=SUMPRODUCT({TRUE;TRUE;TRUE};{10;20;30})
en uiteindelijk in
=SUMPRODUCT({1;1;1};{10;20;30})
Resultaat: 60 oftewel (1 * 10) + (1 * 20) + (1 * 30) = 10 + 20 + 30 = 60.
Het tweede voordeel is dat we tekst in een kolom met louter numerieke waarden op een andere manier kunnen interpreteren en behandelen.
Bijvoorbeeld in B1 staat tekst. Door het gebruik van SUMPRODUCT kunnen we de (tekst)waarde negeren en toch een uitkomst krijgen namelijk 4.
=SUMPRODUCT(--(A1:A4="x");(B1:B4))
Deze formule resulteert in:
=SUMPRODUCT(--{FALSE;TRUE;FALSE;TRUE};{"nummer";1;2;3})
Resulteert in:
=SUMPRODUCT(--{0;1;0;1};{"nummer";1;2;3})
Resulteert in:
(0 * nummer) + (1 * 1) + (0 * 2) + (1 * 3) = 4
Onderstaande formule zou een fout geven: #VALUE!
=SUMPRODUCT((A1:A4="x")*(B1:B4))
Nog wat uitleg over SUMPRODUCT
In het volgende voorbeeld willen we het aantal verkochte VW vinden van het type A. A2:A13 staat het merk, B2:B13 het type en C2:C13 het aantal verkochte auto’s. De formule om dat te berekenen is:
=SUMPRODUCT((A2:A13="VW")*(B2:B13="A")*(C2:C13))
Het eerste gedeelte (A2:A13=”VW”) controleert of de waarden in de array van het merk VW zijn. Dit resulteert in:
{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE}
Hetzelfde geldt voor het type. In het bereik (B2:B13=”A”) controleren we of het type A betreft. Ook dit resulteert in een aantal TRUE en FALSE:
{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
Tenslotte, in het bereik C2:C13 wordt niks gecontroleerd maar we nemen de waarden over zoals ze er staan. Dus:
{3;4;2;1;4;3;2;8;6;8;7;6}
We hebben dus 3 arrays, twee met TRUE;FALSE waarden en één array met nummers. Dit wordt hier onder getoond.
Afbeelding beneden toont een complete weergave van de TRUE/FALSE waarden en hun numerieke equivalent van 1/0 en de individuele vermenigvuldiging met als uiteindelijk opgeteld resultaat van 35.
Wil je alleen het aantal berekenen dat aan de voorwaarden voldoet?
Voorwaarde: (A2:A13=”VW”) én (B2:B13=”A”) dan resulteert dat in:
Nog een paar voorbeelden.
Hoe vaak komt een bepaalde maand in kolom C voor en tel vervolgens de corresponderende bedragen in kolom D van die maand op. Met andere woorden, tel en sommeer de bedragen van een specifieke maand.
We tellen eerst hoe vaak de maand Mei voorkomt in kolom C. Mei is de vijfde maand vandaar: MONTH($C$2:$C$18)=5
Formule in E2:
=SUMPRODUCT(--(MONTH($C$2:$C$18)=5))
En vervolgens willen we de corresponderende waarden van de maand mei in kolom D optellen. Volledige Formule in E2:
=SUMPRODUCT(--(MONTH($C$2:$C$18)=5);$D$2:$D$18)
Volgende voorbeeld:
Afkortingen van de maanden in kolom B en in de kolommen D OF E staat “Yes” of “No”. De opgave is, zoek in kolom B de maand “apr” (april) en in de kolommen D OF E staat “Yes”. Onthoud wat we eerder stelden:
De Asterisk * betekent EN
De Plus + betekent OF
De formule, in G5, wordt:
=SUMPRODUCT((B$2:B$18="apr")*((D$2:D$18="Yes")+(E$2:E$18="Yes")>0))
Uiteindelijk resulteert dat in:
=SUMPRODUCT({0;1;0;0;0;1;0;0;0;1;0;0;0;0;0;0;1})
Resultaat = 4.
Volgende voorbeeld:
Je kunt SUMPRODUCT ook gebruiken voor een zogenaamde 2 dimensionale opzoek-methode. Je zoekt een waarde op een horizontale as en een waarde op een verticale as. Op het kruispunt van die waarden vind je het resultaat. De horizontale as wordt gevormd door Rij 1 en de verticale as is Kolom A. We zoeken in Kolom A de waarde “CAC40” en in Rij 1 de waarde “Last“. Het resultaat op het kruispunt is 5,354
Zie afbeelding voor de opzet.
Formule gaat in H7:
=SUMPRODUCT((A2:A17=H2)*(A1:F1=H4);A2:F17)