In Excel ist es manchmal so, wie im richtigen Leben. Etwas sieht ganz einfach aus, stellt sich bei näherer Betrachtung aber also doch nicht so trivial heraus. Der vermeintlich naheliegende Weg kann der falsche sein.
Die gute Nachricht: Meistens gibt es auch für nicht triviale Fragen in Excel ein Lösung.
So auch im heutigen Beispiel, wenn bei der Summierung mehrere Bedingungen innerhalb einer Spalte erfüllt sein müssen.
Und so geht’s:
Das Problem
In einer Tabelle sollen also bedingte Summen gebildet werden. Dazu habe ich folgendes kleine Beispiel vorbereitet:
Ausgangslage: Umsätze nach zwei Kriterien
Gesucht ist die Summe über alle Umsätze für ein bestimmtes Produkt in mehreren Regionen. Die Kriterien habe ich rechts neben der Tabelle in den Spalten F und G eingetragen. Spontan hört sich das nach einem einfachen Fall für SUMMEWENNS an.
Zur Erinnerung:
Die Funktion SUMMEWENNS kann grundsätzlich mit mehreren Kriterien umgehen:
=SUMMEWENNS(Summenbereich;Kriterienbereich1;Kriterium1;Kriterienbereich2;Kriterium2...)
Die naheliegende Lösung wäre also:
=SUMMEWENNS(C2:C21;A2:A21;F2;B2:B21;G2;B2:B21;G3)
Die vermeintliche Lösung mit SUMMEWENNS
Das Problem: Alle genannten Kriterien werden dabei mit einem logischen UND verknüpft. Das heißt, es müssen alle Kriterien gleichzeitig erfüllt sein. Ein Datensatz kann aber immer nur einer Region angehören und nicht zwei verschiedenen. Damit scheidet also die vermeintlich naheliegende Version aus, denn sie liefert fälschlicherweise das Ergebnis 0, wie man im folgenden Bild sieht:
Die Lösung funktioniert leider nicht
Was also tun?
Die Lösung liefert SUMMENPRODUKT
Die SUMMENPRODUKT-Funktion ist ein sehr mächtige, wenn auch nicht immer ganz leicht zu durchschauende Funktion. Vom Grundgedanken her werden damit zwei oder mehr Tabellenbereiche zeilenweise multipliziert und die Einzelergebnisse dann addiert.
=SUMMENPRODUKTE(Array1;Array2;....)
Gerhard Pundt hat hier vor längerer Zeit zwei Gastartikel zu dieser Funktion veröffentlicht und eine ganze Reihe von Einsatzmöglichkeiten aufgezeigt:
SUMMENPRODUKT: Theorie und Praxis (Teil 1) und SUMMENPRODUKT: Theorie und Praxis (Teil 2)
Und auch für das heutige Problem hat SUMMENPRODUKT eine Lösung parat. Denn damit lassen sich nicht nur einfache Multiplikationen zwischen den Spalten durchführen, sondern auch Bedingungen einbauen. So kann SUMMENPRODUKT alternativ zu SUMMEWENNS verwendet werden.
Hätten wir in unserer Aufgabenstellung nur ein Produkt und eine Region zu prüfen, würde die Lösung so aussehen:
=SUMMENPRODUKT(C2:C21*(A2:A21=F2)*(B2:B21=G2))
SUMMENPRODUKT mit einer Region
Das Argument (A2:A21=F2) prüft jede Zelle im genannten Bereich eine Übereinstimmung mit F2 (= Carbonic) und liefert ein logisches WAHR, falls eine Übereinstimmung vorliegt und ansonsten ein logisches FALSCH. Analog vergleicht (B2:B21=G2) die Regionenspalte mit dem Wert in G2 (= Süd) und liefert wieder für jede einzelne Zelle ein WAHR bzw. FALSCH.
Mit WAHR und FALSCH kann aber noch nicht unmittelbar gerechnet werden. Daher werden in der Formel die drei Argumente nicht mit dem üblichen Semikolon getrennt, sondern mit einem Multiplikationszeichen. Und dabei wird aus WAHR die Zahl 1 und aus FALSCH die Zahl 0.
Somit ergeben sich für die ersten Zeilen
20000 * 0 * 0 = 0
20000 * 1 * 1 = 20000
17000 * 0 * 0 = 0
usw.
Im folgenden Bild habe ich zur Veranschaulichung drei Hilfsspalten eingebaut, welche die Vorgehensweise von SUMMENPRODUKT verdeutlichen soll:
Hilfsspalten für bessere Nachvollziehbarkeit
In unserer ursprünglichen Aufgabenstellung wollten wir aber zwei Regionen berücksichtigen. Wenn wir jetzt einfach noch ein Argument anhängen, laufen wir in die gleiche Falle wie mit SUMMEWENNS:
=SUMMENPRODUKT(C2:C21*(A2:A21=F2)*(B2:B21=G2)*(B2:B21=G3))
Falsche Variante: SUMMENPRODUKT mit zwei Regionen
Denn in jeder Zeile unserer Datentabelle steht genau eine Region. Ein Datensatz kann nicht gleichzeitig Region Süd UND Region Nord sein, daher liefert die Berechnung für die zweite Zeile
20000 * 1 * 1 * 0 = 0
ABER:
Verknüpft man die beiden Argumente für die Regionen mit einem Pluszeichen, dann klappt es.
=SUMMENPRODUKT(C2:C21*(A2:A21=F2)*((B2:B21=G2)+(B2:B21=G3)))
Richtige Variante: SUMMENPRODUKT mit zwei Regionen
Während das Multiplikationszeichen in der Konsequenz ein logisches UND bedeutet, wird mit einem Pluszeichen ein logisches ODER daraus. Damit die Logik richtig rechnet, sind die beiden Argument noch mit einem weiteren Klammerpaar umgeben. Die 0- und 1-Werte für die Regionen werden also addiert, was im folgenden Bild in der Hilfsspalte I zu sehen ist:
Die ODER-Verknüpfung mit Hilfsspalten dargestellt
Und so ließe sich das Ganze auch mit mehreren Produkten fortsetzen:
=SUMMENPRODUKT(C2:C21*((A2:A21=F2)+(A2:A21=F3))*((B2:B21=G2)+(B2:B21=G3)))
Zwei Produkte und zwei Regionen
Ich gebe zu, dass die Formel insbesondere am Anfang auf den unbedarften Anwender etwas abschreckend wirkt. Aber wenn man sich Schritt für Schritt vorantastet, verschwindet der anfängliche Schrecken hoffentlich. Und wie man sieht, steckt SUMMENPRODUKT die SUMMEWENNS-Funktion locker in die Tasche. Es lohnt sich also, sich näher mit dieser mächtigen Funktion zu beschäftigen.
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,
mit Deinem Beitrag zeigst Du ein Problem auf, das viele Leser noch gar nicht gesehen haben, und bietest eine feine Lösung an. Danke!
Danke auch für die Erwähnung meiner „uralten“ Gastbeiträge. Da freue ich mich doch sehr.
VG
Gerhard
Hallo Gerhard,
dankeschön für Deinen Kommentar. Und Deine beiden Beiträge zu der Funktion waren ja wirklich umfassend, da wäre es doch schade, wenn sie hier kein Erwähnung fänden.
Schöne Grüße,
Martin
Hallo Martin
Danke für den Tip. ich verwende die SUMMENPRODUKT schon sehr lange aber dass man die so einfach verknüpfen kann, ist mir neu.
Hallo BRU,
ja, SUMMENPRODUKT ist ein wahres Füllhorn, da schlummern viele tolle Dinge unter der Oberfläche 🙂
Schöne Grüße,
Martin
Hallo Martin,
ich wollte dir sagen, dass ich immer wieder begeistert bin über deine Blog-Einträge.
Danke
P.S. Bist du eigentlich auch auf Udemy?
Grüße Kevin
Hallo Kevin,
vielen Dank für dein Feedback, das freut mich sehr!
Nein, auf Udemy bin ich nicht, mich findet man bisher nur hier 😉
Schöne Grüße,
Martin
Man könnte auch mehrere SUMMEWENNS mit plus zusammenhängen um das gleiche zu erreichen.
=SUMMEWENNS($C$2:$C$12;$A$2:$A$12;E2;$B$2:$B$12;F2)+SUMMEWENNS($C$2:$C$12;$A$2:$A$12;E3;$B$2:$B$12;F3)…
Hallo Silas,
ja, mit mehreren SUMMEWENNS kommt man auch zum Ergebnis, die Formeln werden halt nur deutlich länger. Aber sie führen zum gleichen Ziel.
Schöne Grüße,
Martin
Wow, cooler Artikel, der endlich die Funktion Summenprodukt für mich verständlich gemacht hat.
Herzlichen Dank & viele Grüße
Claudia
Hi Claudia,
dankeschön, freut mich zu hören!
Schöne Grüße,
Martin
Hallo, dazu eine Frage.
Wäre nicht die einfachere Variante um das Problem zu lösen die Funktion DBSumme()? Hier kann ich mehrere Kriterien mit logischen Verknüpfungen UND / ODER transparent berechnen. Spricht etwas dagegen?
Für mich sind die DB-Funktionen eine vergessene Art. Wobei aus meiner Sicht diese Funktionsgruppe sehr effizient arbeitet und dabei in Kombination mit formatierten Tabellen auch direkte Ergebnisse bei großen veränderbaren Datenmengen liefern kann.
Das habe ich auch gedacht und das Ergebnis ist das gleiche. Filtern wäre auch möglich.
Ich finde FILTER() in Kombination mit SUMME() hier tatsächlich viel praktischer, weil es a) vielseitiger einsetzbar ist und man zur Not auch andere Werte berechnen kann (Mittelwert, Varianz, Median…). Fürs Protokoll: SUMME(FILTER(C2:C21;(((B2:B21=G1)+(B2:B21=G2))*(A2:A21=F1))))
Hallo Lukas,
ja, deine Variante mit der FILTER-Funktion ist auch sehr elegant. Der einzige Haken ist, dass die Funktion nur in M365 (und Excel 2021) zur Verfügung steht. Anwender von älteren Excel-Versionen müssen darauf leider verzichten.
Schöne Grüße,
Martin
Hallo Matthias,
stimmt, DBSUMME ist auch eine schöne Alternative. Über die DB-Funktionen sollte ich mal einen separaten Artikel schreiben…
Vielen Dank für die Anregung!
Schöne Grüße,
Martin
Diese Variante setzte ich bisher auch immer ein.
Trotzdem eine tolle Lösung für mich lasten Hasen.
Vielen lieben Dank??
Guten Tag,
die aufgezeigte Lösung ist ein guter Ansatz, aus meiner Sicht würde ich sagen, dass es auch mit Index, Vergleich und Summewenns ausgehen würde. Ich werde es mal testen…
Doch ich stehe derzeit vor einem anderen zu lösendem Excel Fall. In einer Spalte mit Datensätzen steht ein (Teil) Datum. Das wiederum nicht einheitlich, z. B. JJJJ?MM, MM?JJJJ, MMM JJJJ, MMMM JJJJ, TT.MM.JJJJ, dann kein Datum, oder zwei Werte (hiervon soll das kleinere gelten)…
ERGEBNIS soll ein Wert im Format JJJJ-MM sein, so kann eine Auswertung aus dem Textfeld der einzelnen Elemente erfolgen. In weiteren Spalten sind Stückzahlen, Einzelpreis, Gesamtpreis zu finden.
Ich bin mit Textteilen, Textverketten, Teil, Suchen, Links, Rechts zu einigen Ergebnissen gekommen. Jedoch ist die Formel bereits auf einem 29″ Monitor 3Zeilig…
Ein Feedback wäre schön, vielen Dank vorab.
Liebe Grüße
Andreas
Hallo Andreas,
wenn die Datumswerte wirklich in so vielen unterschiedlichen Schreibweisen auftauchen, dann gibt es meines Erachtens keine andere Lösung, als über irgendwelche verschachtelten Formeln alle Varianten abzufangen. Und vermutlich wird selbst das zu teilweise falschen Ergebnissen führen, wenn beispielsweise Monate und Tage vertauscht sind (wie bei der englischen/amerikanischen Schreibweise) und beides zu korrekten deutschen Datumsangaben führt.
Tut mir leid, hier habe ich keine guten Tipp. Außer vielleicht mal grundsätzlich zu hinterfragen, wie es zu solchen Einträgen kommen kann…
Schöne Grüße,
Martin
wie kann man die Summewenn Funktion anwenden wenn ich z.b. in spalte A mehrere Datum stehen, in Spalte B stehen dazu z.b. Umsätze
nun möchte ich nur bestimmte daten summieren. wenn ich sage das ich die umsätze der letzten 7 tage möchte kann ich ja mit Date das aktuelle datum setzen. und davon auch 7 Tage zurück rechnen. Somit hab ich ja die zwei werte die ich in der spalte A vergleichen möchte
Ziel:
Summe aus Spalte B = Wenn das Datum aus Spalte A größer gleich dem ersten Datum (datum – 7 Tage ) und kleiner gleich dem letzten Datum (aktuelles Datum) ist
=SUMMEWENNS(B1:B100;A1:A100;“>=“&C1)UND(wenn(B1:B100;A1:A100;“<="&D1))
C1 ist vom aktuelle datum 7 tage zurück
D1 ist das aktuelle Datum
Hallo Alexander,
bezogen auf die Angaben in deinem Beispiel wäre das die benötigte Formel:
=SUMMEWENNS(B1:B100;A1:A100;“>=“&C1;A1:A100;“<="&D1) Schöne Grüße, Martin
Hallo Martin,
ich habe ebenfalls ein Summenprodukt zusammengestellt. Allerdings gibt er mir immer den Fehler „WERT!“ aus.
Ich habe zwei Spalten. Die eine Spalte gibt den Status wieder (Werte erledigt, bleibt oder leere Zelle) und die zweite Spalte beinhaltetr entweder einen € Betrag oder ist leer.
Nun habe ich es so geschrieben:
=SUMMENPRODUKT(G6:G37*((F6:F37=K7)+(F6:F37=K8)))
G6:G37 wäre der Summenbereich (zweite Spalte). F6:F37 wäre die erste Spalte mit dem Status. K7 ist eine leere Zelle und K8 eine Zelle mit dem Inhalt „bleibt“.
Was mache ich falsch?
VG Felix
Hallo Felix,
mit deiner Formel ist alles richtig, ich habe sie gerade bei mir ausprobiert. Ein #WERT!-Fehler tritt bei mir nur auf, wenn eine Zelle in G6:G37 keinen numerischen Wert enthält. Sind also irgendwo Buchstaben im Summenbereich enthalten? Oder nur vermeintlich leere Zellen, die aber tatsächlich vielleicht ein Leerzeichen enthalten?
Schöne Grüße,
Martin