SUMMENPRODUKT – Funktionsweise, Arrays und Kriterien – Excel & GS

Dieses Tutorial zeigt Ihnen, wie Sie die Excel-Funktion SUMMENPRODUKT in Excel und Google Sheets verwenden können.

 

Die SUMMENPRODUKT-Funktion im Überblick

Die SUMMENPRODUKT-Funktion multipliziert Zahlenreihen und summiert die resultierende Reihe.

Sie ist eine der leistungsstärksten Funktionen in Excel. Ihr Name könnte Sie zu der Annahme verleiten, dass sie nur für grundlegende mathematische Berechnungen (gewichteter Mittelwert) gedacht ist, aber sie kann für sehr viel mehr verwendet werden.

Mathematische Grundlagen

Schauen wir uns ein einfaches Beispiel für SUMMENPRODUKT an, womit wir der Gesamtumsatz berechnen.

Wir haben eine Tabelle mit Produkten und wollen den Gesamtumsatz berechnen. Man könnte versucht sein, einfach eine neue Spalte hinzuzufügen, die verkaufte Menge * Preis zu nehmen und dann die Summe der neuen Spalte zu bilden. Stattdessen können Sie aber auch einfach die SUMMENPRODUKT-Funktion verwenden. Lassen Sie uns die Formel durchgehen:

=SUMMENPRODUKT(A2:A4;B2:B4)

Die Funktion lädt die Zahlenbereiche in Arrays, multipliziert sie miteinander und summiert dann die Ergebnisse:

=SUMMENPRODUKT({100; 50; 10}; {6; 7; 5})
=SUMMENPRODUKT({100 * 6; 50 * 7; 10 * 5})
=SUMMENPRODUKT({600; 350; 50}
=1000

Die SUMMENPRODUKT-Funktion konnte alle Zahlen für uns multiplizieren UND die Summierung durchführen.

Gewichteter Mittelwert

Ein weiterer Fall, in dem es hilfreich ist, SUMMENPRODUKT zu verwenden, ist es, wenn Sie einen gewichteten Mittelwert berechnen müssen. Dies kommt am häufigsten bei Schularbeiten vor. Betrachten wir also die folgende Tabelle:

Wir können sehen, wie viel die Tests, Prüfungen und Hausaufgaben bei der Gesamtnote wert sind und wie der aktuelle Mittelwert für jedes einzelne Element lautet. Wir können die Gesamtnote berechnen, indem wir Folgendes schreiben:

=SUMMENPRODUKT(B2:B4; C2:C4)

Unsere Funktion multipliziert wieder alle Elemente in den Arrays, bevor sie die Gesamtsumme bildet. Dies funktioniert folgendermaßen:

=SUMMENPRODUKT({30%; 50%; 20%}; {73%; 90%; 95%})
=SUMMENPRODUKT({22%; 45%; 19%})
=86%

Mehrere Spalten

SUMMENPRODUKT kann auch bei noch mehr Spalten, die alle miteinander multipliziert werden müssen, verwendet werden. Schauen wir uns ein Beispiel an, bei dem wir das Volumen in Holzstücken berechnen müssen.

Anstatt eine Hilfsspalte zu erstellen, um den Gesamtumsatz für jede Zeile zu berechnen, können wir dies mit einer einzigen Formel tun. Unsere Formel lautet:

=SUMMENPRODUKT(B2:B5; C2:C5; D2:D5)

Die ersten Elemente der einzelnen Reihen werden miteinander multipliziert (z. B. 4 * 2 * 1 = 8). Dann die zweiten (4 * 2 * 2 = 16)  und die dritten usw. Insgesamt ergibt sich so eine Reihe von Produkten, die wie {8; 16; 16; 32} aussehen. Das Gesamtvolumen wäre dann die Summe dieser Reihe (72).

Arrays

SUMMENPRODUKT erfordert die Eingabe von Arrays.

Was verstehen wir unter „Array“? Ein Array ist einfach eine Gruppe von Elementen (z. B. Zahlen), die in einer bestimmten Reihenfolge genau wie ein Zellenbereich angeordnet sind. Wenn Sie also die Zahlen 1, 2, 3 in den Zellen A1:A3 haben, würde Excel dies als Array {1;2;3} lesen. Sie können sogar {1;2;3} direkt in Excel-Formeln eingeben, und Excel erkennt die Anordnung.

Wir werden weiter unten mehr über Arrays sprechen.

Ein Kriterium

Okay, fügen wir eine weitere Komplexitätsebene hinzu. Wir haben gesehen, dass SUMMENPRODUKT Arrays von Zahlen verarbeiten kann, aber was ist, wenn wir nach Kriterien suchen wollen? Nun, Sie können auch Arrays für boolesche Werte erstellen (boolesche Werte sind Werte, die WAHR oder FALSCH entsprechen).

Nehmen wir zum Beispiel ein einfaches Array {1; 2; 3}. Erstellen wir ein entsprechendes Array, das angibt, ob jede Zahl größer als 1 ist. Dieses Array würde wie {FALSCH; WAHR; WAHR} aussehen.

Dies ist äußerst hilfreich in Formeln, da wir WAHR/FALSCH leicht in 1/0 umwandeln können. Schauen wir uns ein Beispiel an.

Anhand der folgenden Tabelle wollen wir berechnen, wie viele Einheiten vom Typ Rot verkauft wurden:

Wir können dies mit dieser Formel tun:

=SUMMENPRODUKT(A2:A4; --(B2:B4="Rot"))

Moment mal! Sie fragen sich: Was hat es mit dem doppelten Minuszeichen auf sich? Erinnern Sie sich, wie ich sagte, dass wir WAHR/FALSCH in 1/0 umwandeln können? Wir tun dies, indem wir den Computer zwingen, eine mathematische Operation auszuführen. In diesem Fall sagen wir: Nimm den negativen Wert, und dann nimm den negativen Wert noch einmal. Wenn wir das ausschreiben, wird sich unser Array wie folgt ändern:

{WAHR; WAHR; FALSCH}
{-1; -1; 0}
{1; 1; 0}

Zurück zur vollständigen SUMMENPRODUKT-Formel, die unser Array lädt und dann wie folgt multipliziert:

=SUMMENPRODUKT({100; 50; 10}; {1; 1; 0})
=SUMMENPRODUKT({100; 50; 0})
=150

Beachten Sie, dass das 3. Element zu einer 0 wurde, da alles, was mit 0 multipliziert wird, zu Null wird.

Mehrere Kriterien

Wir können bis zu 255 Arrays in unsere Funktion laden. Daher können wir sicherlich weitere Kriterien einfügen. Schauen wir uns diese größere Tabelle an, in der wir den Verkaufsmonat hinzugefügt haben.

Wenn wir wissen wollen, wie viele verkaufte Artikel rot waren und in den Monat Februar fielen, könnten wir unsere Formel wie folgt schreiben:

=SUMMENPRODUKT(A2:A4; --(B2:B4="Rot"); --(C2:C4="Feb"))

Der Computer würde dann unsere Arrays auswerten und miteinander multiplizieren. Wir haben bereits besprochen, wie WAHR/FALSCH-Arrays in 1/0 umgewandelt werden, also überspringe ich diesen Schritt für jetzt.

=SUMMENPRODUKT({100; 50; 10}; {1; 1; 0}; {0; 1; 1})
=SUMMENPRODUKT({0; 50; 0})
=50

In unserem Beispiel gab es nur eine Zeile, die allen Kriterien entsprach, aber bei echten Daten hätten Sie vielleicht mehrere Zeilen, die Sie summieren müssten.

Komplexe Kriterien

Okay, bis zu diesem Punkt sind Sie vielleicht nicht beeindruckt, weil alle unsere Beispiele mit anderen Funktionen wie SUMMEWENN oder ZÄHLENWENN hätten durchgeführt werden können. Jetzt werden wir etwas tun, was diese anderen Funktionen nicht können. Bisher enthielt unsere Spalte Monat die tatsächlichen Namen der Monate. Was wäre, wenn sie stattdessen Datumsangaben enthielte?

Wir können jetzt keine SUMMEWENN durchführen, da diese die von uns benötigten Kriterien nicht verarbeiten kann. Mit SUMMENPRODUKT können wir jedoch das Array manipulieren und eine tiefere Prüfung durchführen. Wir haben bereits Arrays manipuliert, als wir WAHR/FALSCH in 1/0 umgewandelt haben. Wir werden dieses Array mit der MONAT-Funktion manipulieren. Hier ist die vollständige Formel, die wir verwenden werden:

=SUMMENPRODUKT(A2:A4; --(B2:B4="Rot"); --(MONAT(C2:C4)=2))

Schauen wir uns das 3. Array genauer an. Zunächst wird unsere Formel die Monatsnummer aus jedem Datum in C2:C4 extrahieren. Damit erhalten wir {1; 2; 2}. Als nächstes prüfen wir, ob dieser Wert gleich 2 ist. Jetzt sieht unser Array wie {FALSCH; WAHR; WAHR} aus. Wir verwenden wieder das doppelte Minus und erhalten {0; 1; 1}. Wir befinden uns jetzt wieder an einer ähnlichen Stelle wie in Beispiel 3 und unsere Formel kann uns sagen, dass 50 rote Einheiten im Februar verkauft wurden.

Doppelminus gegen Multiplikation

Wenn Sie die SUMMENPRODUKT-Funktion schon einmal verwendet haben, haben Sie vielleicht eine etwas andere Darstellungsart gesehen. Anstatt ein doppeltes Minus zu verwenden, können Sie Folgendes schreiben:

=SUMMENPRODUKT(A2:A4*(B2:B4="Rot")*(MONAT(C2:C4)=2))

Die Formel funktioniert immer noch auf dieselbe Weise. Wir teilen dem Computer nur manuell mit, dass wir die Arrays multiplizieren wollen. SUMMENPRODUKT würde dies sowieso tun, also gibt es keine Änderung in der Art und Weise, wie die Mathematik funktioniert. Die Durchführung der mathematischen Operation wandelt unser WAHR/FALSCH genauso in 1/0 um. Warum also der Unterschied?

In den meisten Fällen spielt es keine große Rolle und es ist eine Frage der Benutzerpräferenz. Es gibt jedoch mindestens einen Fall, in dem die Multiplikation erforderlich ist.

Wenn Sie SUMMENPRODUKT verwenden, erwartet der Computer, dass alle Argumente (Array1; Array2; etc.) die gleiche Größe haben. Das bedeutet, dass sie die gleiche Anzahl von Zeilen oder Spalten haben. Sie können jedoch mit SUMMENPRODUKT eine so genannte zweidimensionale Array-Berechnung, die wir im nächsten Beispiel sehen werden, durchführen. In diesem Fall sind die Arrays unterschiedlich groß, so dass wir die Prüfung, ob alle gleich groß sind, umgehen müssen.

Zwei Dimensionen

In allen vorherigen Beispielen hatten die Arrays die gleiche Richtung. SUMMENPRODUKT kann Dinge behandeln, die in zwei Richtungen gehen, wie wir in der nächsten Tabelle sehen werden.

Hier ist unsere Tabelle mit den verkauften Einheiten, aber die Daten sind so angeordnet, dass die Kategorien über die Kopfzeile gehen. Wenn wir herausfinden wollen, wie viele Artikel rot und von Kategorie A waren, können wir schreiben:

=SUMMENPRODUCT((A2:A4="Rot")*(B1:C1="A")*B2:C4)

Was ist hier los? Es stellt sich heraus, dass wir in zwei verschiedene Richtungen multiplizieren werden. Es ist schwieriger, dies mit einem geschriebenen Satz zu veranschaulichen, also haben wir ein paar Bilder, die uns dabei helfen. Zunächst wird unser Zeilenkriterium (ist es Rot?) über jede Zeile im Array multipliziert.

=SUMMENPRODUKT((A2:A4="Rot")*B2:C4)

Als nächstes wird das Spaltenkriterium (ist es Kategorie A?) in jeder Spalte multipliziert.

=SUMMENPRODUKT((A2:A4="Rot")*(B1:C1="A")*B2:C4)

Nachdem diese beiden Kriterien ihre Arbeit getan haben, bleiben nur noch die 5 und die 10 als Nicht-Nullen übrig. SUMMENPRODUKT liefert uns dann die Gesamtsumme von 15 als Antwort.

Erinnern Sie sich daran, dass wir darüber gesprochen haben, dass die Arrays gleich groß sein müssen, es sei denn, es handelt sich um zwei Dimensionen. Das war teilweise richtig. Schauen Sie sich noch einmal die Arrays an, die wir in unserer Formel verwendet haben. Die Höhe von zwei unserer Arrays ist die gleiche, und die Breite von zwei unserer Arrays ist die gleiche. Sie müssen also immer noch sicherstellen, dass die Dinge richtig ausgerichtet sind, aber Sie können es in verschiedenen Dimensionen tun.

Zwei Dimensionen und komplex

Oft liegen uns Daten vor, die nicht im besten Layout für unsere Formeln sind. Wir können versuchen, sie manuell neu anzuordnen, oder wir können unsere Formeln intelligenter einsetzen. Betrachten wir die folgende Tabelle.

Hier haben wir die Daten für unsere Artikel und Verkäufe für jeden Monat gemischt. Wie würden wir herausfinden, wie viele Artikel Bob im gesamten Jahr verkauft hat?

Dazu werden wir zwei zusätzliche Funktionen verwenden: SUCHEN und ISTZAHL. Mit der SUCHEN-Funktion können wir in den Kopfzellen nach dem Schlüsselwort „Artikel“ suchen. Die Ausgabe dieser Funktion besteht entweder aus einer Zahl oder einem Fehler (wenn das Schlüsselwort nicht gefunden wird). Dann werden wir ISTZAHL verwenden, um diese Ausgabe in unsere booleschen Werte umzuwandeln. Unsere Formel wird wie unten dargestellt aussehen.

Mit dem ersten Array sollten Sie inzwischen ziemlich vertraut sein. Es wird eine Ausgabe wie {0; 1; 0; 1} erzeugen. Das nächste Kriterien-Array, worüber wir gerade gesprochen haben. Es erzeugt eine Zahl für alle Zellen mit „Artikel“ darin und einen Fehler für die anderen {5; #NV!; 5; #NV!}. Die ISTZAHL-Funktion wandelt dies dann in Boolesche Werte um {WAHR; FALSCH; WAHR; FALSCH}. Wenn wir dann multiplizieren, werden nur die Werte der ersten und dritten Spalte beibehalten. Nachdem alle Arrays miteinander multipliziert wurden, sind die einzigen Zahlen, die keine Nullen sind, diejenigen, die hier hervorgehoben sind:

=SUMMENPRODUKT((A2:A5="Bob")*(ISTZAHL(SUCHEN("Artikel";B1:E1))*B2:E5))

SUMMENPRODUKT summiert diese Zahlen und wir erhalten das Endergebnis 29.

SUMMENPRODUKT Oder

Es gibt viele Situationen, in denen wir die Werte summieren möchten, wenn unsere Kriterienspalte einen Wert ODER einen anderen enthält. Sie können dies in SUMMENPRODUKT erreichen, indem Sie zwei Kriterien-Arrays gegeneinander summieren.

In diesem Beispiel möchten wir die verkauften Einheiten für Rot und Blau summieren.

Unsere Formel sieht wie folgt aus:

=SUMMENPRODUKT(A2:A7; (B2:B7="Rot")+(B2:B7="Blau"))

Schauen wir uns das Array des Kriteriums „Rot“ an. Es wird ein Array erzeugen, das wie folgt aussieht: {1; 1; 0; 0; 0; 0}. Das blaue Kriterien-Array sieht wie {0; 0; 1; 0; 1; 0} aus. Wenn Sie die beiden Arrays zusammenfügen, sieht das neue Array wie folgt aus: {1; 1; 1; 0; 1; 0}. Wir können sehen, wie die beiden Arrays zu einem einzigen Kriterien-Array verschmolzen sind. Die Funktion multipliziert diesen Wert dann mit unserem ersten Array und wir erhalten {100; 50; 10; 0; 75; 0}. Beachten Sie, dass die Werte für Grün auf Null gesetzt wurden. Der letzte Schritt von SUMMENPRODUKT besteht darin, alle Zahlen zu summieren, um die Lösung 235 zu erhalten.

Ein Wort der Warnung an dieser Stelle. Seien Sie vorsichtig, wenn sich die Kriterien-Arrays gegenseitig nicht ausschließen. In unserem Beispiel konnten die Werte in Spalte B entweder Rot oder Blau sein, aber wir wussten, dass es nie beides sein konnte. Stellen Sie sich vor, wir hätten diese Formel geschrieben:

=SUMMENPRODUKT(A2:A7; (A2:A7>=50)+(B2:B7="Blau"))

Wir wollen blaue Artikel, die verkauft wurden oder in einer Menge von mehr als 50 Stück vorhanden sind, finden. Diese Bedingungen schließen sich jedoch nicht aus, da eine einzelne Zeile sowohl über 50 in Spalte A als auch blau sein könnte. Dies würde dazu führen, dass das erste Kriterien-Array wie {1; 1; 0; 1; 1; 0} aussieht und das zweite wie {0; 0; 1; 0; 1; 0}. Wenn man sie zusammenzählt, erhält man {1; 1; 1; 1; 2; 0}. Sehen Sie, dass wir jetzt eine 2 drin haben? Würde man dies unverändert lassen, würde SUMMENPRODUKT den Wert in dieser Zeile verdoppeln und aus der 75 eine 150 machen und wir würden das falsche Ergebnis erhalten. Um dies zu korrigieren, fügen wir eine äußere Kriterienprüfung für unser Array etwa so ein:

=SUMMENPRODUKT(A2:A7; --((A2:A7>=50)+(B2:B7="Blau")>0))

Nachdem die beiden inneren Kriterien-Arrays summiert wurden, prüfen wir, ob das Ergebnis größer als 0 ist. Dadurch wird die 2, die wir vorher hatten, überflüssig und wir haben stattdessen ein Array wie {1; 1; 1; 1; 1; 0}, das das richtige Ergebnis liefert.

SUMMENPRODUKT-IDENTLISCH

Die meisten Funktionen in Excel unterscheiden nicht zwischen Groß- und Kleinschreibung, aber manchmal müssen wir in der Lage sein, eine Suche unter Berücksichtigung der Groß- und Kleinschreibung durchzuführen. Wenn das gewünschte Ergebnis numerisch ist, können wir dies erreichen, indem wir die IDENTISCH-Funktion innerhalb der SUMMENPRODUKT-Funktion verwenden. Betrachten Sie die folgende Tabelle:

Wir möchten die Punktzahl für das Element „ABC123“ ermitteln. Normalerweise vergleicht die IDENTISCH-Funktion zwei Elemente und gibt eine boolesche Ausgabe zurück, die angibt, ob die beiden Elemente genau gleich sind. Da wir uns jedoch in einer SUMMENPRODUKT-Funktion befinden, weiß unser Computer, dass wir es mit Arrays zu tun haben und ist in der Lage, ein Element mit jedem anderen in einem Array zu vergleichen. Unsere Formel sieht dann so aus:

=SUMMENPRODUKT(--IDENTISCH("ABC123"; A2:A5); B2:B5)

Die IDENTISCH-Funktion prüft dann jedes Element in A2:A5, um zu sehen, ob es dem Wert und der Groß-/Kleinschreibung entspricht. Das Ergebnis ist ein Array, das wie {0; 1; 0; 0} aussieht. Bei der Multiplikation mit B2:B5 wird das Array zu {0; 2; 0; 0}. Nach der abschließenden Summierung erhalten wir unsere Lösung von 2.

SUMMENPRODUKT in Google Sheets

Die SUMMENPRODUKT-Funktion funktioniert in Google Sheets genau so wie in Excel:

SUMMENPRODUKT-Beispiele in VBA

Sie können die SUMMENPRODUKT-Funktion auch in VBA verwenden. Geben Sie Folgendes ein:

Application.WorksheetFunction.SumProduct(Array1,Array2,Array3)

Ausführen der folgenden VBA-Anweisungen

Range("B10") = Application.WorksheetFunction.SumProduct(Range("A2:A7"), Range("B2:B7"))

wird die folgenden Ergebnisse erzeugen:

Die Funktionsargumente (Array1 usw.) können Sie entweder direkt in die Funktion eingeben oder Variablen definieren, die Sie stattdessen verwenden.