Generieke formule
=SUMPRODUCT(costs,--(range="x"))
Samenvatting
Om de prijs van een productbundel te berekenen met een simpele "x" om een product op te nemen of uit te sluiten, kunt u een formule gebruiken op basis van de SOMPRODUCT-functie. In het getoonde voorbeeld is de formule in D11:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Uitleg
De functie SUMPRODUCT vermenigvuldigt bereiken of matrices met elkaar en retourneert de som van producten. Dit klinkt saai, maar SOMPRODUCT is een elegante en veelzijdige functie, wat dit voorbeeld mooi illustreert.
In dit voorbeeld is SUMPRODUCT geconfigureerd met twee arrays. De eerste array is het bereik met productprijzen:
$C$5:$C$9
Merk op dat de verwijzing absoluut is om wijzigingen te voorkomen, aangezien de formule naar rechts wordt gekopieerd. Dit bereik resulteert in de volgende array:
(99;69;129;119;49)
De tweede array wordt gegenereerd met deze uitdrukking:
--(D5:D9="x")
Het resultaat van D5: D9 = "x" is een reeks TRUE FALSE-waarden zoals deze:
(TRUE;TRUE;FALSE;FALSE;FALSE)
Het dubbele negatieve (-) converteert deze TRUE FALSE-waarden naar 1s en 0s:
(1;1;0;0;0)
Dus binnen SUMPRODUCT hebben we:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
De SUMPRODUCT-functie vermenigvuldigt vervolgens de overeenkomstige items in elke array met elkaar:
=SUMPRODUCT((99;69;0;0;0))
en retourneert de som van producten, in dit geval 168.
In feite fungeert de tweede array als een filter voor de waarden in de eerste array. Nullen in array2 annuleren items in array1, en 1s in array2 laten waarden van array1 doorgaan in het uiteindelijke resultaat.
Met een enkele array
SUMPRODUCT is ingesteld om meerdere arrays te accepteren, maar u kunt deze formule een beetje vereenvoudigen door aan het begin een enkele array op te geven:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
De wiskundige bewerking (vermenigvuldiging) dwingt automatisch de WAAR-ONWAAR-waarden in de tweede uitdrukking naar enen en nullen, zonder dat een dubbel negatief nodig is.
