SUMMENPRODUKT: Theorie und Praxis (Teil 1) 3

Die mysteriöse Excel-Funktion SUMMENPRODUKT anschaulich erklärt - Teil 1
 

So, lieber Excel-Freund, es ist wieder soweit: Heute gibt es den ersten Gastartikel des Jahres 2015, geschrieben von meinem geschätzten Excel-Kollegen Gerhard Pundt.

Er widmet sich einer spannenden und extrem mächtigen Excel-Funktion, die bei vielen Anwendern völlig zu Unrecht ein Schattendasein führt. Es geht um die Funktion

SUMMENPRODUKT

Gerhard hat sich so intensiv mit der Materie auseinandergesetzt, dass daraus sogar eine zweiteilige Artikel-Serie geworden ist. Keine Angst: Er hat dazu viele praxisnahe Beispiele gefunden, die dir dabei helfen werden, die Vielseitigkeit dieser mächtigen Funktion besser zu verstehen.

Und nun viel Spaß mit dem ersten Teil:

Ein bisschen Wortspalterei

Das Wort SUMMENPRODUKT erscheint auf den ersten Blick als zusammengesetztes Substantiv. Im Deutschen steht das Grundwort in einem zusammengesetzten Wort stets am Ende. Das Grundwort entscheidet den Gesamtinhalt. So könnte man meinen, es gehe bei SUMMENPRODUKT um das Produkt von Summen. Das Grundwortprinzip lässt sich z.B. beim Begriff Armbanduhren (= Art von Uhren) bzw. beim Begriff Uhrenarmband (= Teil dieser Uhren) nachvollziehen.

Dem ist bei SUMMENPRODUKT nicht so. Bei SUMMENPRODUKT werden die Elemente von Matrizen miteinander multipliziert und die Produkte anschließend summiert. Ein besseres Wort wäre deshalb vielleicht PRODUKTESUMME, aber eine Funktion mit diesem Namen gibt es nicht. Nebenbei, bei der Funktion QUADRATESUMME ist dies besser gelungen. So bleibe ich bei SUMMENPRODUKT und sehe dieses Wort nicht als zusammengesetztes Substantiv, sondern einfach als Namen für eine Funktion.

An ein paar Beispielen möchte ich zeigen, wie die Funktion arbeitet (Beispieldatei hier herunterladen). Die Funktion ist eine tolle Funktion, denn sie kann die Arbeit von mehreren Funktionen, nämlich PRODUKT, WENN bzw. SUMMEWENN bzw. SUMMEWENNS und SUMME in einem Arbeitsgang vereinen. Sie kann dennoch nicht alles. Andere Funktionen haben unvermindert ihre Existenzberechtigung.

Die Beispiele sind ausnahmslos von mir, methodisch habe ich mich allerdings ein wenig umgesehen. Die Funktion SUMMENPRODUKT gehört zur Kategorie “Mathematik und Trigonometrie”. Die Syntax lautet gemäß Excel-Hilfe:

SUMMENPRODUKT(Matrix1;Matrix2;Matrix3; ...)

wobei Matrix1; Matrix2; … 2 bis 255 Matrizen sind, deren Komponenten wir zunächst multiplizieren und anschließend addieren möchten.”

Aus der Praxis heraus muss die Syntax auf eine zweite Schreibweise erweitert werden:

SUMMENPRODUKT(Matrix1*Matrix2*Matrix3; ...)

Beide Schreibweisen werden mit den Beispielen gezeigt.

Beispiel 1: Einspaltige Matrizen

In den Spalten A und B habe ich zwei einspaltige Matrizen aufgelistet. Die Produkte daraus sind in Spalte D errechnet. In E8 schließlich habe ich die Produkte addiert:

Beispiel 1: SUMMENPRODUKT (Variante1)

Beispiel 1: Einfache Matrizen

Die Funktion SUMMENPRODUKT rechnet genauso und führt zum gleichen Ergebnis:

Beispiel 1: SUMMENPRODUKT (Variante 1)

Beispiel 1: SUMMENPRODUKT (Variante 1)

Eine Matrix kann in Excel in folgenden Formen auftreten:

  • ein Bereich, z.B. A5:A8
  • eine in geschweiften Klammern gesetzte Aufzählung von Werten sein, z.B.: {12;21;9;17}
  • Beispiel 1: SUMMENPRODUKT (Variante 2)

    Beispiel 1: SUMMENPRODUKT (Variante 2)

  • ein Name für einen Bereich, z.B. “Matrix1” für den Bereich A5:A8 und Matrix2 für den Bereich B5:B8
  • Beispiel 1: SUMMENPRODUKT (Variante 3)

    Beispiel 1: SUMMENPRODUKT (Variante 3)

  • eine mit einem Namen versehen Formel, z.B. wird im Namensmanager “Formel1” definiert
  • Beispiel 1: SUMMENPRODUKT (Variante 4)

    Beispiel 1: SUMMENPRODUKT (Variante 4)

  • eine während der Berechnung erzeugte, unsichtbare Matrix (mehr dazu im Beispiel 11 in Teil 2 dieser Artikelserie)

Es kann vorkommen, dass die Matrizen nicht parallel zueinander stehen:

SUMMENPRODUKT mit verdrehter Matrix

SUMMENPRODUKT mit verdrehter Matrix

Matrix 2 muss transponiert werden, dies geschieht mit der Funktion MTRANS. Aber Achtung, diese Funktion ist als Matrixformel mit Strg+Shift+Enter abzuschließen und ist dann von geschweiften Klammern umgeben.

MTRANS integriere ich in SUMMENPRODUKT und schließe diese Funktion dann gesamt als Matrixformel ab. Dann sieht die Formel so aus:
{=SUMMENPRODUKT(A38:A41;MTRANS(C35:F35))}
(Matrixformel: mit Strg+Shift+Enter abschließen)

Welche Variante auch gewählt wird, das Ergebnis ist das gleiche. Details dazu sind in der Beispieldatei unter Beispiel1 aufgeführt.

Beispiel 2: einspaltige Matrix und Konstante

Matrix 1 enthält Nettowerte, Matrix 2 durchgängig den Wert 19%. Die Nettowerte sollen mit den 19% multipliziert, die Produkte dann summiert werden:

Beispiel 2: Matrix 1 und Matrix 2

Beispiel 2: Matrix 1 und Matrix 2

Die Funktion rechnet so:
=SUMMENPRODUKT(A5:A8;B5:B8) = 1.032,08 €

Nun gehe ich einen Schritt weiter und verzichte auf Matrix 2. Stattdessen steht in C19 die Konstante 19%:

Beispiel 2: Matrix und Konstante

Beispiel 2: Matrix und Konstante

Die Formel schreibe ich nun etwas anders. Statt Matrix und Konstante mit “;” zu trennen (dies führt im Ergebnis zu #WERT!, setze ich an diese Stelle “*” und multipliziere so die Matrix mit der Konstante. Schließlich wird die Summe der Produkte errechnet. Das Ergebnis stimmt mit dem vorangegangenen überein:

Beispiel 2: Matrix und Konstante

Beispiel 2: Matrix und Konstante

Beispiel 3: Die häuslichen Wasserkosten

In einer Tabelle sollte jeweils das Ablesedatum und der Zählerstand eingetragen werden. Der Grundpreis / Tag (GPW bzw. GPA) und der Mengenpreis / m³ (MPW bzw. MPA) können je nach Gültigkeit schon vorgetragen sein. Datum und Zählerstand trage ich jeweils zum Ablesezeitpunkt ein:

Beispiel 3: Wasserkosten

Beispiel 3: Wasserkosten

Die Anzahl der Tage zum Ablesedatum 31.01.2013 berechne ich in C8 mit der Formel:
=WENN(A8="";"";A8-A7)

Diese Formel ziehe ich mit dem Ausfüllkästchen bis C19 runter. Die WENN-Abfrage ist notwendig, weil sonst ungewollte Negativwerte in Spalte C ausgewiesen werden. Den Verbrauch zum Ablesedatum 31.01.2013 berechne ich in D8 mit der Formel
=WENN(B8="";"";B8-B7)
und ziehe sie bis D19 runter. Die WENN-Abfrage ist auch hier zur Vermeidung von Negativwerten notwendig.

Die Tabelle ist fertig und es kann ans Rechnen gehen. Als Erstes will ich wissen, welcher Betrag insgesamt für den Grundpreis zu zahlen ist. Die Komponenten zweier einspaltiger Matrizen werden multipliziert und dann addiert.

Also: In Zeile 8 werden 31 Tage mit 0,36 €/Tag multipliziert, in Zeile 9 werden 28 Tage mit 0,36 €/Tag multipliziert usw. bis zu Zeile 19 (31 Tage multipliziert mit 0,36 €/Tag). Die zwölf Produkte werden schließlich summiert. Das ist das Prinzip von SUMMENPRODUKT.
=SUMMENPRODUKT(C8:C19;E8:E19) = 131,40

Genauso verfahre ich mit der Abfrage zum Mengenpreis. Auch hier werden zwei einspaltige Matrizen multipliziert und anschließend addiert:
=SUMMENPRODUKT(D8:D19;F8:F19) = 56,25 €

Ebenso nehme ich die Abfrage zu den Gesamtkosten für Trinkwasser vor:
=SUMMENPRODUKT(C8:D19;E8:F19) = 187,65 €

In dieser Formel habe ich es nun erstmalig mit zwei zweispaltigen Matrizen zu tun, deren Komponenten zuerst multipliziert und dann addiert werden. Die erste Matrix enthält die Werte der Spalten Tage und Verbrauch, die zweite Matrix die der Spalten Grundpreis/Tag und Mengenpreis/m³.

Gerechnet wird so, z.B. in Zeile 8: 31 Tage x 0,36 €/Tag und 4 m³ x 1,25 €/m³. So gehe ich bis zur Zeile 19 vor und addiere schließlich die Einzelergebnisse. Jetzt soll ein nächster Schritt getan werden.

Für die Bereiche, die in den vorangegangenen Formeln die Matrizen bezeichnen, vergebe ich Namen:
C8:C19 Tage
D8:D19 Verbrauch
E8:E19 GrundpreisW
F8:F19 MengenpreisW

Nun rechne ich das Ganze noch einmal mit den namentlichen Angaben:
=SUMMENPRODUKT(Tage;GrundpreisW) = 131,40
=SUMMENPRODUKT(Verbrauch;MengenpreisW) = 56,25
=SUMMENPRODUKT(Tage:Verbrauch;GrundpreisW:MengenpreisW) = 187,65

Beispiel 3: Einsatz von SUMMENPRODUKT

Beispiel 3: Einsatz von SUMMENPRODUKT

Ähnliche Tabellen können auch für Abwasser oder Strom oder Gas aufgebaut und genutzt werden.

Beispiel 4: Kosten eines bestimmten Zeitraums abfragen

Mich interessieren die Kosten für Wasser oder Abwasser oder Strom oder Gas für einen bestimmten Zeitraum, z.B. für das erste Quartal.
Dazu mache ich eine Vorgabe:

Beispiel 4: Zeitraum festlegen

Beispiel 4: Zeitraum festlegen

In den Formeln gebe ich nun die Bedingung ein, dass der Monat aus dem Datum in Spalte A größer/gleich der Vorgabe in C23 und kleiner/gleich der Vorgabe in C24 sein muss.

Die Formel zur Berechnung des Grundpreises verändert sich so:
=SUMMENPRODUKT((MONAT(A8:A19)>=$C$23)*(MONAT(A8:A19)<=$C$24)*Tage*GrundpreisW) = 32,40

Für den Mengenpreis rechne ich:
=SUMMENPRODUKT((MONAT(A8:A19)>=$C$23)*(MONAT(A8:A19)<=$C$24)*Verbrauch*MengenpreisW) = 13,75

Abschließend zu diesem Beispiel noch ein paar Formeln, die zeigen, was SUMMENPRODUKT auch kann. Die Funktion kann Summen berechnen:
=SUMMENPRODUKT(Tage) = 365
=SUMMENPRODUKT(Verbrauch) = 45

Ich will den durchschnittlichen Tagesverbrauch wissen:
=SUMMENPRODUKT(Verbrauch)/SUMMENPRODUKT(Tage) = 0,1233

Jetzt kommt ein Wahrheitswert ins Spiel. Ich will wissen, wie viel Grundpreis ich für die Monate zahle, die mehr als 30 Tage haben:
=SUMMENPRODUKT((Tage>30)*Tage*GrundpreisW) = 78,12

Hier frage ich die Summe des Produktes für nur einen Monat ab, in $F$37 steht: 31.08.2013.
=SUMMENPRODUKT((A8:A19=$F$37)*Tage*GrundpreisW) = 11,16

Zu guter Letzt habe ich die Abfrage von drei Wahrheitswerten abhängig gemacht:

  • das Datum soll größer als der 31.07.2013 sein
  • der Zählerstand soll größer als 8357 sein
  • die Anzahl der Tage soll größer als 30 sein

Beachte bitte, dass das Zeichen “*” “und” bedeutet. “Und” heißt in diesem Beispiel, es werden aus den Matrizen nur die Elemente ausgesucht, auf die obige drei Bedingungen zutreffen und dann multipliziert:
=SUMMENPRODUKT((WERT(A8:A19)>WERT("31.07.2013"))*(B8:B19>8357)*(Tage>30)*Verbrauch*MengenpreisW) = 11,25

(Hinweis: alle diese Formeln findest du auch in der Beispieldatei).

Beispiel 5: Kleiner Exkurs zur Schreibweise von logischen Abfragen

Das Beispiel zeigt zwei Matrizen mit je vier Elementen. Das SUMMENPRODUKT über alles ergibt 403:

Beispiel 5

Beispiel 5

Ich will das Ergebnis wissen, das ich erhalte, wenn ich nur die Werte aus Matrix2 berücksichtige, die größer als 2 sind.

Die erste Formel verwendet ein doppeltes Minuszeichen:
=SUMMENPRODUKT(A5:A8;--(B5:B8>2);B5:B8)

SUMMENPRODUKT mit Bedingungen (Variante 1)

SUMMENPRODUKT mit Bedingungen (Variante 1)

Was bedeutet das? Wenn ich in meiner Excel-Datei =B6>2 schreibe, erhalte ich WAHR:

Arbeiten mit Wahrheitswerten

Arbeiten mit Wahrheitswerten

Damit kann ich nicht rechnen. Das erste Minus macht WAHR zur Zahl -1, das zweite zu +1. Gehe ich so von B5 bis B8 vor erhalte ich zunächst FALSCH;WAHR;WAHR;WAHR, dann 0;1;1;1. Diese Werte multipliziert SUMMENPRODUKT nun mit Matrix1 und Matrix2. Die Summe ergibt 371.

Die zweite Formel ersetzt das doppelte Minuszeichen durch „0+“. Ich erhalte wieder 0;1;1;1. Multipliziert mit den Werten aus beiden Matrizen ergibt die Summe wieder 371:
=SUMMENPRODUKT(A5:A8;0+(B5:B8>2);B5:B8)

In der dritten Formel schreibe ich statt „0+“ nun „1*“. Das Ergebnis ist 371:
=SUMMENPRODUKT(A5:A8;1*(B5:B8>2);B5:B8)

Die vierte Formel setzt statt „1*“ die Funktion N vor B5:B8>2. Die N-Funktion wandelt WAHR in 1 und FALSCH in 0 um. Ich erhalte wieder 371:
=SUMMENPRODUKT(A5:A8;N(B5:B8>2);B5:B8)

Formel 5 und Formel 6 sind fast identisch. Sie unterscheiden sich nur dadurch, dass A5:A8 bzw. B5:B8 einmal ohne und einmal mit Klammern geschrieben werden:
=SUMMENPRODUKT(A5:A8*(B5:B8>2)*B5:B8)
=SUMMENPRODUKT((A5:A8)*(B5:B8>2)*(B5:B8))

Beispiel 5: Alle Varianten

Beispiel 5: Alle Varianten

Beispiel 6: Tanken

Als kostenbewusster Autofahren führe ich genau Buch, wie viel ich wann, wo und zu welchem Literpreis getankt habe. Hier sieht man einen Auszug aus der Beispieldatei:

Beispiel 6: Tanken

Beispiel 6: Tanken

Zuerst interessiert mich, welche Kosten ich in den zehn Kalenderwochen gesamt hatte. Dazu rechne ich:
=SUMMENPRODUKT((E7:E16)*(F7:F16)) = 692,49

Als nächstes interessiert mich, welche Kosten ich ab km-Stand 12474 gehabt habe. Dazu füge ich diese Bedingung in die vorangegangene Formel ein:
=SUMMENPRODUKT((B7:B16>12474)*(E7:E16)*(F7:F16)) = 485,08

Jetzt will ich noch wissen, wie viel Geld ich ab km-Stand 12474 bei der Tankstelle Tankeschön gelassen habe. Auch das ist nicht so schwierig:
=SUMMENPRODUKT((B7:B16>12474)*(D7:D16="Tankeschön")*(E7:E16)*(F7:F16)) = 129,66

Zum Schluss interessiert mich noch der Durchschnittsverbrauch pro 100 km. Dazu verwende ich die Funktion so:
=SUMMENPRODUKT((E7:E16)/(C7:C16)*100)/ANZAHL2(A7:A16) = 6,5

Beispiel 7: Planwerte und Wachstum

Meine Ausgangstabelle zeigt Spalte A Planwerte für z.B. den Umsatz von vier Produkten. Darüber steht mit 1,5% das angestrebte jährliche Wachstum. Die Spalten sind beschriftet mit den folgenden Planjahren und dem kumulierten Wachstum:

Beispiel 7: Planwerte und Wachstum

Beispiel 7: Planwerte und Wachstum

Im Bereich B6:E9 wird nun zeilenweise das Wachstum im Planjahr zum jeweiligen Prozentsatz berechnet.
Mit SUMMENPRODUKT frage ich zunächst ab, um welchen Betrag der Planumsatz bis 2018 insgesamt steigt.
=SUMMENPRODUKT((B5:E5)*$A$10) = 330,0

Will ich das für das Jahr 2016 wissen, schreibe ich B5:C5 usw.
Abfragen kann ich auch, wie hoch der Umsatz bei den angenommenen Wachstumsraten in 2018 sein wird:
=SUMME(A10;SUMMENPRODUKT((A6:A9)*(B5:E5))) = 2.530,0

Will ich das Abfragejahr dynamisch in die Formel einbringen (mit Zelle B26 schaffe ich mir dazu eine Auswahlmöglichkeit), schreibe ich so:
=SUMME(A6:A9;SUMMENPRODUKT((A6:A9)*(B5:INDIREKT(ADRESSE(5;VERGLEICH(B26; A4:E4;0)))))) = 2.530,0

Beispiel 7: Zusammenfassung

Beispiel 7: Zusammenfassung

Fortsetzung folgt…

Über den Autor
Ich heiße Gerhard Pundt, bin 59 Jahre alt, verheiratet und habe Betriebswirtschaft studiert.

Seit ca. 15 Jahren arbeite ich in einem Unternehmen der Wasserver- und Abwasserentsorgung in Mecklenburg-Vorpommern als Controller.

Zu Excel kam ich 1993, bedingt durch die Arbeit. Mein heutiges Wissen in Excel und VBA habe ich mir autodidaktisch durch Lesen, Probieren und Üben angeeignet.

Heute kann ich sagen: Ich weiß mir zu helfen.

Was mache ich sonst noch gern? Ich lese Romane, inzwischen auch digital mit einem Ebook-Reader.

 


So, das war der erste Teil in der Artikelserie zur SUMMENPRODUKT-Funktion in Excel. Ich denke, das sollten wir jetzt erst mal eine Weile sitzen lassen. Und nächste Woche gibt es hier den zweiten Teil mit weiteren interessanten Beispielen und Anwendungsfällen.

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

3 Gedanken zu “SUMMENPRODUKT: Theorie und Praxis (Teil 1)

  • Lars

    Hallo Martin,

    eine Frage zur Funktion “Summe”. Wenn ich mehrere Tabellenblätter, hier als Beispiel Monate, in ein Blatt zusammenaddieren will, dann gebe ich ein: =Summe(Januar:Dezember!A1) ein. Funktioniert auch problemlos. Nur warum funktioniert dieser Befehl nicht, wenn die Tabellenblätter alle nach KW benannt sind? =Summe(KW 1:KW 52!A1) erhalte ich immer eine Fehlerwarnung.

    • Martin Weiß

      Hallo Lars,

      der Grund liegt darin, dass die Namen der Tabellenblätter ein Leerzeichen enthalten. Daher müssen sie in der Formel in einfache Anführungszeichen gesetzt werden:
      =SUMME(‘KW 1:KW 52’!A1)

      Damit funktioniert’s.

      Schöne Grüße,
      Martin