Summe mit mehreren Bedingungen innerhalb eines Felds 20

Eine scheinbar gewöhnliche Aufgabe erfordert doch eine ungewöhnliche Lösung.
 

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

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

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

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

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

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

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

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

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

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.

 

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

20 Gedanken zu “Summe mit mehreren Bedingungen innerhalb eines Felds

  • Avatar-Foto
    Gerhard Pundt

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    BRU

    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.

    • Avatar-Foto
      Martin Weiß

      Hallo BRU,

      ja, SUMMENPRODUKT ist ein wahres Füllhorn, da schlummern viele tolle Dinge unter der Oberfläche 🙂

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Kevin

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Silas

    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)…

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Claudia Raddatz

    Wow, cooler Artikel, der endlich die Funktion Summenprodukt für mich verständlich gemacht hat.
    Herzlichen Dank & viele Grüße
    Claudia

  • Avatar-Foto
    Matthias Rack

    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.

      • Avatar-Foto
        Lukas

        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))))

        • Avatar-Foto
          Martin Weiß

          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

    • Avatar-Foto
      Martin Weiß

      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

    • Avatar-Foto
      Peter

      Diese Variante setzte ich bisher auch immer ein.
      Trotzdem eine tolle Lösung für mich lasten Hasen.
      Vielen lieben Dank??

  • Avatar-Foto
    Andreas

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Alexander

    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

    • Avatar-Foto
      Martin Weiß

      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