SUMMENPRODUKT-WENN-Formel – Excel & Google Sheets

In diesem Tutorial zeigen wir Ihnen, wie Sie die Kombination SUMMENPRODUKT-WENN verwenden können, um die Summe der Produkte von Arrays oder Bereichen auf Basis von Kriterien zurückgeben.

Die SUMMENPRODUKT-Funktion

Die SUMMENPRODUKT-Funktion wird verwendet, um Zahlenreihen zu multiplizieren und die resultierende Reihe zu summieren.

Um ein SUMMENPRODUKT-WENN zu erstellen, werden wir die SUMMENPRODUKT-Funktion zusammen mit der WENN-Funktion in einer Array-Formel verwenden.

SUMMENPRODUKT-WENN

Durch die Kombination von SUMMENPRODUKT und WENN in einer Array-Formel können wir im Wesentlichen ein „SUMMENPRODUKT-WENN“ erstellen, das ähnlich wie die integrierte SUMMEWENN-Funktion funktioniert. Lassen Sie uns ein Beispiel durchgehen.

Wir haben eine Liste der Umsätze von Managern in verschiedenen Regionen mit entsprechenden Provisionssätzen:

Angenommen, wir werden gebeten, den Provisionsbetrag für jeden Manager folgendermaßen zu berechnen:

Um dies zu erreichen, können wir eine WENN-Funktion mit Manager als Kriterium in die SUMMENPRODUKT-Funktion folgendermaßen einfügen:

=SUMMENPRODUKT(WENN(<Kriterienbereich>=<Kriterien>;<Wertebereich1>*<Wertebereich2>))

=SUMMENPRODUKT(WENN($C$2:$C$10=$G2;$D$2:$D$10*$E$2:$E$10))

Wenn Sie Excel 2019 oder eine frühere Version verwenden, müssen Sie die Formel durch Drücken von STRG + UMSCHALT + ENTER eingeben, um die geschweiften Klammern um die Formel zu erhalten (siehe Bild oben).

Wie funktioniert die Formel?

Die Formel wertet jede Zelle in unserem Kriterienbereich als WAHR oder FALSCH aus.

Die Gesamtprovision für Olivia berechnen:

=SUMMENPRODUKT(WENN($C$2:$C$10=$G2;$D$2:$D$10*$E$2:$E$10))

=SUMMENPRODUKT(WENN({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}))

Anschließend ersetzt die WENN-Funktion jeden Wert durch FALSCH, wenn die Bedingung nicht erfüllt ist.

= SUMMENPRODUKT({928,62; 668,22; FALSCH; FALSCH; FALSCH; 480,564; FALSCH; FALSCH; FALSCH})

Jetzt überspringt die SUMMENPRODUKT-Funktion die FALSCH-Werte und summiert die verbleibenden (2.077,40).

SUMMENPRODUKT-WENN mit mehreren Kriterien

Um SUMMENPRODUKT-WENN mit mehreren Kriterien zu verwenden (ähnlich wie die eingebaute SUMMEWENNS-Funktion), verschachteln Sie einfach mehrere WENN-Funktionen in der SUMMENPRODUKT-Funktion, und zwar auf die folgende Weise:

=SUMMENPRODUKT(WENN(<Kriterium1 Bereich>=<Kriterium1>; WENN(<Kriterium2 Bereich>=<Kriterium2>; <Werte Bereich1>*<Werte Bereich2>))

=SUMMENPRODUKT(WENN($B$2:$B$10=$G2;WENN($C$2:$C$10=$H2;$D$2:$D$10*$E$2:$E$10)))

(STRG + UMSCHALT + ENTER)

SUMMENPRODUKT-WENN – Ein anderer Ansatz

In Excel gibt es oft mehrere Möglichkeiten, um zu den gewünschten Ergebnissen zu gelangen. Eine andere Möglichkeit, das „SUMMENPRODUKT-WENN“ zu berechnen, besteht darin, die Kriterien innerhalb der SUMMENPRODUKT-Funktion als Array mit „Double Unary“ (–) folgendermaßen einzuschließen:

=SUMMENPRODUKT(--($B$2:$B$10=$G2);--($C$2:$C$10=$H2);$D$2:$D$10*$E$2:$E$10)

Diese Methode verwendet den „Double Unary“ (–), um ein WAHR-FALSCH-Array in Nullen und Einsen umzuwandeln. SUMMENPRODUKT 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, sollten Sie Ihre Bereiche und Formeleingaben immer mit einem Sperrverweis (F4) versehen, damit sie automatisch ausgefüllt werden können.
• Wenn Sie Excel 2019 oder eine neuere Version verwenden, können Sie die Formel auch ohne STRG + UUMSCHALT + ENTER eingeben.

SUMMENPRODUKT-WENN in Google Sheets

Die Funktion SUMMENPRODUKT-WENN funktioniert in Google Sheets genauso wie in Excel, außer der Tatsache, dass Sie die Funktion ARRAYFORMULA anstelle von STRG+SHIFT+ENTER verwenden müssen, um die Array-Formel zu erstellen.