Samenvatting
De SUMPRODUCT-functie vermenigvuldigt bereiken of arrays samen en geeft de som van de producten terug. De SUMPRODUCT-functie is een ongelooflijk veelzijdige functie die kan worden gebruikt om te tellen en op te tellen zoals COUNTIFS of SOM.ALS-functie, maar met meer flexibiliteit.
Syntaxis
=SUMPRODUCT (array1,[array2], ….)
Argumenten
array1 – De eerste array of het eerste bereik om te vermenigvuldigen, dan add.array2 – [optioneel] De tweede array of het tweede bereik om te vermenigvuldigen en vervolgens toe te voegen.
array1 – De eerste array of het eerste bereik om te vermenigvuldigen, dan add.array2 – [optioneel] De tweede array of het tweede bereik om te vermenigvuldigen en vervolgens toe te voegen.
Gebruiksaanwijzing
De SUMPRODUCT functie werkt met arrays, maar het vereist niet de normale array syntax (Ctrl + Shift + Enter) om in te voeren. Het doel van de SUMPRODUCT functie is het vermenigvuldigen en vervolgens optellen van arrays. Als er slechts één array wordt geleverd, zal SUMPRODUCT eenvoudigweg de items in de array optellen. Er kunnen maximaal 30 arrays worden geleverd.
Wanneer u SUMPRODUCT voor het eerst tegenkomt, kan het saai, complex en zelfs zinloos lijken. Maar SUMPRODUCT is een verbazingwekkend veelzijdige functie met vele toepassingen. Omdat het sierlijk en zonder klachten met arrays omgaat, kunt u het gebruiken om reeksen cellen op een slimme, elegante manier te verwerken (zie de links naar formulevoorbeelden op deze pagina).
Om te illustreren hoe SUMPRODUCT werkt, zijn hier een paar veelvoorkomende voorbeelden.
SUMPRODUCT voor voorwaardelijke bedragen en tellingen
Stel dat u enkele bestelgegevens heeft in A2:B6, met Staat in kolom A, Verkoop in kolom B:
A
B
1
Staat
Verkoop
2
UT
75
3
CO
100
4
TX
125
5
CO
125
6
TX
150
Met SUMPRODUCT kunt u de totale omzet voor Texas (“TX”) met deze formule tellen:
=SUMPRODUCT(-(A2:A2:A6=”TX”))
En met deze formule kunt u de totale omzet voor Texas (“TX”) optellen:
=SUMPRODUCT(-(A2:A6=”TX”),B2:B6)
Opmerking: Laat u niet verwarren door het dubbel-negatief. Dit is een veel gebruikte truc in meer geavanceerde Excel formules om TRUE en FALSE waarden te dwingen tot 1’s en 0’s. Zie meer hieronder…..
Voor het bovenstaande voorbeeld van de som is hier een virtuele weergave van de twee arrays zoals deze voor het eerst door SUMPRODUCT worden verwerkt:
reeks 1
reeks2
FALSE
75
FALSE
100
TRUE
125
FALSE
125
TRUE
150
Elke array heeft 5 items. De eerste matrix bevat de TRUE / FALSE waarden die voortvloeien uit de uitdrukking A2:A6=”TX”, en de tweede matrix is de inhoud van B2:B6. Elk item in de eerste matrix wordt vermenigvuldigd met het overeenkomstige item in de tweede matrix. In de huidige toestand zal het resultaat van SUMPRODUCT echter nul zijn omdat de TRUE en FALSE waarden als nul worden behandeld. We hebben de items in array1 nodig om numeriek te zijn – ze moeten worden “geforceerd” in enen en nullen. Dit is waar de dubbel-negatieve komt in.
Door gebruik te maken van het dubbele negatief — (dubbele unary, voor u technische types) zijn we in staat om de TRUE/FALSE te dwingen in de numerieke waarden één en nul, zoals weergegeven in de virtuele weergave hieronder. De laatste kolom “Product” geeft het resultaat van de vermenigvuldiging van de twee arrays samen. Het samengetelde resultaat, 275, is de waarde die SUMPRODUCT oplevert.
reeks 1
reeks2
Product
0
*
75
=
0
0
*
100
=
0
1
*
125
=
125
0
*
125
=
0
1
*
150
=
150
Som
275
Met behulp van de accoladesyntax voor arrays ziet het voorbeeld er na dwang zo uit:
=SUMPRODUCT({0,0,1,0,1,1},{75,100,125,125,125,150})
en zo na vermenigvuldiging:
=SUMPRODUCT({0,0,0,125,0,150})
Dit voorbeeld gaat dieper in op bovenstaande ideeën.
SUMPRODUCT met andere functies
SUMPRODUCT kan andere functies direct gebruiken. Mogelijk ziet u SUMPRODUCT gebruikt met de LEN-functie om het totaal aantal tekens in een bereik te tellen, of met functies zoals ISBLANK, ISTEXT, enz. Dit zijn normaal gesproken geen array-functies, maar wanneer ze een bereik krijgen, creëren ze een “resultatenserie”. Omdat SUMPRODUCT is gebouwd om met arrays te werken, is het in staat om direct berekeningen op de arrays uit te voeren. Dit kan een goede manier zijn om ruimte te besparen in een werkblad, door de noodzaak van een “helper”-kolom te elimineren.
Stel bijvoorbeeld dat u 10 verschillende tekstwaarden in A1:A10 hebt en u wilt het totaal aantal tekens tellen voor alle 10 waarden. U kunt in kolom B een helperkolom toevoegen die deze formule gebruikt: LEN(A1) om de tekens in elke cel te berekenen. Dan kunt u SUM gebruiken om alle 10 nummers bij elkaar op te tellen. Echter, met behulp van SUMPRODUCT kunt u een formule als deze schrijven:
=SUMPRODUCT (LEN(A1:A10)))
Bij gebruik met een bereik zoals A1:A10 zal LEN een reeks van 10 waarden teruggeven. Dan zal SUMPRODUCT eenvoudigweg alle waarden optellen en het resultaat teruggeven, zonder dat er een helperkolom nodig is.
Zie onderstaande voorbeelden van vele andere manieren om SUMPRODUCT te gebruiken.
Opmerkingen:
SUMPRODUCT behandelt niet-numerieke items in arrays als nullen.
Array argumenten moeten even groot zijn. Anders genereert SUMPRODUCT een #VALUE! foutwaarde.
Logische tests in arrays zullen TRUE en FALSE waarden creëren. In de meeste gevallen zul je deze tot 1 en 0’s willen dwingen.
SUMPRODUCT kan vaak het resultaat van andere functies direct gebruiken (zie onderstaande formulevoorbeelden)
COUNTIF is een functie om cellen te tellen die aan één criterium voldoen. COUNTIF kan worden gebruikt om cellen te tellen met data, getallen en tekst die overeenkomen met specifieke criteria. De COUNTIF functie ondersteunt logische operatoren (>,…..
De Excel COUNTIFS functie geeft het aantal cellen dat aan één of meer criteria voldoet. COUNTIFS kan worden gebruikt met criteria op basis van data, nummers, tekst en andere voorwaarden. COUNTIFS ondersteunt logische operatoren (>,…..
SUMIF is een functie om cellen op te tellen die aan één criterium voldoen. SUMIF kan worden gebruikt om cellen op te tellen op basis van data, getallen en tekst die aan specifieke criteria voldoen. SUMIF ondersteunt logische operatoren (>,,=) en wildcards (*,?) voor gedeeltelijke matching…….
SUMIFS is een functie om cellen op te tellen die aan meerdere criteria voldoen. SUMIFS kan worden gebruikt om waarden op te tellen wanneer aangrenzende cellen voldoen aan criteria gebaseerd op data, getallen en tekst. SUMIFS ondersteunt logische operatoren (>,…..
