Generieke formule
=SUMPRODUCT(expression,range)
Samenvatting
Om resultaten van SOMPRODUCT met specifieke criteria te filteren, kunt u eenvoudige logische expressies rechtstreeks toepassen op arrays in de functie, in plaats van de ALS-functie te gebruiken. In het getoonde voorbeeld zijn de formules in H5: H7:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
waarbij de volgende benoemde bereiken zijn gedefinieerd:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Als u benoemde bereiken liever vermijdt, gebruikt u de hierboven ingevoerde bereiken als absolute verwijzingen. De logische uitdrukkingen in H6 en H7 kunnen worden gecombineerd, zoals hieronder wordt uitgelegd.
Uitleg
Dit voorbeeld illustreert een van de belangrijkste sterke punten van de SUMPRODUCT-functie: de mogelijkheid om gegevens te filteren met logische basisuitdrukkingen in plaats van de ALS-functie. Binnen SUMPRODUCT is de eerste array een logische uitdrukking om te filteren op de kleur "rood":
--(color="red")
Dit resulteert in een array of TRUE FALSE-waarden, die worden omgezet in enen en nullen met de dubbele negatieve (-) bewerking. Het resultaat is deze array:
(1;0;1;0;0;0;1;0;0;0)
Merk op dat de array 10 waarden bevat, één voor elke rij. Een één geeft een rij aan waar de kleur "rood" is en een nul geeft een rij aan met een andere kleur.
Vervolgens hebben we nog twee arrays: één voor hoeveelheid en één voor prijs. Samen met deze resultaten van de eerste array hebben we:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Door de arrays uit te breiden, hebben we:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),(10;6;14;9;11;10;8;9;11;10),(15;18;15;16;18;18;15;16;18;16))
Het kerngedrag van SUMPRODUCT is vermenigvuldigen en vervolgens optellen. Aangezien we werken met drie reeksen, kunnen we de werking visualiseren zoals in de onderstaande tabel, waarbij de resultaatkolom het resultaat van vermenigvuldiging matrix1 * matrix2 * array3 :
| matrix1 | matrix2 | matrix3 | resultaat |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 16 | 0 |
Merk op dat array1 werkt als een filter - nul waarden hier "nul uit" waarden in rijen waar de kleur niet "rood" is. Door de resultaten weer in SUMPRODUCT te plaatsen, hebben we:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Dat levert een eindresultaat van 480 op.
Aanvullende criteria toevoegen
U kunt criteria uitbreiden door nog een logische uitdrukking toe te voegen. Als u bijvoorbeeld de totale verkoop wilt vinden waarbij de kleur 'Rood' is en de staat 'TX', bevat H6:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Opmerking: SOMPRODUCT is niet hoofdlettergevoelig.
Vereenvoudigen met een enkele array
Excel-professionals zullen de syntaxis in SOMPRODUCT vaak een beetje vereenvoudigen door arrays rechtstreeks in array1 als volgt te vermenigvuldigen :
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Dit werkt omdat de wiskundige bewerking (vermenigvuldiging) automatisch de WAAR en ONWAAR waarden van de eerste twee uitdrukkingen in enen en nullen samenvoegt.
