SUMMENPRODUKT in Excel: multiplizieren, summieren und mit Bedingungen zählen, alles in einer Zelle

Die meisten Excel-Funktionen erfüllen eine offensichtliche Aufgabe. Auch SUMMENPRODUKT scheint auf den ersten Blick nur eine offensichtliche Sache zu tun: ein paar Zahlen multiplizieren und addieren. Sobald du sein Geheimnis kennst, verwandelt es sich jedoch in eines der vielseitigsten Werkzeuge der ganzen Anwendung. Die Möglichkeiten sind vielfältig: Es kann eine Rechnung summieren, einen gewichteten Mittelwert bilden, mit zwei oder drei Bedingungen summieren, passende Zeilen zählen und sogar ODER-Logik ausführen. Und das alles ohne eine einzige Hilfsspalte und ohne das alte Array-Tastenkürzel.

Der Grund, warum so wenige Einsteiger sie nutzen, ist, dass niemand das Geheimnis erklärt. In der Funktion „SUMMENPRODUKT” werden die Werte „WAHR” und „FALSCH” zu „1” und „0”, mit denen du rechnen kannst. Diese eine Idee schaltet alles Clevere frei, was die Funktion kann. Dieser Leitfaden beginnt bei der schlichten Multipliziere-und-additiere-Version und arbeitet sich dann zu den Kunststücken hoch, wobei jedes mit einem Bild erklärt wird. Alle Beispiele bauen aufeinander auf und du kannst sie selbst nachbauen.

FAQ — Welche Fragen beantwortet dieser Artikel?

Q: Was macht die SUMMENPRODUKT-Funktion?

A: Im einfachsten Fall nimmt die Funktion SUMMENPRODUKT zwei oder mehr Spalten mit gleich hoher Zeilenzahl, multipliziert sie Zeile für Zeile und addiert alle Ergebnisse. Das Ergebnis ist eine einzige Zahl. =SUMMENPRODUKT(Säcke; Preis) liefert dir den Gesamtumsatz ohne Hilfsspalte. Seine tiefere Stärke sind bedingte Summen und Zählungen, indem du WAHR/FALSCH-Prüfungen einmultiplizierst.

Q: Warum SUMMENPRODUKT statt einer Hilfsspalte und SUMME?

A: Weil es das Multiplizieren unsichtbar in einer einzigen Zelle erledigt. Es gibt keine zusätzliche Spalte zu bauen, zu speichern, zu schützen oder versehentlich zu überschreiben, und die Formel funktioniert weiter, wenn deine Daten wachsen. Für einen Einmalfall macht es kaum einen Unterschied; für einen Bericht, den du pflegst, ist es viel sauberer.

Q: Wie macht SUMMENPRODUKT bedingte Summen und Zählungen?

A: Eine Prüfung wie (Region="Berlin") erzeugt eine Spalte aus WAHR/FALSCH-Werten. Multipliziere sie mit deinen Zahlen, und aus WAHR wird 1 (Zeile behalten) und aus FALSCH wird 0 (Zeile löschen). Multipliziere zwei Prüfungen, und du bekommst UND. Lass die Werte-Spalte ganz weg, und du zählst, statt zu summieren.

Q: Was ist der Unterschied zwischen * und einem Semikolon in SUMMENPRODUKT, und warum schreiben Leute --?

A: Wenn du Matrizen mit * verbindest, erzwingt die Multiplikation automatisch die Umwandlung von WAHR/FALSCH in 1/0. Wenn du sie mit Semikolon trennst, wandelt SUMMENPRODUKT logische Werte nicht um, es behandelt WAHR/FALSCH als 0. Also multipliziere die Prüfungen entweder miteinander oder wandle sie selbst mit einem doppelten Minus -- (oder *1) um.

Q: Braucht man SUMMENPRODUKT in Excel 365 mit dynamischen Arrays noch?

A: Es ist weniger unverzichtbar als früher, in Excel 365 läuft ein einfaches =SUMME((A2:A7="Berlin")*C2:C7) jetzt automatisch über (es „spillt“) und funktioniert ohne Strg+Umschalt+Eingabe, und SUMMEWENNS/ZÄHLENWENNS decken viele Alltagsfälle ab. Aber SUMMENPRODUKT gewinnt weiterhin bei ODER-Logik in einer Zelle, bei Prüfungen, die auf einer Berechnung beruhen, bei gewichteten Summen und bei garantierter Kompatibilität über alle Excel-Versionen hinweg.

Q: Verlangsamt SUMMENPRODUKT eine Arbeitsmappe?

A: Das kann es, wenn du ihm ganze Spaltenbezüge wie A:A über Hunderttausende Zeilen gibst, weil es jede Zelle auswertet. Richte es auf die Zeilen, die du tatsächlich hast (oder eine Tabelle), dann ist es für normale Geschäftsdaten völlig schnell genug.

Die Grundidee: ein Reißverschluss für zwei Spalten

Stell dir einen Reißverschluss vor. Zwei Reihen von Zähnen kommen zusammen, und der Schieber verzahnt sie Paar für Paar, den ganzen Weg hinauf. Genau so arbeitet SUMMENPRODUKT. Es läuft zwei (oder mehr) Spalten im Gleichschritt hinunter und multipliziert bei jeder Zeile die Werte, die nebeneinander stehen. Dann addiert es jedes dieser Produkte zu einer einzigen Summe.

Das ist der ganze Motor. Zwei Spalten {2; 5; 3} und {10; 4; 20} werden zu den Paaren 2×10, 5×4, 3×20, das ist {20; 20; 60}, und SUMMENPRODUKT addiert sie zu 100. In einer Zelle:

=SUMMENPRODUKT(Säcke; Preis)

Die Syntax

=SUMMENPRODUKT(Matrix1; [Matrix2]; [Matrix3]; ...)

Jede Matrix ist ein Bereich oder eine Berechnung, die eine Spalte (oder ein Raster) aus Zahlen erzeugt. SUMMENPRODUKT multipliziert die Matrizen elementweise und summiert dann alles. Es gibt nur eine harte Regel, und Einsteiger dürfen sie nicht überspringen: Jede Matrix muss gleich groß sein, dieselbe Zeilenanzahl (und Spaltenanzahl). Wenn ein Bereich von Zeile 2 bis 7 reicht und ein anderer von Zeile 2 bis 8, kann Excel die Zähne nicht aufeinander ausrichten und gibt den Fehler #WERT! zurück.

Gibst du SUMMENPRODUKT nur eine Matrix, addiert es sie einfach und verhält sich wie SUMME. Die Magie beginnt bei zwei.

Der Ein-Zellen-Trick: Schluss mit Hilfsspalten

Vor SUMMENPRODUKT war der klassische Weg, „Menge mal Preis“ zu summieren, eine Hilfsspalte zu bauen. =Säcke*Preis in jeder Zeile, und diese Spalte dann mit SUMME zu addieren. Das funktioniert, aber jetzt gehört dir für immer eine zusätzliche Spalte: Sie muss gebaut, gepflegt und vor einer versehentlichen Änderung geschützt werden.

SUMMENPRODUKT fasst beide Schritte in einer Zelle zusammen. Gleiches Ergebnis, nichts Zusätzliches auf dem Blatt, und es dehnt sich automatisch aus, wenn du Zeilen hinzufügst. Das allein würde ihm schon einen Platz in deinem Werkzeugkasten sichern. Aber wir wärmen uns gerade erst auf.

Das Beispiel: eine Kaffeerösterei

Alles ab hier nutzt ein kleines Auftragsbuch einer fiktiven Rösterei, die Kaffee sackweise im Großhandel verkauft. Sechs Aufträge, Spalten A bis E, Daten in den Zeilen 2 bis 7:

Aus diesen sechs Zeilen quetschen wir sechs verschiedene Antworten heraus.

Antwort 1: Gesamtumsatz in einer Zelle

Umsatz ist Säcke mal Preis, über alle Aufträge summiert:

=SUMMENPRODUKT(C2:C7; D2:D7)

Das ist 12×9 + 8×8 + 20×8 + 15×9 + 10×9 + 6×10 = 108 + 64 + 160 + 135 + 90 + 60 = 617 €. Keine Hilfsspalte, eine Zelle.

Antwort 2: Ein gewichteter Mittelwert, der die Wahrheit sagt

Hier fängt SUMMENPRODUKT an, clever zu wirken. Was ist die durchschnittliche Kundenbewertung? Die naive Antwort mittelt die Bewertungsspalte: (5+4+4+3+5+4) / 6 = 4,17. Aber das behandelt einen 6-Säcke-Auftrag und einen 20-Säcke-Auftrag als gleich wichtig und so denkt ein Betrieb nicht. Ein gewichteter Mittelwert gewichtet jede Bewertung mit der Anzahl der Säcke:

=SUMMENPRODUKT(C2:C7; E2:E7) / SUMME(C2:C7)

Der SUMMENPRODUKT-Teil multipliziert Säcke mal Bewertung und summiert es: 12×5 + 8×4 + 20×4 + 15×3 + 10×5 + 6×4 = 291. Teile durch die 71 Säcke insgesamt, und du erhältst 4,10, niedriger als die schlichten 4,17, weil dieser große 20-Säcke-Filter-Auftrag (nur mit 4 bewertet) mehr Gewicht verdient. Dieses „Summe aus Wert×Gewicht, geteilt durch Summe der Gewichte“ ist das klassische Muster des gewichteten Mittelwerts, und SUMMENPRODUKT drückt es in einer sauberen Zeile aus.

Das Geheimnis, das alles möglich macht: WAHR/FALSCH werden zu 1/0

Jetzt die eigentliche Magie. Tippe einen Vergleich über einen Bereich, und Excel erzeugt eine Spalte logischer Werte. (A2:A7="Berlin") ergibt für unsere Rösterei {WAHR; FALSCH; WAHR; FALSCH; WAHR; FALSCH}. Für sich genommen ist das nur ein Ja/Nein. Aber sobald du damit rechnest, wandelt Excel jedes WAHR in 1 und jedes FALSCH in 0 um.

Diese Spalte aus 1 und 0 ist eine Maske. Multipliziere deine Zahlen mit der Maske, und die 1 behält ihre Zeile, während die 0 ihre auslöscht. Das ist die gesamte Grundlage für jeden bedingten Trick weiter unten.

Eine Warnung, die das Bild hervorhebt, weil sie fast jeden erwischt: Wenn du rohes WAHR/FALSCH als mit Semikolon getrenntes Argument an SUMMENPRODUKT übergibst — =SUMMENPRODUKT((A2:A7="Berlin"); C2:C7) — behandelt es die logischen Werte still als 0 und gibt 0 zurück. Du musst die Prüfung entweder mit * einmultiplizieren oder sie selbst mit einem doppelten Minus -- umwandeln. Gleich mehr dazu.

Antwort 3: Bedingter Umsatz mit einer Prüfung

Gesamtumsatz nur für Berlin:

=SUMMENPRODUKT((A2:A7="Berlin") * C2:C7 * D2:D7)

Die Maske (A2:A7="Berlin") ist {1;0;1;0;1;0}. Multipliziere sie durch, und die München- und Hamburg-Zeilen fallen auf null, es bleibt 108 + 160 + 90 = 358 €. Beachte: Das ist ein Argument. Alles wird innerhalb einer Klammer multipliziert. Das Sternchen, also die *-Funktion, erledigt die Umwandlung für uns.

Antwort 4: Zwei Prüfungen, verknüpft mit UND

Umsatz für Berlin und Espresso? Multipliziere eine zweite Maske ein:

=SUMMENPRODUKT((A2:A7="Berlin") * (B2:B7="Espresso") * C2:C7 * D2:D7)

Jede Zeile trägt jetzt zwei Masken. Multipliziere sie, und eine Zeile überlebt nur, wenn beide 1 sind, denn 1×1=1, aber 1×0 und 0×0 sind beide 0. Eine einzige 0 irgendwo in der Zeile löscht sie. Nur die zwei Berlin-Espresso-Aufträge bleiben: 108 + 90 = 198 €. Deshalb verhält sich * wie UND. Füge eine dritte Prüfung auf die gleiche Weise hinzu für drei Bedingungen, eine vierte für vier und so weiter.

Antwort 5: Zählen ist einfach das Summieren der Maske

Hier kommt der elegante Teil. Um die Berlin-Espresso-Aufträge zu zählen, statt ihren Umsatz zu summieren, behalte die Masken und lass die Werte-Spalten weg:

=SUMMENPRODUKT((A2:A7="Berlin") * (B2:B7="Espresso"))

Da es nur 1 und 0 zu multiplizieren gibt, addiert SUMMENPRODUKT einfach die Einsen und davon gibt es 2. Derselbe Filter, eine kleine Änderung, und aus einer bedingten Summe wird eine bedingte Zählung. (Genau das macht ZÄHLENWENNS; SUMMENPRODUKT lässt dich das nur von Hand bauen, was sich auszahlt, wenn die Bedingungen exotisch werden.)

Wenn du die Prüfungen lieber als getrennte, mit Semikolon getrennte Argumente behältst, wandle jede zuerst mit einem doppelten Minus in Zahlen um:

=SUMMENPRODUKT(--(A2:A7="Berlin"); --(B2:B7="Espresso"))

-- heißt schlicht „negieren, dann noch einmal negieren“: Es kippt WAHR zu −1 zu 1 und FALSCH zu 0 zu 0, ein Weg mit zwei Zeichen, um logische Werte in Zahlen zu verwandeln.

Noch weiter: ODER-Logik und 2-D-Raster

ODER nutzt Addition statt Multiplikation. Umsatz aus Berlin oder München:

=SUMMENPRODUKT(((A2:A7="Berlin") + (A2:A7="München")) * C2:C7 * D2:D7)

Wo die Multiplikation UND ergab, ergibt die Addition ODER: Eine Zeile bekommt 1, wenn eine der beiden Prüfungen WAHR ist. Das sind 358 (Berlin) + 124 (München) = 482 €. Eine Warnung, weil du Masken addierst, würde eine Zeile, die beide Bedingungen erfüllt, 2 ergeben und doppelt gezählt. Wenn die beiden Prüfungen auf derselben Spalte liegen (eine Region kann nicht gleichzeitig Berlin und München sein), bist du sicher; wenn sie sich überschneiden könnten, überdenke die Logik.

SUMMENPRODUKT beherrscht auch zweidimensionale Raster. Richte es auf einen ganzen rechteckigen Block, etwa eine Tarif­tabelle mit Regionen an der Seite und Produkten oben, multipliziere ihn mit einem passenden Block aus Mengen, und es multipliziert und summiert die gesamte Matrix in einer Zelle. Das ist ein wirklich fortgeschrittener Zug, aber er folgt genau derselben Regel: gleich große Matrizen ausrichten, multiplizieren, addieren.

Dein Spickzettel

Halte diese Karte bereit, bis die Muster in Fleisch und Blut übergegangen sind.

Die Regel * gegen Semikolon, ein für alle Mal

Das ist die häufigste Quelle der Verwirrung, deshalb hier ganz klar:

Verbinde deine Matrizen mit * und packe das Ganze in eine Klammer — =SUMMENPRODUKT((Prüfung1) * (Prüfung2) * Werte) — und die Multiplikation wandelt jedes WAHR/FALSCH für dich in 1/0 um. Diesen Stil nutzen die meisten, weil er sich natürlich liest als „Prüfung UND Prüfung, mal den Zahlen“.

Trenne deine Matrizen mit Semikolon — =SUMMENPRODUKT(Matrix1; Matrix2) — und SUMMENPRODUKT multipliziert sie, wandelt aber logische Werte nicht um; jede WAHR/FALSCH-Matrix wird als Nullen gelesen. Mit Semikolon musst du also jede Prüfung mit -- oder *1 vorab umwandeln. Beide Stile sind korrekt; mische nur nicht ein Semikolon mit einer nicht umgewandelten Prüfung und erwarte eine andere Zahl als 0.

Häufige Fehler und wie du sie behebst

Ungleich große Bereiche. Ein #WERT!-Fehler bedeutet fast immer, dass eine deiner Matrizen eine andere Höhe hat als die übrigen. Sorge dafür, dass jeder Bereich exakt dieselben Zeilen umfasst. Eine Excel-Tabelle (siehe unten) verhindert das vollständig.

Rohes WAHR/FALSCH mit Semikolon gibt 0 zurück. Wenn ein bedingtes SUMMENPRODUKT 0 zurückgibt, obwohl du eine Zahl erwartest, hast du wahrscheinlich eine Prüfung als Semikolon-Argument übergeben, ohne sie umzuwandeln. Wechsle zum *-Stil oder umschließe die Prüfung mit --.

Ganze-Spalten-Bezüge bei großen Daten. A:A zwingt SUMMENPRODUKT, sich durch eine Million Zeilen zu arbeiten, und kann eine Arbeitsmappe träge machen. Verweise stattdessen auf den tatsächlichen Datenbereich oder eine Tabellenspalte.

Klammern um eine Prüfung vergessen. Jeder Vergleich braucht seine eigenen Klammern: (A2:A7="Berlin"). Ohne sie erzeugt die Operatorrangfolge Unsinn.

Text, der wie Zahlen aussieht. Wenn eine Summe zu niedrig ausfällt, sind die „Zahlen“ vielleicht Text aus einem Export. Der Text "9" multipliziert nicht wie die Zahl 9. Bereinige die Spalte zuerst.

Robust mit Excel-Tabellen

Fest verdrahtete Bereiche wie C2:C7 veralten in dem Moment, in dem du einen Auftrag hinzufügst. Lege die Daten in eine Tabelle (markieren und Strg+T drücken) und verwende ihre Spaltennamen:

=SUMMENPRODUKT((Bestellungen[Region]="Berlin") * (Bestellungen[Röstung]="Espresso") * Bestellungen[Säcke] * Bestellungen[Preis])

Jetzt bedeutet Bestellungen[Säcke] immer die ganze Säcke-Spalte, egal auf wie viele Zeilen die Tabelle wächst. Neue Aufträge werden automatisch einbezogen, die Bereiche können nie aus dem Takt geraten, und die Formel liest sich fast wie ein Satz.

Wann du zu SUMMENPRODUKT greifst und wann nicht

Greif zu SUMMEWENNS / ZÄHLENWENNS für das alltägliche „summieren/zählen, wo diese Spalten diesen Werten entsprechen“. Sie sind schneller, lesen sich klar und nehmen bereitwillig ganze Spaltenbezüge. Wenn deine Aufgabe eine geradlinige Mehrfach­bedingungs-Summe mit UND-Logik ist, sind sie meist die bessere erste Wahl.

Greif zu SUMMENPRODUKT, wenn du etwas brauchst, das sie nicht in einer Zelle ausdrücken können: ODER-Logik, eine Bedingung, die auf einer Berechnung statt einer schlichten Spalte beruht, eine gewichtete Summe oder einen gewichteten Mittelwert oder ein zweidimensionales Raster. Und wenn eine Datei in alten Excel-Versionen ohne dynamische Arrays laufen muss, ist SUMMENPRODUKT der verlässliche Klassiker.

Greif zu Power Pivot und DAX, wenn die Daten über ein Blatt hinauswachsen, viele Tabellen, Hunderttausende Zeilen, derselbe Monatsbericht immer wieder neu. Ein Berechnung wie SUMX oder SUM in einem Datenmodell überholt und überlebt eine Wand aus SUMMENPRODUKT-Formeln.

Zusammenfassung

SUMMENPRODUKT belohnt ein Bild im Kopf: ein Reißverschluss, der Spalten Zahn für Zahn verzahnt, jedes Paar multipliziert, alles addiert, während WAHR/FALSCH still zu 1/0 werden, damit eine Prüfung Zeilen ein- und ausschalten kann. Halte dieses Bild fest, und die „fortgeschrittenen“ Formeln hören auf, fortgeschritten zu sein. Sie sind alle derselbe Trick, nur anders verpackt.

Was du als Nächstes lesen solltest

Dieser Leitfaden passt gut zu einigen anderen SafeOffice-Artikeln. Der WAHR/FALSCH-Artikel ist die Theorie hinter dem ganzen Trick der booleschen Arithmetik. Der SUMMEWENNS-Artikel behandelt das alltägliche Arbeitspferd der bedingten Summe, das SUMMENPRODUKT ergänzt. Der Tabellen-Artikel bewahrt deine Bereiche davor, zu veralten, und der Power-Pivot-Artikel ist der Ort, an den du gehst, wenn die Daten über ein Arbeitsblatt hinauswachsen.

Siehe: Boolesche Logik in Excel: die Magie von WAHR und FALSCH

Siehe: So verwendest du SUMMEWENNS, um mit mehreren Kriterien zu summieren

Siehe: Excel-Tabellen: warum jeder Datensatz eine sein sollte

Siehe: Power Pivot und das Datenmodell: ein Anfängerleitfaden