Alle Kriterien erfüllt? Bedingte Summen in Excel 21

Zur Ermittlung von Summen, die bestimmten Kriterien entsprechen sollen, gibt es mehrere Möglichkeiten in Excel.
 

DIE Standardfunktion in Excel, die jeder kennt, ist die SUMME-Funktion. Was macht man aber, wenn man nicht einfach nur sämtliche Werte einer Liste summieren möchte, sondern nur solche, die eine bestimmte Bedingung erfüllen?

Und was, wenn es sich nicht nur um eine Bedingung handelt, sondern gleich um mehrere?

Folgende Funktionen sehen wir uns dazu an:

  • SUMMEWENN
  • SUMMEWENNS
  • DBSUMME

Und so geht’s:

Wer die nachfolgenden Funktionen direkt und ohne viel Tipp-Arbeit ausprobieren möchte, kann sich die Beispiel-Datei hier herunterladen.

SUMMEWENN

Wenn wir eine Liste aufsummieren wollen, für die eine einzelne Bedingung gelten soll, verwenden wird die SUMMEWENN-Funktion.
=SUMMEWENN(Bereich;Kriterium;[Summenbereich])

Das Kriterium muss dabei immer in Anführungszeichen gesetzt werden. Den Summenbereich muss ich nur angeben, wenn er sich von meinem ersten Bereich unterscheidet. Wenn ich also die Summe für eine andere Spalte bilden möchte, als die Spalte, auf die sich mein Kriterium bezieht.

Beispiel 1:

Ich habe eine Liste aller Länder dieser Erde und deren Einwohnerzahlen. Möchte ich nun wissen, wieviele Einwohner in Afrika leben, lautet die SUMMEWENN-Funktion wie folgt:

Bedingte Summe mit SUMMEWENN

Bedingte Summe mit SUMMEWENN

Um die Formeln etwas leichter nachvollziehbar zu machen, habe ich für die einzelnen Spalten entsprechend ihrer Überschriften aussagefähige Namen vergeben:

  • A7:A213 = Land
  • B7:B213 = Kontinent
  • C7:C213 = Einwohner

Beispiel 2:

Möchte ich die Summe aller Einwohnerzahlen von Ländern mit weniger als 10.000 Einwohnern bilden, lautet die Formel so:

Bedingte Summe (Beispiel 2)

Bedingte Summe (Beispiel 2)

Da sich das Kriterium auf die gleiche Spalte bezieht, wie die zu summierenden Zahlen, kann ich mir den optionalen Summenbereich in der Funktion sparen.

SUMMEWENNS

Sollen für meine Summierung jedoch mehr als eine Bedingung gelten, verwende ich stattdessen die Funktion SUMMEWENNS.

=SUMMEWENNS(Summenbereich;Kriterienbereich1;Kriterium1;[Kriterienbereich2];[Kriterium2]...)

Nun möchte ich alle Einwohner Afrikas berechnen, die in einem Land leben, welches mit dem Buchstaben M beginnt (ja, das ist nicht sehr geistreich, aber damit habe ich zwei Kriterien):

Bedingte Summe mit zwei Kriterien

Bedingte Summe mit zwei Kriterien

Auch hier müssen die einzelnen Kriterien wieder in Anführungszeichen gesetzt werden.

Jetzt kommt’s noch geistreicher: Wie zuvor, nur kommt zusätzlich als drittes Kriterium die Beschränkung auf Länder mit mehr als 1 Mio. Einwohnern dazu:

SUMMEWENNS mit drei Kriterien

SUMMEWENNS mit drei Kriterien

Das Prinzip dürfte jetzt klar sein.

DBSUMME

Eine andere und sehr elegante Option ist die Anwendung der Datenbankfunktion DBSUMME. Diese Möglichkeit bietet sich immer dann an, wenn die Kriterien öfter mal wechseln sollen.

=DBSUMME(Datenbank;Datenbankfeld;Kriterienbereich)

Zur Veranschaulichung habe ich das gleiche Beispiel wie oben nochmal mit der DBSUMME-Funktion nachgestellt:

Bedingte Summe mit DBSUMME

Bedingte Summe mit DBSUMME

Wie man hier sieht, gibt es einen zusätzlichen Kriterienbereich, in dem ich einfach meine Suchkriterien eintippen kann. Die Überschriften müssen dabei mit den Feldnamen der Datenbank identisch sein.

Was man außerdem über den Kriterienbereich wissen muss:
Alle Kriterien, die sich in der gleichen Zeile befinden, werden mit einem logischen UND verknüpft. Kriterien in unterschiedlichen Zeilen werden mit einem logischen ODER verknüpft.

Beispiel:
Berechne die Einwohnerzahl aller Länder, die in Afrika liegen, mit “M” beginnen UND mehr als 1 Mio. Einwohner haben
ODER
die zu Australien und Ozeanien gehören (unabhängig von der Einwohnerzahl)

DBSUMME mit verschiedenen Kriterien

DBSUMME mit verschiedenen Kriterien

Wie man sieht, gibt es verschiedene Möglichkeiten, bedingte Summen zu berechnen. Insbesondere wenn es darauf ankommt, Kriterien häufig und flexibel zu wechseln ohne ständig die Formeln anpassen zu müssen, bietet sich die Datenbankfunktion DBSUMME an.

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.



Kommentar erstellen

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

21 Gedanken zu “Alle Kriterien erfüllt? Bedingte Summen in Excel

  • Ein_in_Excel_Verlaufener

    Ich habe folgendes Problem, kann man das mit einer der oben genannten Lösungen lösen?

    Ich habe eine Tabelle mit Behälternummer und Inhalt in KG (meist 10 Behälter)
    1 20
    2 23
    3 19
    4 24
    5 14
    6 13

    Nun will ich diese Behälter so in 2 Gruppen aufteilen, dass deren Inhalt die Bedingung: Summe der Behälterinhalte x ist >48 kg und <59 kg für beide Gruppen erfüllt. Ferner möchte ich gern wissen, welche Behälter Excel zuammengemischt hat.
    Ist die Aufgabe nicht lösbar, so kann gerne die Meldung "ohne Behälter zu halbieren nicht möglich" ausgegeben werden.

    Für eine Lösung wäre ich sehr dankbar.

    Vielen herlichen Dank für Ihre Zeit und Mühe.

    Eine wunderschöne Woche wünscht
    Ein_in_Excel_Verlaufener

    • Martin Weiß Beitragsautor

      Hallo lieber Ein_in_Excel_Verlaufener,

      ich glaube, ich habe Ihre Aufgabenstellung noch nicht richtig verstanden. Nochmal mit meinen Worten:
      – Aus einer Liste mit 10 Behältern sollen immer genau zwei Gruppen gebildet werden
      – Dabei soll jede der beiden Gruppen in der Summe zwischen 48 und 59 kg liegen
      – Die Behälter dürfen dazu beliebig kombiniert werden
      – Wenn nur eine oder gar keine Gruppe die Bedingung erfüllt, soll eine Meldung ausgegeben werden
      – Zusätzlich soll noch je Gruppe eine Liste der verwendeten Behälter angezeigt werden

      Ist das so richtig?

      Tut mir leid, aber dafür kenne ich keine Lösung. Vielleicht hat ja einer meiner Leser eine Idee.

      Schöne Grüße,
      Martin

      • Ein_in_Excel_Verlaufener

        Ja fast genauso.
        Am Ende brauche ich nur die Info von Excel, welche Behälter ich kombinieren kann, sodass die Bedingung erfüllt ist. Oder eben die Aussage, keine Kombination erfüllt die Bedingung.

        Mein bisheriger Ansatz geht darauf hinaus, dass ich wohl alle Kombinatonsmöglichkeiten der 10 Behälter aufschreiben muss und dann über wenn Funktionen auslesen kann. Allerdings müsste, in dem Fall, dass es mal einen Behälter mehr gibt, gleich alles neu geschrieben werden, da diese Vriante nicht flexibel ist.

        Gruß
        Ein_in_Excel_Verlaufener

  • David

    Hallo, super Erklärung. Nun habe ich aber folgendes Problem:

    Ich habe eine Reihe von Rohren (z.B. 5 Stück) mit unterschiedlichem Durchmesser und unterschiedlichen Wanddicken.

    Außerdem habe ich (z.B. 3) Klassen, denen ich diese Rohre zuordnen muss, z.B. Klasse A, Klasse B, Klasse C. Die Zuordnung erfolgt tabellarisch über die drei Kriterien: [Durchmesser], [Wanddicke] und [Durchmesser mal Wanddicke].
    Die Zuordnung ist nicht linear, sondern quasi willkürlich, z.B.:

    Klasse A: Rohre mit Durchmesser 25 bis 100mm und Wanddicke größer 3mm oder Rohre mit Durchmesser über 100mm, Wanddicke kleiner 3mm, sofern Durchmesser mal Wanddicke kleiner als 1000

    Klasse B: Rohre mit Durchmesser 100 bis 500mm und Wanddicken größer als 3mm, sofern Durchmesser mal Wanddicke kleiner als 3000.

    Unsw.
    Kann ich das mit Excel umsetzen, ohne ca. 2 DIN A4-Seiten mit WENN-Formeln vollzuschreiben?

    Danke im Voraus

    • Martin Weiß Beitragsautor

      Hallo David,

      das war eine ziemlich kniffelige Aufgabe und hat deshalb etwas länger gedauert. Eine mögliche Lösung könnte so aussehen:
      Kommentar-David

      Oben steht die Referenztabelle, in der die verschiedenen Kriterien für die Klassen definiert sind. Unten dann drei Beispiele, für die in Spalte E die jeweilige Klasse ermittelt wird.

      Schöne Grüße,
      Martin

  • G.J.D.

    Super und vielmals Danke für die anschauliche Erklärung. Hätte bitte eine Frage:
    Gibt es so was wie “bedingte Produkte” auch in Exel? Ich versuche folgende bedingte Multiplikation für eine Exel-Zelle, z.B. A3, als Exel-Rechenanweisung zu verfassen, scheitere aber ständig.
    Wenn (A1+A2) > = 4125, dann A3 = (4125-A2)*0,155,
    wenn (A1+A2) < 4125, dann A3 = A1*0,155.
    Könnten Sie bitte einem lernenden Greenhorn einen Tipp oder gar Lösung geben!
    Bin sehr dankbar für jede Hilfe. Besten Dabk im Voraus

    • Martin Weiß Beitragsautor

      Hallo G.J.D.

      Sie haben die Antwort auf Ihre Frage fast schon in “Excel-Deutsch” formuliert 🙂
      Die Lösung gibt die WENN-Funktion:
      =WENN(Bedingung;Dann;Ansonsten)

      In Ihrem Beispiel schreiben Sie in Zelle A3 folgende Formel:
      =WENN((A1+A2)>=4125;(4125-A2)*0,155;A1*0,155)

      Schöne Grüße,
      Martin

  • Michael

    Guten Abend,
    ich habe mir heute einige von den Beispielen für die Bedingten Summen heruntergeladen.
    In einigen Dateien kommt es zu einer Fehlermeldung:
    Der Eintrag =_xlfn.FORMULATEXT(C2) führt zu der Fehlermeldung “Name”
    Wo könnte das Problem liegen?
    Gruß
    Michael

    • Martin Weiß Beitragsautor

      Hallo Michael,

      das liegt ziemlich sicher daran, dass in der Datei Formeln bzw. Funktionen enthalten sind, die in Deiner Excel-Version noch nicht vorhanden sind. Mit welcher Excel-Version arbeitest Du denn?

      Schöne Grüße,
      Martin

      • Michael

        Hallo Martin,
        vielen Dank für Deine schnelle Antwort.
        Ich verwende Office 2010. Für eine Funktion ist die Syntax schon sehr eigentümlich.
        Über Google konnte ich leider auch nichts in Erfahrung bringen.
        Vielen Dank für Deine Mühe!

        Viele Grüße aus Berlin
        Michael

        P.S: Deine Beispiele sind wirklich hervorragend. Mit viel Fachverstand & Herzblut gemacht. Mir gefällt vor allem die gelungene Darstellung!

        • Martin Weiß Beitragsautor

          Hallo Michael,

          vielen Dank für das Lob! Die Syntax ist in der Tat eigentümlich, aber wie gesagt, das ist meines Wissens nach die “natürliche” Reaktion von Excel auf eine Funktion, die nur in neueren Versionen existiert und daher nicht richtig interpretiert werden kann.

          Schöne Grüße,
          Martin