Laden Sie die Beispielarbeitsmappe herunter
Dieses Tutorial zeigt, wie man „sumproduct if“ berechnet und die Summe der Produkte von Arrays oder Ranges basierend auf Kriterien zurückgibt.
SUMPRODUCT-Funktion
Die SUMPRODUCT-Funktion wird verwendet, um Arrays von Zahlen zu multiplizieren und das resultierende Array zu summieren.
Um ein „Sumproduct If“ zu erstellen, verwenden wir die SUMPRODUCT-Funktion zusammen mit der IF-Funktion in einer Array-Formel.
SUMMENPRODUKT WENN
Durch die Kombination von SUMPRODUCT und IF in einer Array-Formel können wir im Wesentlichen eine „SUMPRODUCT IF“-Funktion erstellen, die ähnlich wie die integrierte SUMIF-Funktion funktioniert. Gehen wir ein Beispiel durch.
Wir haben eine Liste der von Managern in verschiedenen Regionen erzielten Verkäufe mit entsprechenden Provisionssätzen:
Angenommen, wir werden gebeten, den Provisionsbetrag für jeden Manager wie folgt zu berechnen:
Um dies zu erreichen, können wir eine IF-Funktion mit dem Manager als unsere Kriterien innerhalb der SUMPRODUCT-Funktion wie folgt:
=SUMMENPRODUKT(WENN(=,*))
=SUMMENPRODUKT(WENN($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))
Wenn Sie Excel 2022 und früher verwenden, müssen Sie die Formel eingeben, indem Sie drücken STRG + UMSCHALT + EINGABETASTE um die geschweiften Klammern um die Formel zu erhalten (siehe oberes Bild).
Wie funktioniert die Formel?
Die Formel funktioniert, indem jede Zelle in unserem Kriterienbereich als WAHR oder FALSCH bewertet wird.
Berechnung der Gesamtprovision für Olivia:
=SUMMENPRODUKT(WENN($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))
= SUMMENPRODUKT (IF({WAHR; WAHR;FALSCH; FALSCH; FALSCH; WAHR; FALSCH; FALSCH; FALSCH}, {928.62; 668.22;919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61})))
Als nächstes ersetzt die IF-Funktion jeden Wert durch FALSE, wenn seine Bedingung nicht erfüllt ist.
= SUMMENPRODUKT({928.62; 668.22; FALSCH; FALSCH; FALSCH; 480.564; FALSCH; FALSCH; FALSCH})
Jetzt überspringt die SUMPRODUCT-Funktion die FALSE-Werte und summiert die restlichen Werte (2.077,40).
SUMMENPRODUKT WENN mit mehreren Kriterien
Um SUMPRODUCT IF mit mehreren Kriterien zu verwenden (ähnlich wie die integrierte SUMIFS-Funktion), verschachteln Sie einfach weitere IF-Funktionen wie folgt in die SUMPRODUCT-Funktion:
=SUMMENPRODUKT(WENN(=, WENN(=, *))
(STRG + UMSCHALT + EINGABETASTE)
=SUMMENPRODUKT(IF($B$2:$B$10=$G2,IF($C$2:$C$10=$H2,$D$2:$D$10*$E$2:$E$10)))
(STRG + UMSCHALT + EINGABETASTE)
Ein anderer Ansatz für SUMPRODUCT IF
In Excel gibt es oft mehrere Möglichkeiten, um zu den gewünschten Ergebnissen zu gelangen. Eine andere Methode zur Berechnung des „Summenprodukts wenn“ besteht darin, die Kriterien einzubeziehen innerhalb die SUMPRODUCT-Funktion als Array mit Double Unary wie folgt:
=SUMMENPRODUKT(--($B$2:$B$10=$G2),--($C$2:$C$10=$H2),$D$2:$D$10*$E$2:$E$10)
Diese Methode verwendet das doppelte unäre (-), um ein TRUE FALSE-Array in Nullen und Einsen zu konvertieren. SUMPRODUCT multipliziert dann die konvertierten Kriterien-Arrays miteinander:
=SUMMENPRODUKT({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})
Tipps und Tricks:
- Wenn möglich, sperren Sie Ihre Bereiche und Formeleingaben immer mit einer Sperrreferenz (F4), um das automatische Ausfüllen zu ermöglichen.
- Wenn Sie Excel 2022 oder neuer verwenden, können Sie die Formel ohne Strg + Umschalt + Eingabe eingeben.
SUMMENPRODUKT WENN in Google Tabellen
Die SUMPRODUCT IF-Funktion funktioniert in Google Sheets genauso wie in Excel:
