Dieser Beitrag zeigt 13 Möglichkeiten, die Excel-Funktion SUMMENPRODUKT zu verwenden. Doch denke nicht, dass dies eine erschöpfende Zusammenstellung ist. Es gibt nahezu unzählige weitere Beispiele, wie die Funktion zum Einsatz kommen kann. Vielleicht schreibe ich irgendwann einen neuen Beitrag darüber.
Die Funktion SUMMENPRODUKT gehört zur Kategorie Mathematik und Trigonometrie und reiht sich damit bei den gebräuchlichsten mathematischen Funktionen wie SUMME, SUMMEWENN und SUMMEWENNS ein.
1. SUMMENPRODUKT gemäß Excel-Hilfe
Zum Einstieg zeige ich dir, wie die Funktion standardmäßig eingesetzt wird.
Die Syntax lautet:
SUMMENPRODUKT(Matrix1;Matrix2;Matrix3; …)
wobei Matrix1; Matrix2;… 2 bis 255 Matrizen sind, deren Komponenten zunächst multipliziert und dann addiert werden.
Ein Beispiel dazu: Die nachfolgende Tabelle enthält Flächenangaben in ha und dazugehörige Verbräuche, z.B. Kalidünger, pro ha.
Errechnet werden soll der Gesamtverbrauch für die Gesamtfläche. Dazu nutze ich die Funktion SUMMENPRODUKT:
=SUMMENPRODUKT(B5:B14;C5:C14) =6.323
Wie hat die Funktion gearbeitet?
Rechne z.B. in Spalte D: 27*19 / 40*19 / 29*16 usw. und addiere dann alle Produktwerte aus D5:D14. Als Ergebnis erhältst du ebenfalls 6.323.
Du hast zwei Rechenoperationen durchgeführt. Einmal hast du die Produkte aus Fläche und Verbrauch pro ha gebildet und dann hast du die Produkte addiert. Dazu könntest du auch diese Formel verwenden:
{=SUMME((B5:B14)*(C5:C14))}
Du siehst, das ist eine Matrixformel. Die geschweiften Klammern sind nicht zu schreiben, sondern entstehen, indem du die Formel mit Strg + Shift + Enter abschließt.
SUMMENPRODUKT selbst ist schon eine Matrixformel, auf die geschweiften Klammern kannst du daher verzichten. Schon wegen der fehlenden Klammern ist SUMMENPRODUKT leichter zu händeln.
Es gibt noch eine zweite gebräuchliche Schreibweise, die allerdings nicht in der Excel-Hilfe erläutert wird:
=SUMMENPRODUKT((B5:B14)*(C5:C14)) =6.323
Was ist hier anders?
Die Matrizen werden in Klammern gesetzt und nicht, durch Semikolon getrennt, aufgezählt, sondern auch sichtbar, durch das Zeichen „*“, multipliziert.
2. Die Matrizen durch die Funktion BEREICH.VERSCHIEBEN definieren
Trägst du in die Funktion BEREICH.VERSCHIEBEN nicht nur Bezug, Zeile und Spalte, sondern auch noch Höhe und Breite ein, bestimmst du dadurch eine Matrix. Dazu habe ich bereits in einem früheren Artikel geschrieben.
Diesen Umstand nutze ich im zweiten Beispiel mit SUMMENPRODUKT.
Hier will ich die erste Matrix, also B5:B14 auf diese Weise definieren und das geschieht so:
Als Bezug für BEREICH.VERSCHIEBEN setze ich $B$4 fest, die Zelle, in der „ha“ steht. Von dort aus gehe ich eine Zeile nach unten und 0 Spalten nach rechts. Damit bin ich auf B5. Meine Matrix soll sich von B5 bis B14 ausdehnen, das sind 10 Werte. Als Höhe trage ich daher 10 und als Breite 1 ein, den der Bereich ist nur eine Spalte breit.
Somit lautet die Formel:
=BEREICH.VERSCHIEBEN($B$4;1;0;10;1)
Damit ersetze ich die erste Matrix in meiner Formel SUMMENPRODUKT:
=SUMMENPRODUKT(BEREICH.VERSCHIEBEN($B$4;1;0;10;1);C5:C14)
Das Ergebnis lautet auch hier wieder 6.323.
Ebenso verfahre ich, wenn ich die zweite Schreibweise bevorzuge:
=SUMMENPRODUKT((BEREICH.VERSCHIEBEN($B$4;1;0;10;1))*(C5:C14))
Das Ergebnis ist auch 6.323.
3. Die Matrizen durch Werteaufzählung bestimmen
Bisher habe ich die Matrizen über die Bereiche definiert. Eine Matrix kann aber auch bestimmt werden, indem die Werte, durch Semikolon getrennt, in geschweiften Klammern aufgelistet werden.
Den Bereich B5:B14 ersetze ich durch:
{27;40;29;33;35;43;35;28;36;32}
Eingesetzt in meine SUMMENPRODUKT-Formeln sieht das so aus:
=SUMMENPRODUKT({27;40;29;33;35;43;35;28;36;32};C5:C14)
Das Ergebnis ist keine Überraschung mehr: 6.323.
Verwende ich die zweite Formel-Variante, schreibe ich:
=SUMMENPRODUKT(({27;40;29;33;35;43;35;28;36;32})*(C5:C14))
und erhalte auch 6.323.
4. Die Matrizen namentlich benennen
Bereichen einen Namen zu geben und damit zu operieren, ist eine praktische Sache, die zu mehr Übersicht in langen Formeln führt.
Ich gebe deshalb der Matrix C5:C14 den Namen Verbrauch und setze diesen in die Formeln ein.
=SUMMENPRODUKT(B5:B14;Verbrauch)
bzw.
=SUMMENPRODUKT((B5:B14)*Verbrauch)
Mit beiden Formeln erhalte ich auch hier 6.323.
5. Mit INDIREKT auf einen Matrixnamen zugreifen
Mit der Funktion INDIREKT kann der Bezug auf einen mit Namen benannten Zellbereich zurück gegeben werden.
Ich schreibe den Namen der Matrix C5:C14 in die Zelle E4.
Statt der zweiten Matrix verwende ich in beiden SUMMENPRODUKT-Formeln nun den Ausdruck
=INDIREKT($E$4)
Die erste Formel:
=SUMMENPRODUKT(B5:B14;INDIREKT($E$4))
Die zweite Formel:
=SUMMENPRODUKT((B5:B14)*INDIREKT($E$4))
Ergebnis jeweils 6.323.
Positiv an dieser Variante ist, dass sich der Namen in E4 und damit auch der zugehörige Bereich dynamisch ändern können. Die SUMMENPRODUKT-Formeln greifen immer indirekt auf den Wert in E4 zu.
6. Matrizen aus verschiedenen Dateien
Es kann durchaus vorkommen, dass sich die Flächenmatrix und die Verbrauchsmatrix in verschiedenen Dateien befinden.
Die Formeln habe ich so geschrieben, wobei die Fremddatei ist in diesem Fall geöffnet ist:
=SUMMENPRODUKT(B5:B14;[Lagerdatei.xlsx]Verbräuche!$B$11:$B$20)
bzw.
=SUMMENPRODUKT((B5:B14)*([Lagerdatei.xlsx]Verbräuche!$B$11:$B$20))
Ergebnis jeweils 6.323.
7. Matrix durch INDIREKT und Verkettung definieren
Wie das mit INDIREKT geht, habe ich schon gezeigt. Nun will ich den Bezug, auf den INDIREKT zugreift, noch durch Verkettung erzeugen.
Den Bezug erzeuge ich z.B. in F5 so:
=“C“&ZEILE()&“:C“&ZEILE()+9
Das ergibt: „C5:C14“
Für die SUMMENPRODUKT-Formeln habe ich diese Möglichkeiten:
=SUMMENPRODUKT(B5:B14;INDIREKT($F$5))
und
=SUMMENPRODUKT((B5:B14)*(INDIREKT($F$5)))
In beiden Fällen erhalte ich als Ergebnis natürlich auch 6.323.
8. Flächenmatrix als Summe von jeweils drei Werten (drei verschiedene Flächen)
Bisher waren die gesamten Flächen in einer Matrix abgebildet. Wie aber ist zu verfahren, wenn sich die Werte in B5:B14 als jeweils Summe aus drei Teilflächen darstellen?
Beispielhaft habe ich in folgender Tabelle Werte dargestellt.
Ich könnte jetzt auf die Summenwerte in M5:M14 zugreifen und alles wäre beim Alten. Das will ich aber nicht, sondern jeweils die Bereiche in den Spalten J, K und L nutzen.
Für SUMMENPRODUKT muss ich also eine Matrix definieren, die erst die jeweils drei Werte addiert. So lässt sich das umsetzen:
=SUMMENPRODUKT(((J5:J14)+(K5:K14)+(L5:L14));C5:C14)
bzw.
=SUMMENPRODUKT((J5:L14)*(C5:C14))
In der ersten Formel muss ich die Teilbereiche in Klammern setzen und addieren, in der zweiten Formel nehme ich gleich den Bereich von J5:L14.
Mit beiden Formeln bekomme ich als Ergebnis 6.323.
9. Werte einer Matrix sollen mit „L“ beginnen
Die folgende Tabelle enthält Namen von Kunden, Bestellmengen, Einzelpreise und den Gesamtpreis.
Die farblichen Markierungen beziehen sich auf die Abschnitte 9 und 10.
OK, hier soll der Kundenname mit „L“ beginnen. Das ist nicht so schwierig, ich schreibe die Formel so:
=SUMMENPRODUKT((TEIL(B3:B12;1;1)=“L“)*(C3:C12)*(D3:D12))
und erhalte als Ergebnis 1.161. Die eingeflossenen Werte sind blau markiert, addiert sind demnach die Werte aus Spalte E für die Kunden Lehmann und Liebers.
Probiere das einfach mal aus, indem du die Anfangsbuchstaben „M“ oder „R“ oder andere wählst.
10. Werte einer Matrix sollen bestimmte Buchstaben enthalten
Im Beispiel 9 ging es darum, dass ein bestimmter Buchstabe am Anfang des Kundennamen steht. Hier sollen ein oder mehrere Buchstaben (als Folge) im Kundennamen enthalten sein.
Dazu habe ich drei Beispiele.
Zuerst soll der Buchstabe “a“ an zweiter Stelle im Namen stehen. Die Formel ist ähnlich der aus Beispiel 9.
=SUMMENPRODUKT((TEIL(B3:B12;2;1)=“a“)*(C3:C12)*(D3:D12))
Als Ergebnis erhalte ich so 1.336, das sind die Werte (grün) für die Kunden Bahlmann und Ramlow.
Wichtig ist, dass in der TEIL-Formel das Erste_Zeichen mit 2 benannt wird. Ob der Buchstabe groß oder klein geschrieben wird, ist unerheblich, beides funktioniert. Dennoch neige ich dazu, es auch deutlich zu machen, wenn ich einen kleinen Buchstaben suche.
Im zweiten Fall soll die Zeichenfolge „er“ an den letzten beiden Stellen des Namens stehen.
Dafür bietet sich die Funktion RECHTS an, ich schreibe daher:
=SUMMENPRODUKT((RECHTS(B3:B12;2)=“er“)*(C3:C12)*(D3:D12))
Das Ergebnis lautet 2.461, enthalten sind alle ockerfarben markierten Werte. Dazu gehören demnach die Kunden Müller, Meier, Krüger und Dudenhöfer.
Der dritte Fall zu Fall 10 fordert, dass ein (kleines) „t“ im Kundennamen enthalten sein soll, an welcher Stelle ist egal. Das geht mit dem bisherigen Formelaufbau nicht.
Ich verwende zum Aufsuchen des „t“ die Funktion FINDEN und verlange außerdem, dass das Ergebnis von FINDEN eine Zahl ist. Die Formel sieht so aus:
=SUMMENPRODUKT((ISTZAHL(FINDEN(„t“;B3:B12))*(C3:C12)*(D3:D12)))
Das Ergebnis ist 735 (violett markiert) und gehört zum Kunden Retzke.
Ich gebe zu, die Kombination von ISTZAHL und FINDEN habe ich in anderen Blogs, aber auch in Foren gefunden, sie ist kein Gedankenblitz von mir. Ohne ISTZAHL funktioniert es nicht.
11. Nur Werte eines bestimmten Monats berücksichtigen
Hierzu verwende ich eine Tabelle, die in der ersten Spalte ein Datum und in der zweiten z.B. eine Liefermenge enthält.
Aus der Tabelle soll die Summe der Mengen, die im Monat Januar geliefert wurden, ermittelt werden. Hinter dem Datum soll außerdem eine tatsächliche Menge stehen.
Dafür setze ich in die SUMMENPRODUKT-Formel die Funktionen MONAT und ISTZAHL ein.
=SUMMENPRODUKT((MONAT(B3:B14)=1)*(ISTZAHL(C3:C14))*(C3:C14))
Was passiert?
Der Monat soll 1, bedeutet Januar, sein. ISTZAHL muss das Ergebnis WAHR liefern. Soweit beides zutrifft, werden die Werte aus Spalte C addiert.
Im Beispiel ist der Monat Januar zweifach vertreten, in B4 und B5. In C4 und C5 stehen Zahlen, kein Text. Damit werden 564 und 232 addiert, das Ergebnis ist 796.
12. Dienstagswerte mit SUMMENPRODUKT und TEST
Eine Tabelle enthält Angaben zum Datum, zur Menge und zum Preis pro Mengeneinheit. Eine Möglichkeit, nur Dienstagswerte zu erfassen, ist, die Funktion REST anzuwenden. Als Divisor trage ich die 7 ein, denn es gibt 7 Wochentage.
Eine Möglichkeit, nur Dienstagswerte zu erfassen, ist, die Funktion REST anzuwenden. Als Divisor trage ich die 7 ein, denn es gibt 7 Wochentage.
=SUMMENPRODUKT((REST(A3:A26;7)=3)*(B3:B26)*(C3:C26))
Die Formel ermittelt die blau markierten Zeilen als Dienstagswerte. Menge und Preis/ME werden jeweils multipliziert und die Produkte addiert.
Das Ergebnis lautet 89.588.
13. Dienstagswerte mit SUMMENPRODUKT und TEXT
Für dieses Beispiel greife ich wieder auf die Tabelle aus Beispiel 12 zu und verwende statt REST die Funktion TEXT.
=SUMMENPRODUKT((TEXT(A3:A26;“TTT“)=“Di“)*(B3:B26)*(C3:C26))
Was macht TEXT?
Das Datum 26.01.2016 ist im Format „TTT“ das Kürzel für den Dienstag, „Di“. Die Funktion TEXT wandelt jetzt jeden Datumswert in eben das Wochentagskürzel um. Damit werden im Bereich A3:A26 drei Werte ermittelt, auf die „Di“ zutrifft.
Die Funktion SUMMENPRODUKT multipliziert jetzt nur die Werte aus B3:B26 und C3 und C26, auf die „Di“ zutrifft. Die Produkte werden abschließend addiert.
Das Ergebnis ist hier auch 89.588.
Ich habe nun die Formel noch ein wenig verändert und will nicht „Di“, sondern „Dienstag“ sehen. Dazu verändere ich das Zahlenformat von „TTT“ auf „TTTT“, mehr nicht.
=SUMMENPRODUKT((TEXT(A3:A26;“TTTT“)=“Dienstag“)*(B3:B26)*(C3:C26))
Das Ergebnis ist auch jetzt 89.588.
Zum Abschluss mache ich noch ein Spielchen. Ich lege mir eine Liste mit den Wochentagen „Sonntag“ bis „Samstag“ an und gebe ihr den Namen „Wochentage“. Einem Auswahlfeld in A28 teile ich mit der Datenüberprüfung die Liste „Wochentage“ als zulässig zu.
Ich setze zusätzlich die Funktion VERGLEICH ein, um festzustellen, ob das Datum in A3:A26 im Format „TTTT“ dem Wert im Auswahlfeld in A28, hier auch Dienstag, entspricht.
=SUMMENPRODUKT((VERGLEICH(TEXT(A3:A26;“TTTT“);
Wochentage;0)=VERGLEICH(A28; Wochentage;0))*(B3:B26)*(C3:C26))
Als Ergebnis erhalte ich wieder 89.588.
Zusammenfassung
In diesem Beitrag habe ich 13 Fälle zur Anwendung der Funktion SUMMENPRODUKT konstruiert. Vielleicht findest du Gefallen daran und magst den einen oder anderen für deine Zwecke nutzen.
Abschließend möchte ich dich noch auf einen zweiteiligen Beitrag verweisen, den ich in 2015 zum gleichen Thema für den Blog „Der Tabellenexperte“ schreiben durfte.
Entdecke mehr von Clevercalcul
Melde dich für ein Abonnement an, um die neuesten Beiträge per E-Mail zu erhalten.
Hallo Gerhard,
danke für deinen Kommentar, ich habe dir mal eine Mail geschrieben.
Danke und Grüße Heinrich
Hallo,
ich habe folgende Formel: =SUMMENPRODUKT((MONAT(INC!AC2:INC!AC2167)=A2)*(JAHR(INC!AC2:INC!AC2167)=B2)) In dieser zähle ich in einer anderen Tabelle (INC) das vorkommen, alles gut soweit, nun wollte ich jedoch auch die Berücksichtigung von Filtern in der Tabelle INC einbeziehen und zwar mit folgender Formel: =SUMMENPRODUKT((MONAT(TEILERGEBNIS(3;INC!AC2:INC!AC2176))=A2)*(JAHR(TEILERGEBNIS(3;INC!AC2:INC!AC2176))=B2)) Leider ergibt diese 0, nun stehe ich etwas auf dem Schlauch
Vielen dank vorab und Grüße
Hallo Heinrich,
SUMMENPRODUKT arbeitet nach der Syntax:
SUMMENPRODUKT(Array1;Array2;…)
MONAT, JAHR sind nicht array-fähig, deshalb wirst Du zu keinem Ergebnis kommen.
Versuche es doch mal mit der Funktion SUMMEWENNS oder sende mir eine Beispieldatei, so kann ich schlecht Dein Vorhaben nachvollziehen.
Viele Grüße
Gerhard
Hallo Gerhard,
danke für deinen Artikel. Ich habe eine konkrete Frage zur Verwendung von SUMMENPRODUKT bei Matrizzen aus mehreren Dateien:
In deinem Beispiel unter Punkt 6 zeigst du die Formel: =SUMMENPRODUKT((B5:B14)*([Lagerdatei.xlsx]Verbräuche!$B$11:$B$20))
Ich habe eine ähnliche Formel, nur dass in den Zellen, auf die im letzten Teil verwiesen wird ([Lagerdatei.xlsx]Verbräuche!$B$11:$B$20) keine fixen Werte stehen, sondern Verweise auf andere Tabellen enthalten sind.
Anscheinend durch diese verweise funktioniert SUMMENPRODUKT nicht, dann wenn ich die Verweise durch feste Werte ersetze funktioniert es. Aber ich bin auf die Verweise angewiesen. Hast du vielleicht einen Tip für mich, wie ich mein Problem lösen kann?
Danke im Voraus,
Susanne
Hallo Susanne,
danke für Deinen Kommentar. Ich habe versucht, Deine Frage nachzuvollziehen und in meiner Lagerdatei SVERWEISE eingebaut. SUMMENPRODUKT rechnet auch dann mit den „VERWEIS-Werten“.
Kannst Du mir vielleicht Deine Formel genau aufschreiben und mir die Art der Verweise kurz erklären? Gerne auch als E-Mail (siehe Blog/Kontakt).
Grüße Gerhard
Ok. Danke für die Info! Ja das lustige ist eigentlich das mit Openoffice und Libreoffice das funktioniert und bei Excel nicht. Die Kalenderwoche allein als Befehl auch funktioniert aber in solchen „Schleifen“ klappt das dann überhaupt nicht..
Hallo Gerhard, Ich habe hier eine Tabelle im Einsatz mit einer Datumsspalte und einer Menge. Ich würde gerne für die Kalenderwoche jeweils eine Summe der Mengen errechnen. In Libreoffice sieht die Formel bei mir so aus: =SUMMENPRODUKT(KALENDERWOCHE(F2:F399)=41;H2:H399). Leider funktioniert diese Formel im Excel nicht. Wenn ich das ganze mit Monat eintrage -> =SUMMENPRODUKT(MONAT(F2:F399)=11;H2:H399) funktioniert das. Was mache ich da genau falsch? Grüsse, Jan
Hallo Jan,
bei mir funktionieren Deine Formeln beide nicht. Mein Vorschlag: Lasse in einer Hilfsspalte K jeweils die Kalenderwoche berechnen. Rechne mit dieser Formel:
=SUMMEWENN(K2:K399;11;H2:H399)
Zur Verwendung von KALENDERWOCHE ist das Internet voll mit Ideen, aber nichts funktioniert
Grüße Gerhard
Hallo Gerhard,
ich danke Dir für Deine Hilfe. Ich habe mich nun genau nach Deinen Vorgaben gehalten aber leider funktioniert es trotzdem nicht. Nun wird mir zwar der Wert 0 angezeigt, aber in dem angegebenen Bereich habe ich 3x ein Januar-Datum eingetragen. Ich bin echt am verzweifeln…..
Hallo yve,
das kann nicht sein, ich habe es gerade noch einmal geprüft.
Wenn die Datumswerte im Blatt Tabelle1 (bzw. Bezeichnung aus deiner Datei) in D4:D8000 stehen, schreibst du im Blatt Tabelle2 z.B. in B4 die Formel
=SUMMENPRODUKT((MONAT(Tabelle1!D$4:D$8000)=$E$6)*1)
In $E$6 steht die Monatszahl, 1 für Januar oder 2 für Februar usw.
Gern kannst du mir auch eine Beispieldatei senden, die Mailadresse findest du im Impressum des Blogs.
Grüße
Gerhard
Hupps, bei so vielen Informationen kommt man etwas durcheinander 😉
Im Tabellenblatt 1 Spalte B sind die Daten drin die ich für die Auswertung benötige.
Also zum Beispiel 03.01.2018; 05.02.2018 etc.
Ich möchte gerne im Tabellenblatt 2 angezeigt bekommen, welche Anzahl von welchem Monat (Jan, Feb, Mrz etc) in Spalte B aus Tabellenblatt 1 vorhanden ist. Die genannte Formel greift auch, aber nicht für den Monat Januar.
Ich habe in der Formel 7996 Zellen markiert und diese Anzahl wird mir dann angezeigt wenn ich die Formel
=SUMMENPRODUKT(–(MONAT(‚WS v. KD da‘!D$4:D$8000)=1)) nutze. Wenn ich aber statt der 1 die 2 für Februar einsetze zeigt Excel mir die korrekte Anzahl an. Aber wie bekomme ich es hin, dass Excel mir die richtige Anzahl für Januar anzeigt und nicht die markierten Zellen?
Ich hoffe ich habe es einigermaßen verständlich erklären können ^^
LG Yve
Hallo yve,
genau das wollte ich wissen. Hier eine Lösung:
Schreibe in Tabelle2 z.B. in E6 die Zahl für den Monat, nach dem du suchst. Also 2 für Februar oder 8 für August.
Schreibe z.B. in B4 nun diese Formel:
=SUMMENPRODUKT((MONAT(Tabelle1!B8:B22)=$E$6)*1)
Die Bereichsangabe B8:B22 ist natürlich mein Beispiel, passe das bitte an.
Übrigens beschäftigt sich ein Kurzbeitrag in meiner Blogkategorie Excel-Minis (erscheint am 28.11.2017) genau mit diesem Thema.
Ich hoffe, ich konnte dir helfen.
Grüße
Gerhard
Hallo Michael,
ich bin gerade an der Gestaltung einer Statistik für 2018, demnach sind die entsprechenden Zellen leer.
Als Formel nutze ich
=SUMMENPRODUKT((MONAT(‚TABELLENBLATT!D$4D$8000)=1))
Wenn ich Daten in das Tabellenblatt (also Februar bis Dezember) zeigt Excel mir aktuell „0“ an, was ja auch korrekt ist, aber für Januar zeigt Excel mir alle für die Formel markierten Zellen an. Was mach ich falsch?
Bin nicht ganz so fit in Excel ^^
Hallo yve,
ich bin Gerhard, der Autor des Beitrags, deshalb antworte ich mal zuerst.
Deine Formel ist mir nicht ganz klar. Beschreibe doch bitte mal kurz, in welchen Bereichen eines Tabellenblattes sich Zahlen befinden und was du mit der Formel ausrechnen willst. Dann kann ich sicher auch antworten.
Bis dahin
Gerhard
Hallo Gerhard,
natürlich steht da nicht „abc“, aber ohne jetzt allzu sehr ins Detail zu gehen und die komplette Tabelle zu posten: Es steht in der Spalte P z.B. schon mal „(1:24)“ in Klammern – und zwar deshalb, weil diese Flugzeit aus bestimmten Gründen und ganz bewusst dem Kunden nicht berechnet (aber trotzdem „irgendwie“ und rein informativ angezeigt) werden soll. Oder es sind ganze Textzeilen als Kommentar in der ein gesamtes Jahr umfassenden Tabelle vorhanden.
SUMME hat damit kein Problem, SUMMENPRODUKT aber offenbar schon. Das ist aber bisher nirgends klar erwähnt worden.
Mit dem Zahlenformat [hh]:mm hat das nix zu tun, das kenne und verwende ich seit Jahren (wenn nicht Jahrzehnten). 😉
Hallo Michael,
ich danke dir für die ergänzenden Angaben. Auch wenn ich mich intensiv mit Excel beschäftige, kann ich nicht alle Fälle kennen, die auftreten können.
Besser wäre es aus meiner Sicht, wenn die Spalte P wirklich nur den richtigen Zeiten vorbehalten bleibt. Hinweise auf Nichtberechnung oder Kommentare lassen sich doch sicher in gesonderten Spalten unterbringen. Dann macht auch SUMMENPRODUKT keine Probleme.
Ich habe also hier und heute keine Lösung für dich.
Grüße
Gerhard
P.S. zu meinem letzten Beitrag: Ich sehe gerade, dass die Anführungszeichen in typographische umgewandelt wurden. Gemeint sind natürlich die „“ (hoffe, die werden jetzt nicht auch wieder konvertiert).
Die eigentliche Formel ist etwas komplexer, die oben ist die verkürzte Form (führt aber zum selben Ergebnis). Hier noch die derzeit vollständige:
=SUMMENPRODUKT(($M$1:$M72={„CJ2″.“XLS“})*(NICHT(ISTFEHLER(ZEITWERT(TEXT($P$1:$P72;“[h]:mm;@“)))))*(NICHT(ISTFORMEL($P$1:$P72)))*($P$1:$P72))
Aber auch das ergibt #WERT! (bzw. in der anderen Variante ein mit der Anzahl der zutreffenden Zellen multipliziertes Ergebnis).
Ich befinde mich im Urlaub, habe nur ein Smartphone dabei. Sorry, da kann ich momentan schlecht helfen. Schau mir das später mal an.
Hallo Gerhard,
danke erstmal für die Hilfe. Du schriebst:
[Zitat]
ich habe weiter nachgedacht. So sollte es funktionieren:
=SUMMENPRODUKT(($M$1:$M72={„CJ2″.“XLS“})*($P$1:$P72))
Alles im Zahlenformat [hh]:mm
[Zitatende]
Das ist aber doch exakt dieselbe Formel, die ich bereits in meinem Beitrag vom 2. September 2017 um 0:30 erwähnt hatte (und die #WERT! ergibt):
[Zitat]
Ich versuche Flugzeiten in Spalte P zu addieren, die in einem Teil der Spalte M den Text „CJ2“ oder „XLS“ enthalten. Also schreibe ich:
=SUMMENPRODUKT(($M$1:$M72={„CJ2″.“XLS“})*($P$1:$P72))
Die Syntax habe ich so aus irgendeinem Forenbeitrag entnommen. Das ergibt jedoch #WERT! Das verstehe ich schon nicht.
[Zitatende]
Das mit dem Zahlenformat [hh]:mm ist mir natürlich geläufig.
Einen Grund für das Problem habe ich aber mittlerweile gefunden: SUMMENPRODUKT funktioniert offenbar nur dann, wenn der zu summierende Spaltenbereich – in diesem Beispiel also ($P$1:$P72) – ausschließlich numerische Werte enthält. Befinden sich in einzelnen Zellen des Bereichs keine numerischen Werte (sondern z.B. Text), dann ergibt SUMMENPRODUKT eben #WERT!, statt den nicht numerischen Wert einfach zu ignorieren (wie es SUMME tut).
Leider habe ich also noch keine Lösung.
Hallo Michael,
ja sicher ist es die gleiche Formel, denn sie funktioniert doch, wenn das Zahlenformat [hh]:mm ist und wenn Flugzeiten auch als Zahl in der Spalte P stehen.
Markiere doch einfach P1:P72 und setze das richtige Zahlenformat. Eine Flugzeit wird doch nicht mit „abc“ o.ä. angegeben sein. Übrigens rechnet SUMMENPRODUKT auch mit Zahleneinträgen im Textformat, nur nicht mit Buchstaben.
Grüße
Gerhard
Vielen Dank für diesen hilfreichen Blogeintrag. Trotzdem komme ich nicht ganz klar:
Ich versuche Flugzeiten in Spalte P zu addieren, die in einem Teil der Spalte M den Text „CJ2“ oder „XLS“ enthalten. Also schreibe ich:
=SUMMENPRODUKT(($M$1:$M72={„CJ2″.“XLS“})*($P$1:$P72))
Die Syntax habe ich so aus irgendeinem Forenbeitrag entnommen. Das ergibt jedoch #WERT! Das verstehe ich schon nicht.
Schreibe ich es hingegen so:
=SUMMENPRODUKT(($M$1:$M72={„CJ2″.“XLS“})*(SUMME($P$1:$P72)))
Dann ergibt es zwar einen Wert, aber der wird mit 5 multipliziert (weil die angegebenen Kriterien in $M$1:$M72 halt auf 5 Zellen zutreffen). Das erscheint mir zwar eigentlich logisch, aber sämtliche Forenbeiträge (und auch dieser Blog) behaupten, es würden nur die Werte in $P$1:$P72 addiert, jedoch nicht mit mit der Anzahl der zutreffenden Zellen multipliziert werden.
Was mache ich falsch und wie mache ich es richtig? Excel 2013, übrigens.
Hallo Michael,
eine Lösung für deine Formel habe ich im Moment nicht.
Vielleicht magst du es mit dieser Formel versuchen, das funktioniert:
=SUMMEWENN(M1:M72;“XLS“;P1:P72)+SUMMEWENN(M1:M72;“CJ2″;P1:P72)
Wenn du Zeiten im Zahlenformat hh:mm verwendest, funktioniert es nicht wirklich. Nehme das Zahlenformat [hh]:mm.
Grüße
Gerhard
Hallo Michael,
ich habe weiter nachgedacht. So sollte es funktionieren:
=SUMMENPRODUKT(($M$1:$M72={„CJ2″.“XLS“})*($P$1:$P72))
Alles im Zahlenformat [hh]:mm
Grüße
Gerhard
Hallo Gerhard,
vielen Dank für deine Mühe und die schnelle Antwort.
Inzwischen habe ich den Fehler gefunden. Es lag an „N“&ZEILE(3:1000).
Nachdem ich daraus „N“&ZEILE($N$3:$N$1000) gemacht habe, hat alles funktioniert.
Schöne Grüße
Roland
Hallo Roland,
statt SUMME geht es auch mit
(TEILERGEBNIS(9;N$3:N1000))
TEILERGEBNIS scheint dir ja wichtig zu sein.
Grueße
Gerhard
Hallo Thomas,
vielleicht kannst du mir helfen:
In meiner Datei verwende ich unter anderem folgende Formel:
=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT(„N“&ZEILE(3:1000)))*(H3:H1000=“PP/AmEx“)*(N3:N1000″“))
Sie bezieht sich auf Daten in einer dynamischen Tabelle mit z.Zt. 63 Zeilen und das Ergebnis ist korrekt.
Sobald ich aber eine weiter Zeile in der Tabelle hinzufüge (z.B. Zeile 64) sieht die Formel so aus:
=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT(„N“&ZEILE(3:1000)))*(H3:H1001=“PP/AmEx“)*(N3:N1001″“))
und das Ergebnis ist #NV.
Was mach ich falsch? Herzlichen dank für deine Hilfe.
Schöne Grüße
Roland
Hallo Roland,
ich denke, dass du mich, Gerhard, als den Autor meinst.
Versuche es statt mit TEILERGEBNIS doch nur mit SUMME, etwa so:
=SUMMENPRODUKT((SUMME(N$3:N1000))*(H$3:H1000=“PP/AmEx“)*(N$3:N1000))
Bei mir hat es so funktioniert.
Viele Grueße
Gerhard
Das Problem war, dass im WP Kommentarfeld die < > Kleiner als und Größer als als HTML Code verstanden wurde…
Es ging darum dass die Werte nur gezählt werden sollen wenn der Wert größer oder kleiner 0 ist.
Konkret wäre die Formel dann
=RUNDEN(SUMME(F4:F32)/SUMMENPRODUKT((F4:F32<>0)*1);-2)
Ich habe größer als und kleiner als einmal in der Formel als != ersetzt 🙂
=RUNDEN(SUMME(F4:F32)/SUMMENPRODUKT((F4:F32!=0)*1);-2)
Das ist gut, kannte ich noch gar nicht.
Hallo!
Mit Summenprodukt funktioniert die Durchschnittsberechnung (ohne Nullwerte) natürlich auch, ich würde aber an dieser Stelle die kürzere Funktion
=MITTELWERTWENN(F4:F32;“0″;F4:F32)
nutzen.
Hallo Thomas,
ich danke für deinen Kommentar. Selbst würde ich auch MITTELWERTWENN bevorzugen, zumal die Funktion mit „Mittelwert“ schon einen Hinweis zum Ergebnis gibt. Aber SUMMENPRODUKT geht eben auch.
Grüße
Gerhard
Ich glaube, dass ich am Wochenende, mit Bezug auf deinen Beitrag, ein konkretes Beispiel veröffentlichen werde… Eigentlich war da ja das Thema Mindmapping angedacht, aber das hat zum Glück noch ein wenig Zeit um im Entwurf zu bleiben 🙂
Viele Grüße
Andreas
Hallo Gerhardt,
du hast mir gerade für eine Durchschnittswertberechnung eine enorme Zeitersparnis geschenkt.
In den Zellen F4:F32 habe ich einzelne Werte (Teilweise 0) und möchte nun einen Durchschnitt bilden Summe / Anzahl Werte 0….
Dank dir bin ich nun auf folgende Formel für die Anzahl 0 gekommen und mag da einfach Danke sagen 🙂
=SUMMENPRODUKT((F4:F320)*1)
Viele Grüße
Andreas
Hallo Andreas,
das freut mich, dass ich einen bescheidenen Beitrag zur Lösung deines Problems leisten konnte.
Danke für den Kommentar.
Mit =MITTELWERTWENN(F4:F32;“0″;F4:F32)
sollte es aber auch gehen.
Viele Grüße
Gerhard
Hallo Andreas,
entschuldige, da habe ich Unsinn geschrieben, weil Problem falsch gedeutet.
Statt SUMMENPRODUKT kannst du auch
ZÄHLENWENN(F4:F32;0)
nehmen.
Viele Grüße
Gerhard
Leider auch nicht… 🙂 Das Tückische ist ja, dass ich die 0 Werte gerade nicht zählen mag. Es sollen nur Werte gezählt werden die > 0 sind 🙂
Ich danke ganz herzlich für den Link.
Du hast ein schönes Beispiel gefunden, den Warenkorb, gefällt mir.
Gefallen tun mir auch die speziellen Begriffe aus Österreich:
Erdäpfel = Kartoffeln
Paradeiser = Tomaten
Danke für den Beitrag!
Zwei Seelen, ein Gedanke … hab grad meinen Blogpost für morgen fertiggeschrieben … SUMMENPRODUKT. Den hab ich sofort um einen Link hierher erweitert.
Sind nämlich wirklich coole Beispiele! Vor allem die vielen Möglichkeiten, Matrizen anzugeben, beflügeln die Phantasie 🙂 Und Wahrheitswerte-mit-SUMMENPRODUKT sind ohnehin mein heimlicher Liebling unter den Excel-Möglichkeiten 🙂
Vielen Dank Katharina für Deinen Kommentar, freut mich, dass Du aus dem Beitrag etwas mitnimmst. Ich freue mich auf Deinen Beitrag morgen.
Gerhard