Summe mit mehreren Bedingungen innerhalb eines Felds 16

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.

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

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.



Schreibe einen Kommentar zu Kevin Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht.

16 Gedanken zu “Summe mit mehreren Bedingungen innerhalb eines Felds

  • 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

    • Martin Weiß Autor des Beitrags

      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

  • 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.

    • Martin Weiß Autor des Beitrags

      Hallo BRU,

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

      Schöne Grüße,
      Martin

  • 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

    • Martin Weiß Autor des Beitrags

      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

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

    • Martin Weiß Autor des Beitrags

      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

  • Claudia Raddatz

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

  • 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.

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

        • Martin Weiß Autor des Beitrags

          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

    • Martin Weiß Autor des Beitrags

      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