SUMMENPRODUKT ist eine Funktion, welche nicht mehr wegzudenken ist und viele Funktionen ersetzen kann, wenn du sie erst mal kennen und einsetzen gelernt hast.

Sie ersetzt viele andere Funktionen da man mit ihr nicht nur multiplizieren und addieren, sondern auch zählen, und suchen kann. Sie ersetzt somit zum Teil die Funktionen SUMMEWENN, ZÄHLENWENN, INDEX und VERGLEICH sowie die Funktion SUMME.

Die Funktion bildet aber nicht Summen und multipliziert sie dann, wie der Name fälschlicherweise glauben lässt. Die Funktion SUMMENPRODUKT bildet einzelne Produkte und summiert diese.

In diesem Artikel möchte ich dir einen Einblick in die Funktion geben und dir zeigen wie die Funktion SUMMENPRODUKT aufgebaut ist und wozu man sie nutzen kann.

Achja bevor ichs vergesse, lade dir hier die Übungsdatei herunter: Übungsdatei SUMMENPRODUKT

Damit kannst du die einzelnen Beispiele besser nach vollziehen.

Syntax der Formel SUMMENPRODUKT

Die Funktion SUMMENPRODUKT ist folgendermaßen aufgebaut.

=SUMMENPRODUKT(Matrix1;Matrix2;…)

Dabei sind maximal 255 Matrizen möglich. Jede Matrix oder Array für sich kann aus einzelnen oder mehreren Zellen bestehen. Eine Matrix kann auch mehrere Zeilen und Spalten groß sein.

Die Funktion SUMMENPRODUKT multipliziert einzelne Werte von Arrays oder Matrizen miteinander und gibt dir die Summe dieser einzelnen Multiplikationen wieder.

SUMMENPRODUKT ist zwar eine Array Funktion, du musst diese aber nicht mit STRG+SHIFT+ENTER abschließen.

Als Ergebnis der Funktion erhältst du immer eine Zahl.

Wenn du mehr über den allgemeinen Aufbau von Formeln und Funktionen wissen möchtest, dann schau dir gerne meinen Artikel dazu an. Excel Formel Grundlagen verstehen und von der Zeitersparnis profitieren

Argumente

Die Argumente der Funktion sind die einzelnen Arrays.

Eine Matrix oder Array kann in Excel in folgenden Formen auftreten:

  • Ein Einzelwert, z.B. A5
  • Ein Bereich, z.B. A5:A8 oder A2:C24. Der Bereich muss zusammenhängend sein kann aber mehrere Zeilen und Spalten gleichzeitig umfassen.
  • Eine Spalte in einer Tabelle. Tabelle_A_Summenprodukt[A]
  • Eine in geschweiften Klammern gesetzte Aufzählung von Werten, z.B.: {12;21;9;17}
  • Als Name für einen Bereich z.B.: „Verkaufszahlen“ für A7:A34

Mehr zu Namen und deren Verwendung erfährst du in diesem Artikel: Alles was du über einen Zellbezug oder eine Referenz in Excel wissen musst

  • Eine mittels Berechnung erzeugte Matrix. Beispielsweise werden nur jene Werte aus einem Bereich verwendet, die größer als 5 sind. Oder jene die mit einer anderen Formel ermittelt werden z.B.: MITTLEWERT.

In der Übungsdatei findest du zu jeder Array Form ein konkretes Beispiel in dem Arbeitsblatt SUMMENPRODUKT klassisch. Hier kannst du dir die Übungsdatei herunterladen

So kannst du die Formel SUMMENPRODUKT einsetzen

Wie die Funktion aufgebaut ist, weißt du nun. Jetzt kommen wir zum wichtigsten Teil, arbeiten wir mit der Funktion. So kann ich dir am besten zeigen, wie du sie einsetzen kannst und was alles mit ihr möglich ist.

Arbeite dazu am besten parallel in der Übungsdatei mit, so kannst du die Beispiele am besten nachvollziehen.

In den folgenden Absätzen möchte ich dir die nützlichsten Anwendungen von SUMMENPRODUKT zeigen. Es gibt darüber hinaus natürlich unzählige weitere Anwendungsmöglichkeiten und Abwandlungen der gezeigten Varianten.

Klassischer gebrauch der Funktion SUMMENPRODUKT

Der einfachste oder ursprünglichste Einsatz der Funktion ist es, aus zwei Bereichen die einzelnen Werte miteinander zu multiplizieren und danach die Summe zu bilden. Das kann auf unterschiedliche Arten geschehen.

Das Ergebnis der Funktion würde wie folgt berechnet:

=SUMMENPRODUKT(C4:C6;D4:D6)= ((2*7)+(5*9)+(8*1))=(14+45+8)=67

Die Funktion multipliziert den ersten Wert aus dem ersten Array mit dem ersten Wert des zweiten Arrays und so weiter. Alle berechneten Werte werden abschließend summiert.

In diesem Bild aus der Übungsdatei findet ihr alle möglichen Schreibweisen einer SUMMENPRODUKT Funktion untereinander aufgelistet.

Die Funktion SUMMENPRODUKT als Ersatz für SUMME

Neben der eigentlichen Verwendung kann man mit der Funktion SUMMENPRODUKT auch Summen berechnen. Zwar ist sie dann etwas unterfordert, aber möglich ist es.

Aber wie macht die Funktion das. Normalerweise multipliziert die Funktion die Werte eines Arrays mit den Werten eines anderen Arrays. In diesem Fall gibt es aber nur ein Array. Dadurch wird nicht mit Null sondern mit keinem Wert multipliziert und danach die Summe gebildet. Es werden zwar beide Berechnungen durchgeführt, relevant ist aber nur die zweite.

Additionen in und mit der Funktion SUMMENPRODUKT

Da ein Rechenschritt die Summenbildung ist, muss es möglich sein, einzelne Werte auch zu addieren und das ist es auch.

Es lassen sich ganze Arrays oder Einzelwerte zu Arrays addieren. Additionen können sowohl als „Hauptrechnung“ ausgeführt werden sowie auch als Nebenrechnung in einem Array. Das siehst du in folgenden Beispielen.

Geballtes Wissen an einem Ort

In meinem Blog findest du viele hilfreiche Artikel, die dir dabei helfen, dein Wissen aufs nächste Level zu heben. Ganz egal ob du nur nach einem einmaligen Problemlöser suchst oder ob du dauerhaft profitieren möchtest. In meinem Blog wirst du fündig.

Geballtes Wissen in einem Buch

Mit diesen hilfreichen Tipps kannst du in nur 17 Minuten, 90% der anderen Excel Nutzer übertrumpfen.

Zählen mit der Funktion SUMMENPRODUKT

Du kannst auch ganz einfach Werte zählen.

Du kannst in der Funktion SUMMENPRODUKT einzelne Arrays auf Bedingungen prüfen. So lassen sich Werte eines Arrays darauf überprüfen, ob sie mit einer Bedingung ident sind also zum Beispiel mit dem Kriterium, das gezählt werden soll.

Ich möchte dir das Anhand von folgendem Beispiel zeigen. In dem Arbeitsblatt „SUMMENPRODUKT-Zählen“ in der Übungsdatei in Zelle B12 wird gezählt, wie oft „Max“ in der Spalte „Verkäufer“ vorkommt. Das wird mit dieser Formel berechnet:

=SUMMENPRODUKT((Tabelle_C_Summenprodukt[Verkäufer]=“Max„)*1)

Im ersten Array wird jeder Wert aus dem Bereich überprüft, ob er mit „Max“ übereinstimmt.

Daraus ergibt sich eine Liste mit WAHR und FALSCH Werten.

=SUMMENPRODUKT({WAHR;FALSCH;FALSCH;FALSCH;FALSCH;WAHR}*1)

Mit WAHR und FALSCH Werten fangen wir jedoch nichts an, wir wollen eine Zahl erhalten. Also müssen wir das Teilergebnis oder die erhaltene Liste mit 1 multiplizieren.

Wie du erkennst nutze ich zwei Besonderheiten einerseits „*1“ und andererseits „–„.

Die Hintergründe dazu und was es mit WAHR oder FALSCH Werten in der Funktion zu tun hat, zeige ich dir weiter unten in diesem Absatz: Rechnen mit WAHR und FALSCH

Multiplizieren mit der Funktion SUMMENPRODUKT

Ähnlich der Variante, in der die Summe eines Bereiches gebildet wird, werden auch bei dieser Variante beide Rechenschritte durchgeführt. Jedoch bestehen hier die Arrays in den ersten beiden Beispielen aus nur einem Wert. Und die Summe aus einem Wert ist eben dieser Wert.

Multipliziert man wie im dritten Beispiel ein ganzes Array mit einer Zahl oder einem Wert, wird jeder Wert aus dem Array, mit dem Wert multipliziert.

Die Funktion SUMMENPRODUKT als Ersatz für SUMMEWENN und SUMMEWENNS

Langsam, aber sicher kommen wir nun zu den Einsatzgebieten, in denen die Funktion SUMMENPRODUKT all ihre Stärken ausspielen kann.

Beispielsweise kann es die gleichen Ergebnisse liefern wie die Funktion SUMMEWENN, ohne aber auf eine Bedingung eingeschränkt zu sein. Natürlich könnte man einfach die Funktion SUMMEWNNS verwenden, aber auch die ist nur bis zu einem gewissen Grat einfacher und übersichtlicher. Dazu habe ich die letzte Berechnung jeweils mit den Funktionen SUMMENPRODUKT und SUMMEWNNS durchgeführt. Die Funktion SUMMEWENN wäre der Herausforderung nicht mehr gewachsen oder zumindest bei weitem umständlicher.

Mein Favorit auch hier ganz klar die Funktion SUMMENPRODUKT.

Die Funktion SUMMENPRODUKT als Ersatz für ZÄHLENWENN

Mit der Funktion SUMMENPRODUKT lassen sich Zellen abhängig von Bedingungen auch zählen. Somit kann sie auch ein Ersatz für die Funktion ZÄHLENWENN sein.

Rechnen mit mehr dimensionalen Bereichen

Als letzte Einsatzmöglichkeit möchte ich dir das Rechnen mit mehr dimensionalen Bereichen zeigen. Das sind Arrays, deren Bereich mehrere Zeilen und Spalten umfasst.

Hierbei liegt die Besonderheit darin, dass ein mehrdimensionaler Bereich, der über mehrere Spalten oder Zeilen verläuft als ein Array behandelt wird.

Der Bereich C19:D23 wird als Array folgendermaßen dargestellt: {2.7;5.9;8.1;3.7;9.4} Die Punkte trennen die Spalten und der Strichpunkt trennt die Zeilen.

Und hier weitere Möglichkeiten mit mehrdimensionalen Bereichen zu rechnen.

Die unterschiedlichen arithmetischen Operatoren und Trennzeichen

Wenn du jetzt fragst „Was für Dinger?“ dann bist du bestimmt nicht allein.

Als arithmetische Operatoren werden beispielsweise +, -, *, / bezeichnet. In der Funktion SUMMENPRODUKT kannst du die Operatoren zur Berechnung einsetzen.

Wie in den vorher gegangenen Beispielen gezeigt kannst du beispielsweise zwei Arrays mit dem + mit einander addieren oder mit dem – voneinander subtrahieren. Kurz gesagt, kannst du alle Grundrechenarten mit den Arrays durchführen. Dabei musst du lediglich darauf achten, dass du diese Berechnungen in separate Klammern setzt, also um das fertige Array nochmal eine Klammer setzt.

Als Trennzeichen bezeichne ich die Zeichen zwischen den Arrays also den ; oder das *. Ich verwende fast ausschließlich das * Zeichen. Das hat den einfachen Grund, dass der ; nur funktioniert, wenn die Arrays nur aus Zahlen bestehen. Sobald du Prüfungen einbaust also mit WAHR und FALSCH Werten rechnest funktioniert der ; nicht mehr.

Vergleiche und logische Funktionen in der Funktion SUMMENPRODUKT

So langsam, aber sicher schöpfen wir das Potential der Funktion SUMMENPRODUKT aus.

Man kann mit Arrays nicht nur die Grundrechenarten ausführen, auch logische Funktionen und Vergleiche können damit ausgeführt werden.

So lassen sich beispielsweise alle Werte in einem Array mit einer Bedingung vergleichen, um nur mit jenen Werten weiter zu rechnen die mit der Bedingung übereinstimmen.

Folgender Vergleiche kannst du dazu nutzen:

=… Gleich

>… Größer

<… Kleiner

 <>… Nicht

>=… Größer Gleich

<=… Kleiner Gleich

Das Ergebnis dieser Prüfungen ist immer ein WAHR oder ein FALSCH. Im nächsten Absatz betrachten wir genauer, wie du mit WAHR oder FALSCH Werten rechnen kannst und musst.

Daneben kannst du aber auch logische Funktionen verwenden.

Ein + entspricht einer ODER-Funktion und ein * entspricht einer UND-Funktion.

Diese logischen Funktionen können als Ergebnis entweder ein Array mit Zahlen haben oder ein Array mit WAHR und FALSCH Werten, das hängt von den Argumenten der Funktionen ab.

Rechnen mit WAHR und FALSCH

Wie du weißt, hat die Funktion SUMMENPRODUKT als Ergebnis immer eine Zahl.  Daher ist es zwar schön und gut, dass man Vergleiche anstellen kann, jedoch muss man diese WAHR oder FALSCH Werte in Zahlen umwandeln, um mit Ihnen weiter zu rechnen.

Dazu können wir uns die Eigenschaften von WAHR und FALSCH zu Nutze machen. Denn wird der Wert WAHR mit einer Zahl multipliziert wird er automatisch in +1 umgewandelt und wird der Wert FALSCH mit einer Zahl multipliziert, wird er automatisch in eine 0 umgewandelt.

Um das etwas besser zu veranschaulichen folgende Formel als Beispiel. Indem du jeden einzelnen Rechenschritt genau aufgeschlüsselt siehst.

=SUMMENPRODUKT((C4:C6>5)*C4:C6*D4:D6)

=SUMMENPRODUKT(({2;5;8}>5)*{2;5;8}*{7;9;1})

=SUMMENPRODUKT({FALSCH;FALSCH;WAHR}*{2;5;8}*{7;9;1})

=SUMMENPRODUKT({0;0;1}*{2;5;8}*{7;9;1})

=SUMMENPRODUKT({0;0;1}*{14;45;8})

=SUMMENPRODUKT({0;0;8})

Daneben gibt es aber noch andere Möglichkeiten, um Wahrheitswerte in Zahlen umzuwandeln. Weil es natürlich manchmal nicht möglich oder gewünscht ist, Werte mit einem Zahlen Array zu multiplizieren.

Mit folgenden Möglichkeiten lassen sich Wahrheitswerte in Zahlen umwandeln.

Die Wahrheitswerte werden meiner Meinung nach sehr unterschätzt und zu selten eingesetzt, daher habe ich dazu einen eigenen Artikel geschrieben, den ich dir sehr ans Herz legen kann, wenn du dich weiter damit beschäftigen möchtest, was ich nur empfehlen kann. Den Artikel findest du hier.

Formeln in Verbindung mit der Funktion SUMMENPRODUKT

Du kannst natürlich auch in der Funktion SUMMENPRODUKT andere Funktionen verwenden und verschachteln. Dabei sind dir keinerlei Grenzen gesetzt.

Worauf musst du beim Einsatz der Funktion SUMMENPRODUKT achten

Wie bei allen anderen Funktionen in Excel ist es auch bei der Funktion SUMMENPRODUKT so, dass man auf einige Dinge achten muss. Vieles davon hast du in den bisherigen Beispielen bereits kennen gelernt. Ich möchte dir trotzdem nochmal alles übersichtlich auflisten.

Auf folgende Punkte solltest du beim Einsatz der Funktion SUMMENPRODUKT achten:

  • Sind die Arrays in deiner Berechnung als Bereiche definiert, so müssen diese Bereiche die gleiche Größe haben.
  • Wenn du in einem Array Berechnungen oder Prüfungen anstellst, dann setz das gesamte Array in Klammern
  • Arbeitest du mit Wahrheitswerten so musst du das Trennzeichen „;“ durch ein „*“ ersetzen.
  • Nicht numerische Werte in Arrays werden als 0 behandelt.
  • Die Funktion SUMMENPRODUKT unterstützt keine Platzhalter. Wenn du mehr zu Platzhalter generell erfahren möchtest, dann schau dir gerne meinen Artikel dazu an. Platzhalter in Excel

Eine weitere Beschreibung zur Funktion SUMMENPRODUKT findest du auch hier

So kannst du SUMMENPRODUKT einsetzen

Was SUMMENPRODUKT alles leisten kann, haben wir nun ausführlich geklärt, aber wozu kann man die Funktion einsetzen. Das möchte ich dir Anhand eines Beispiels zeigen.

Das Beispiel findest du wie gewohnt in der Übungsdatei. Diese kannst du dir hier herunterladen: Übungsdatei SUMMENPRODUKT

Als Ausgangsdaten benutzen wir eine Umsatzliste für Verkäufe aufgeteilt nach Regionen.

Wir wollen wissen, wieviel Umsatz der Verkäufer Max gemacht hat, wie viel Umsatz in der Region West und Süd gemacht wurde und wie viel Umsatz die Verkäufer Max und Fred in der Region Süd gemacht haben.

Ich habe dir in der Übungsdatei für diese Beispiel genau aufgeschlüsselt wie die Daten im Hintergrund durch die Funktion SUMMENPRODUKT berechnet werden und in was sich diese ändern. Dazu später, aber mehr.

Beginnen wir mit unserer ersten Fragestellung. Wie viel Umsatz hat der Verkäufer Max gemacht?

Welche Bestandteile brauchen wir dazu? In allen Berechnungen brauchen wir den Umsatz. Den können wir berechnen mit der Multiplikation aus „Menge“ und „Einzelpreis“. Als letzten Bestandteil brauchen wir nun noch alle Werte die „Max“ entsprechen.

Unsere Formel sieht somit folgendermaßen aus:

=SUMMENPRODUKT((B2:B31=“Max„)*D2:D31*E2:E31)

Das erste Array prüft, welche Werte „Max“ entsprechen und gibt für diese WAHR aus und für alle die dem nicht entsprechen FALSCH. Dieses Array wird dann mit dem Array „Menge“ und „Einzelpreis“ multipliziert.

Als nächstes möchten wir wissen, wie viel Umsatz in der Region West und Süd gemacht wurden.

Das erfolgt analog dem ersten Beispiel jedoch überprüfen wir diesmal ob die Spalte „Region“ den Bedingungen „West“ und „Süd“ entsprechen. Vergiss dabei nicht auf die Klammern.

=SUMMENPRODUKT(((C2:C31=“West„)+(C2:C31=“Süd„))*D2:D31*E2:E31)

Durch das „+“ zwischen den beiden Prüfungen multipliziert die Funktion sowohl die Werte die „West“ entsprechen als auch „Süd“.

Jetzt möchten wir noch wissen, wie viel Umsatz die Verkäufer „Max“ und „Fred“ in der Region „Süd“ gemacht haben.

Für diese Fragestellung habe ich dir die einzelnen Berechnungsschritte, die im Hintergrund durchgeführt werden Schritt für Schritt aufgeschlüsselt, wie du im nächsten Bild siehst.

=SUMMENPRODUKT(((B2:B31=“Max„)+(B2:B31=“Fred„))*(C2:C31=“Süd„)*D2:D31*E2:E31)

Im ersten Schritt wird im ersten Array geprüft, welche Werte in der Spalte B der Bedingung „Max“ entsprechen und markiert diese als WAHR. Danach wird dasselbe mit der Bedingung „Fred“ durchgeführt.

Im zweiten Schritt wird im zweiten Array geprüft, welche Werte in der Spalte C der Bedingung „Süd“ entsprechen und markiert diese mit WAHR.

Im nächsten Schritt bei der Multiplikation werden die WAHR Werte in 1 und die FALSCH Werte in 0 umgewandelt.

Schritt Nummer 4 ist es, die Werte je Zeile zu multiplizieren.

Als letzter Schritt wird nun die Summe der einzelnen Multiplikationen gebildet.

Das Beispiel soll dir nur als kleine Anregung dienen, was man alles mit der Funktion SUMMENPRODUKT machen kann. Natürlich gibt es darüber hinaus noch viele weitere Einsatzmöglichkeiten.

Fazit

SUMMENPRODUKT ist eine Funktion, die einen enormen Einsatzbereich abdeckt.

Ich verwende sie wo es nur geht, da sie gegenüber anderen Funktionen einfacher und verständlicher aufgebaut ist.

Sie bietet Lösungsmöglichkeiten an, die auf den ersten Blick vielleicht gar nicht zu erkennen sind.

Neben dem herkömmlichen Einsatz, um die Summe von Produkten zu berechnen, kann sie noch als SUMMEWENN oder ZÄHLENWENN Funktion eingesetzt werden.

Wenn du sie erstmal häufiger einsetzt, wirst du sie lieben lernen, davon bin ich überzeugt.

Ich verschicke hin und wieder nützliche Tipps und Tricks rund um Excel per E-Mail. Wenn du diese nicht verpassen möchtest, dann trag dich doch in meinen kostenlosen Newsletter ein. Du kannst dich hier eintragen.

Als kleines Dankeschön erhältst du ein eBook mit 17 nützlichen Tipps, mit denen du in kurzer Zeit dein Excel Können auf den nächsten Level bringst.

Den kostenlosen Newsletter und das Geschenk findest du hier.

Du kannst dich jederzeit auch wieder austragen.

Dein Michael, alias Doktor-Excel der smarte Excel Problem Löser

PS.: Vergiss nicht, solltest du spezifische Probleme mit Excel haben, schreib mir eine Nachricht und wir lösen sie gemeinsam. Hier kannst du mich kontaktieren.