Download het voorbeeldwerkboek
Deze tutorial laat zien hoe je "sumproduct if" kunt berekenen, waarbij de som van de producten van arrays of bereiken wordt geretourneerd op basis van criteria.
SOMPRODUCT Functie
De functie SOMPRODUCT wordt gebruikt om reeksen getallen te vermenigvuldigen, waarbij de resulterende reeks wordt opgeteld.
Om een "Somproduct Als" te maken, gebruiken we de SOMPRODUCT-functie samen met de ALS-functie in een matrixformule.
SOMPRODUCT ALS
Door SOMPRODUCT en ALS te combineren in een matrixformule, kunnen we in wezen een "SOMPRODUCT ALS" -functie maken die vergelijkbaar is met hoe de ingebouwde SUMIF-functie werkt. Laten we een voorbeeld doornemen.
We hebben een lijst met verkopen die zijn behaald door managers in verschillende regio's met bijbehorende commissietarieven:
Stel dat we worden gevraagd om het commissiebedrag voor elke manager als volgt te berekenen:
Om dit te bereiken, kunnen we een ALS-functie nesten met de manager als onze criteria in de SOMPRODUCT-functie als volgt:
=SOMPRODUCT(ALS(=,*))
=SOMPRODUCT(IF($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))
Wanneer u Excel 2022 en eerder gebruikt, moet u de formule invoeren door op . te drukken CTRL + SHIFT + ENTER om de accolades rond de formule te krijgen (zie bovenste afbeelding).
Hoe werkt de formule?
De formule werkt door elke cel in ons criteriabereik te evalueren als WAAR of ONWAAR.
De totale commissie voor Olivia berekenen:
=SOMPRODUCT(IF($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))
= SOMPRODUCT (ALS({TRUE; TRUE;FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, {928.62; 668.22;919.695; 447,384; 697.620; 480.564; 689.325; 752.366; 869.61}))
Vervolgens vervangt de IF-functie elke waarde door FALSE als niet aan de voorwaarde wordt voldaan.
= SOMPRODUCT({928.62; 668.22; ONWAAR; ONWAAR; ONWAAR; 480.564; ONWAAR; ONWAAR; ONWAAR})
Nu slaat de SOMPRODUCT-functie de FALSE-waarden over en telt de resterende waarden op (2.077,40).
SOMPRODUCT ALS met meerdere criteria
Om SOMPRODUCT ALS met meerdere criteria te gebruiken (vergelijkbaar met hoe de ingebouwde SUMIFS-functie werkt), nest je gewoon meer ALS-functies in de SOMPRODUCT-functie als volgt:
=SOMPRODUCT(ALS(=, ALS(=, *))
(CTRL + SHIFT + ENTER)
=SOMPRODUCT(IF($B$2:$B$10=$G2,IF($C$2:$C$10=$H2,$D$2:$D$10*$E$2:$E$10)))
(CTRL + SHIFT + ENTER)
Een andere benadering van SUMPRODUCT IF
Vaak zijn er in Excel meerdere manieren om tot de gewenste resultaten te komen. Een andere manier om "somproduct als" te berekenen, is door de criteria op te nemen binnenin de SOMPRODUCT-functie als een array met behulp van dubbele unaire zoals:
=SOMPRODUCT(--($B$2:$B$10=$G2),--($C$2:$C$10=$H2),$D$2:$D$10*$E$2:$E$10)
Deze methode gebruikt de dubbele unaire (-) om een TRUE FALSE-array om te zetten in nullen en enen. SOMPRODUCT vermenigvuldigt vervolgens de geconverteerde criteriaarrays met elkaar:
=SOMPRODUCT({1;1;0;0;0;1;0;0;0},{1;0;1;0;1;0;0;0;0},{928.62; 668,22;919.695; 447.384; 697.620; 480.564; 689,325; 752.366; 869.61})
Tips en trucs:
- Vergrendel waar mogelijk altijd uw bereiken en formule-invoer met een lock-reference (F4) om automatisch invullen mogelijk te maken.
- Als u Excel 2022 of nieuwer gebruikt, kunt u de formule invoeren zonder Ctrl + Shift + Enter.
SOMPRODUCT ALS in Google Spreadsheets
De SOMPRODUCT IF-functie werkt precies hetzelfde in Google Spreadsheets als in Excel:
