In der letzten Woche hat an dieser Stelle mein Gastautor Gerhard Pundt sich intensiv mit der SUMMENPRODUKT-Funktion auseinandergesetzt.
Da diese Funktion unglaublich vielseitig in ihrer Anwendung ist, gehen wir heute in die zweite Runde. Gerhard zeigt wieder an sehr praxisnahen Beispielen, wofür man diese Excel-Funktion einsetzen kann.
Viel Spaß beim Lesen und ausprobieren!
Die Excel-Datei mit allen vorgestellten Beispielen kann übrigens hier heruntergeladen werden.
Beispiel 8: Mittwochswerte
Das Beispiel zeigt eine Liste mit den Tagesangaben eines Monats und die Tagesumsätze. Für eine Analyse werden die Gesamtumsätze gesucht, wenn der Tag Mittwoch war:
Beispiel 8: Mittwochswerte
Dazu wird zusätzlich die Funktion REST benutzt. Wenn das Ergebnis von z.B.
=REST(A7;7) gleich 4 ist, handelt es sich um einen Mittwoch. Die Wochentage werden in Excel 2007 ab Sonntag = 1 gezählt.
Diese Abfrage baue ich nun in die SUMMENPRODUKT-Formel ein:
=SUMMENPRODUKT((REST(A5:A23;7)=4)*(B5:B23)) = 62.227,00
Beispiel 9: Saatgutbedarf
Ein Landwirt plant, bestimmte Flächen mit Gerste zu bestellen. Dazu will er die Menge Saatgut wissen, die er beschaffen, transportieren und zwischenlagern muss. Nachfolgende Tabelle (die Werte sind rein fiktiv und frei erdacht) gibt eine Übersicht über die geplanten Flächen und den einzelnen Saatgutbedarf:
Beispiel 9: Saatgut
Die gesamte Saatgutmenge errechnet sich so:
=SUMMENPRODUKT(B5:B10;D5:D10) = 572
Der Landwirt will nun wissen, wie viel Saatgut er für Gerste benötigt und welche Kosten er damit hat.
Menge:
=SUMMENPRODUKT((C5:C10="Gerste")*(B5:B10)*(D5:D10)) = 190
Kosten:
=SUMMENPRODUKT((C5:C10="Gerste")*(B5:B10)*(D5:D10)*(E5:E10)) = 25.460,00
Schließlich will er wissen, welche Kosten er auf Flur 240/1 oder auf Flur 210/1 hat. Das „oder“ in der Abfrage wird mit dem Zeichen „+“ umgesetzt:
=SUMMENPRODUKT(((A5:A10="240/1")+(A5:A10="210/1"))*(B5:B10)*(D5:D10)*(E5:E10)) = 37.056,00
Beispiel 10: Fuhrbetrieb
Der Inhaber eines Fuhrbetriebes will wissen, mit welcher Durchschnittsgeschwindigkeit seine Fahrer die Pisten befahren. Dazu führt er eine Tabelle mit den Angaben zu Fahrer, Fahrzeug, Fahr-km pro Tour und die Fahrzeit pro Tour:
Beispiel 10: Fuhrbetrieb
Er rechnet nun mit der Funktion SUMMENPRODUKT die Durchschnittsgeschwindigkeit für den Fahrer Müller:
=SUMMENPRODUKT((A5:A14="Müller")*(((C5:C14)/D5:D14)/ZÄHLENWENN(A5:A14;"Müller"))) = 77,5 (km/h)
Ebenso interessant ist, wie schnell z.B. LKW2 so bewegt wird:
=SUMMENPRODUKT((B5:B14="LKW2")*(((C5:C14)/D5:D14)/ZÄHLENWENN(B5:B14;"LKW2"))) = 65,4 (km/h)
Zuletzt will der Inhaber noch wissen, wie viel km die Fahrer Müller oder Baumann mit LKW1 gefahren sind:
=SUMMENPRODUKT((B5:B14="LKW1")*((A5:A14="Müller")+(A5:A14="Baumann"))*(C5:C14)) = 1.360 (km)
Der Fahrer Heinze ist zwar auch mit LKW1 gefahren, wird in dieser Abfrage aber ausgelassen. Hier haben wir es mit einer Oder-Abfrage zu tun, Müller oder Baumann. Die Oder-Abfrage ist am Zeichen „+“ zu erkennen.
Beispiel 11: Kreditzinsen
In diesem Beispiel betrachte ich einen Kredit in Höhe von 24.000 €, der nach zwei Jahren in gleichen Tilgungsraten zurückgezahlt sein soll. Die Zinsen betragen 5% pro Jahr. Einen Ausschnitt aus dem Zins- und Tilgungsplan für 2014 kann man hier sehen:
Beispiel 11: Kreditzinsen
Nun könnte ich die folgenden Fragen natürlich auch auf andere Art und Weise beantworten, aber ich will mir die Antworten mit SUMMENPRODUKT geben.
Als erstes frage ich, wie viel Zinsen im Jahr 2014 nach dem 30.04.2014 zu zahlen sind:
=SUMMENPRODUKT((A10:A21>WERT("30.04.2014"))*(B10:B21)) = 550,00
Zu dieser Abfrage müssen allerdings die Zinsen in der Tabelle schon berechnet sein.
Wenn ich annehme, dass die Zinsen noch nicht berechnet sind, muss ich sie im ersten Schritt berechnen und dann die Frage beantworten, wie viel Zinsen nach dem 30.04.2014 zu zahlen sind.
Das alles mit SUMMENPRODUKT? Das geht:
=SUMMENPRODUKT((A10:A21>WERT("30.04.2014"))*(D9:D20)*($C$5/12)) = 550,00
Die Matrix B10:B21 wird in dieser Berechnung erzeugt, bleibt aber unsichtbar. Lösche die Inhalte von B10:B21 einfach mal, Du wirst sehen, die Formel rechnet trotzdem richtig. Jetzt aber nicht vergessen, die Löschung wieder rückgängig zu machen.
Abschließend frage ich mich noch, wie viel Zinsen nach dem 30.04.2014 bis zum 30.11.2014 gezahlt werden müssen. Dazu erweitere ich die vorangegangene Formel ein wenig:
=SUMMENPRODUKT((A10:A21>WERT("30.04.2014"))*((A10:A21<=WERT("30.11.2014"))*(D9:D20)*($C$5/12))) = 495,83
Prima, das wäre auch geklärt.
Beispiel 12: Kapitalwert
Der Kapitalwert ist das Ergebnis einer dynamischen Investitionsrechnung. Eine solche Rechnung erfolgt, um zu beurteilen, ob eine Investition etwas für den Investor abwirft oder nicht. Man kann auch fragen: Ist die Investition wirtschaftlich?
Excel bietet zur Berechnung des Kapitalwertes die Funktion NBW, Nettobarwert, an. Die Funktion ist in der Kategorie Finanzmathematik zu finden. Hier wird sie nicht weiter erläutert, denn das ist eine andere Geschichte. Nur so viel, die Überschüsse werden mit dem Abzinsfaktor 1/(1+q)n multipliziert und die Ergebnisse (Produkte) addiert.
Ich will sehen, ob auch SUMMENPRODUKT zum gleichen Ergebnis kommt:
Beispiel 12: Kapitalwert
NBW habe ich in E11 nur zur Kontrolle berechnet.
=SUMMENPRODUKT((D6:D9)*((1/(1+$E$5)^(A6:A9)))) = 11,73
Prima, SUMMENPRODUKT hat auch den Kapitalwert errechnet. Das Ergebnis stimmt mit dem von NBW überein. Was bedeutet das Ergebnis?
Ziemlich einfach: Da das Ergebnis bei angenommenen 4% Zinsen positiv ist, ist von einer wirtschaftlichen Investition auszugehen. Probiere es in der Beispieldatei mal mit anderen Zinssätzen. Wird der Kapitalwert negativ, dann lasse die Investition lieber sein.
Beispiel 13: Teilstrings
In Beispiel 10 habe ich ganze Worte in die Abfrage eingebunden, nimm die Werte, wenn der Fahrer „Müller“ heißt. Jetzt will ich nur den Teil einer Artikelbezeichnung verwenden.
Die Tabelle zeigt ein paar Artikelbezeichnungen und den Tagesbedarf in den Werken:
Beispiel 13: Teilstrings
Mich interessiert, wie viel Schrauben mit dem Teilstring „NAV“ benötigt werden. In der Funktion SUMMEWENN würde ich das Suchkriterium mit „*NAV*“ beschreiben. So funktioniert das mit SUMMENPRODUKT nicht. Das Ergebnis ist 0:
=SUMMENPRODUKT((A5:A12="*NAV*")*(C5:C12)) = 0
Warum das so nicht geht, habe ich nicht ergründen können. Ich besinne mich aber auf die Textfunktionen von Excel und schreibe:
=SUMMENPRODUKT((TEIL(A5:A12;10;3)="NAV")*(C5:C12)) = 31.190
Jetzt habe ich das richtige Ergebnis. Diese Variante ist allerdings nur bedingt brauchbar, weil ich mich auf das erste Zeichen an 10. Stelle festlegen muss. Eine bessere Variante ist diese:
=SUMMENPRODUKT((ISTZAHL(FINDEN("NAV";A5:A12)))*(C5:C12)) = 31.190
Zum Schluss frage ich noch nach dem Tagesbedarf an Schrauben „NAV“ im Werk 2:
=SUMMENPRODUKT((ISTZAHL(FINDEN("NAV";A5:A12)))*(B5:B12=2)*(C5:C12)) = 10.279
Beispiel 14: Sonstiges
Bisher bin ich davon ausgegangen, dass mit SUMMENPRODUKT zunächst multipliziert werden muss. Nun habe ich mal probiert, ob auch andere Grundrechenarten mit der Funktion umsetzbar sind:
Beispiel 14: Sonstiges
Summe der Produkte:
=SUMMENPRODUKT((A5:A9)*(B5:B9)) = 6.584
Summe der Quotienten:
=SUMMENPRODUKT((B5:B9)/(A5:A9)) = 12,194
Summe der Summen:
=SUMMENPRODUKT((A5:A9)+(B5:B9)) = 395
Summe der Differenzen:
=SUMMENPRODUKT((B5:B9)-(A5:A9)) = 161
Summe der Potenzen (hier mit Exponent 3):
=SUMMENPRODUKT((A5:A9)^3) = 70.983
Weiteres sollte probiert werden.
Die vorangegangenen Beispiele zeigen, wie universell SUMMENPRODUKT verwendet werden kann.
Mein Fazit:
SUMMENPRODUKT sollte verwendet werden, wenn es tatsächlich etwas zu multiplizieren und anschließend zu summieren gibt. Braucht man nur die Summe von Matrizen, kann man SUMME nutzen. Braucht man nur die Summe unter Bedingungen, kommt man auch mit SUMMEWENN oder SUMMEWENNS zum Ziel. Die verschiedenen Funktionen machen Excel doch erst interessant und spannend.
Ganz sicher aber ist auch mit diesem Artikel noch nicht das letzte Wort über diese Funktion gesprochen.
Ü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.
Das war der zweite und letzte Teil der Artikelserie zur Excel-Funktion SUMMENPRODUKT. Ich bin sicher, du wirst die eine oder andere Anregung gefunden haben, diese Funktion auch in deinen Excel-Alltag sinnvoll zu integrieren.
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.
Hallo Martin!
Erst einmal herzlichen Dank für all die interessanten Beiträge, die Du auf Deiner Seite veröffentlichst.
Mittlerweile bin ich zwar schon Rentner, beschäftige mich aber immer noch gern mit Excel und sammle weiterhin Tipps und Anregungen.
Meine Frage: Ist damit zu rechnen, dass Deine Artikel für längere Zeit im Netz abrufbar sind, oder sollte ich sie mir besser abspeichern?
mfg
Manfred
Hallo Manfred,
freut mich, dass Dir die Artikel gefallen. Auch wenn man niemals nie sagen sollte und keiner in die Zukunft blicken kann: Ich kann Dir zumindest zum heutigen Zeitpunkt versichern, dass ich keinerlei Pläne habe, die Artikel vom Netz zu nehmen. Und das soll sich auch in absehbarer Zeit nicht ändern.
Schöne Grüße,
Martin
Hallo Martin,
natürlich weiß ich, dass das hier kein Forum ist. Vielleicht ist die Fragestellung interessant genug, um darüber nachzudenken.
Angenommen in der ersten Zeile (ab Feld 2) stehen die Namen von Sportlern. In der der ersten Spalte (ab Feld 2) stehen verschiedene Disziplinen. In jeder Zeile wird die Platzierung eingetragen. Ähnlich einem Medaillen-Spiegel lässt sich schnell ermitteln, wer wie oft auf Platz 1, 2 oder 3 war.
Je Spalte (im Beispiel für C) lassen sich die Ergebnisse anzeigen mit:
=(COUNTIF(C2:C10;“=1″))&“•“&(COUNTIF(C2:C10;“=2″))&“•“&(COUNTIF(C2:C10;“=3″)) – Ja, tut mir leid, ich habe eine englische Version.
bspw: 3•1•0
Wie aber kann hier ein Rang ermittelt werden, ohne Zwischenwerte schreiben zu müssen? Das heißt, wer die meisten 1. Plätze belegte, ist auch in Summe Bester, bei Gleichstand entscheidet die Anzahl der Zweitplatzierung …
Für SummenProdukt kein Fall, oder sehe ich nur den Ansatz nicht?
Danke und beste Grüße
Fred
Hallo Fred,
eine interessante Aufgabe, für die ich spontan auch keine Lösung habe. Vielleicht fällt ja einem anderen Leser etwas dazu ein.
Schöne Grüße,
Martin
Hallo Manfred
Ich kann bestätigen, dass auch nach 2469 Tagen der Artikel noch abrufbar ist 😉
Ich verwende die SUMMENPRODUKT-Funktion in meiner Buchhaltungstabelle und habe in der Formel vier Bedingungen (Kategorie, Unterkategorie, Saldo, Konto) eingebaut.
Da die Matrix nur 255 Werte kann, bin ich nun mit Erreichen der 1’020sten Zeile am Limit der Funktion angekommen. Weitere Zeilen werden nicht einberechnet.
Abgesehen von einer Teilung der Tabelle (habe nach Konten getrennt), gibt es noch einen anderen Trick, den ich ausprobieren könnte?
Viele Grüsse, Tanja
Hallo Tanja,
ich bin gerade etwas irritiert hinsichtlich der 255 Werte und der 1020 Zeilen. Was genau meinst du damit? Mir ist kein Limit von 1020 Zeilen bekannt. Beziehen sich die 255 Werte auf die Anzahl der Argumente in deiner Formel? Falls ja, dann sollte es definitiv eine Alternative für solch ein Formelmonster geben 😉
Vielleicht kannst du ja mal die Formel wenigstens auszugsweise in die Kommentare schreiben, damit ich mir ein besseres Bild machen kann.
Schöne Grüße,
Martin
Hallo Martin,
kann man die Funktion eigentlich auch umdrehen und als PRODUKTSUMME nutzen?
Es sollen also nicht die Spalten multipliziert und dann die Zellen aufsummiert werden sondern genau andersrum.
Zuerst die Spalten summieren und dann diese summen „aufmultiplizieren“.
Vielen Dank!
Albertas
Hallo Albertas,
nein, das geht mit SUMMENPRODUKT nicht. Aber du kannst das Ergebnis ja einfach so berechnen:
=SUMME(Spalte1)*SUMME(Spalte2)
Schöne Grüße,
Martin
Hallo Martin,
eigentlich könnte man bei vielen der gezeigten Beispiele auch SUMME verwenden, denn auch da kann ich Bedingungen für Matrizen einbauen, oder?
z.B.: SUMMENPRODUKT((A10:A21>WERT(„30.04.2014“))*(B10:B21)) = SUMME((A10:A21>WERT(„30.04.2014“))*(B10:B21))
Hallo Christof,
jein. Wenn man Microsoft 365 im Einsatz hat, dann funktioniert das.
Bei den älteren Excel-Versionen klappt das jedoch nur, wenn die SUMME-Funktion als Matrix-Formel eingegeben wird. Das heißt, man muss sie zwingend mit Strg+Umschalt+Eingabe abschließen. Ansonsten erhält man einen #WERT!-Fehler.
Mit SUMMENPRODUKT umgeht man das Problem, das funktioniert auch in älteren Versionen ohne diese spezielle Eingabe.
Schöne Grüße,
Martin
Danke für die rasche Antwort!
Hatte schon die Vermutung, dass es damit zusammenhängen könnte.
Aber die Formel sieht jetzt schon sehr elegant aus 😉
Schönes langes Wochenende!
Christof