Alle Kriterien erfüllt? Bedingte Summen in Excel 38

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

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.



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 zu katana Swords Antworten abbrechen

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

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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        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

  • Avatar-Foto
    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

  • Avatar-Foto
    David

    Kleiner Nachtrag: Ergebnis sollte sein, unter jedem Rohr die entsprechende Klasse automatisch angezeigt zu bekommen.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        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!

        • Avatar-Foto
          Martin Weiß

          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

  • Avatar-Foto
    Gundula

    Vielen Dank für die anschauliche Erklärung. Ich habe folgendes Problem in meiner Stundenabrechnung. Ich brauche die Summe der Stunden in den einzelnen Projekten. In Spalte A stehen die Stunden (die Zellen sind als Uhrzeit formatiert), in Spalte B die Projektnummern (als Text). Meine Formel sieht so aus: =SUMMEWENN(A1:A10;“Projekt1″;B1:B10), die Zelle, in der die Summe stehen soll, ist formatiert als „Benutzerdefiniert [h]:mm“. Als Summe kommt immer 0 raus, woran liegt das?

    Ich würde mich freuen über Hilfe.

    Viele Grüße
    Gundula

    • Avatar-Foto
      Martin Weiß

      Hallo Gundula,

      die Formel und das Benutzerformat sehen für mich gut aus. Es könnte noch an der genauen Schreibweise der Projekte liegen. Sind da noch irgendwo Leerzeichen enthalten? Die Bezeichnung der Projekte in Spalte B muss exakt so sein, wie Du es in der Formel angegeben hast. Ansonsten kann ich auf Anhieb auch keinen Fehler entdecken…

      Schöne Grüße,
      Martin

    • Avatar-Foto
      Detlef

      Wenn Du in der Spalte A die Zeiten und in Spalte B die Projektnummern stehen hast, sollte die richtige Formel folgendermaßen lauten:
      =SUMMEWENN(B1:B10;”Projekt1″;A1:A10)

      Bei Deiner Formel suchst Du in den Zeiten nach der Projektnummer und versuchst dann die dazugehörigen Projektnummern (Text) zu addieren. Daher ist das Ergebnis volkommen zutreffend 0. ;o)

      LG Detlef

  • Avatar-Foto
    Michael Zupp

    Hallo,

    Ich möchte gerne alle Zellen in einem bestimmten Bereich zählen, die einen Hintergrundfarbwert von 50 Haben.
    Die Summe aller Werte in den Zellen die den Hintergrundfarbwert 50 haben ist nicht das Problem, Ich muss die Anzahl wissen.

    Danke

    • Avatar-Foto
      Martin Weiß

      Hallo Michael,

      eine Summen- oder Zählfunktion auf Basis der Hintergrundfarbe ist in Excel standardmäßig nicht vorgesehen. Hierfür bedarf es etwas Makro/VBA-Programmierung.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Stefan Busch

    Guten Tag werte Excel-Experten,

    ich benötige bitte eine Formel für folgendes Problem:
    Ich habe eine Tabelle mit Stunden im Zellenformat [hh]:mm zu addieren, in der Ergebniszelle hier im Beispiel J4 soll aber nur den Wert angezeigt werden der über 10:00 Stunden ist, wenn der Wert 10:00 Stunden oder weniger ist soll die Ergebniszelle leer bleiben.

    z.B.: Zelle D4 hat 09:04 Stunden + Zelle F4 hat 01:01 Stunden = Ergebniszelle J4 00:05 Stunden

    z.B.: Zelle D5 hat 07:11 Stunden + Zelle F5 hat 00:58 Stunden = Ergebniszelle J5 bleibt leer da nur 08:09 Stunden

    Das Wort Stunden soll aber nicht in den Zellen vorkommen, ich habe das Wort Stunden in den Beispielen nur zur besseren Verdeutlichung angefügt.

    Vielen Dank für Eure Hilfe.

    Viele Grüße aus Hannover,
    Stefan Busch

    • Avatar-Foto
      Martin Weiß

      Hallo Stefan,

      Uhrzeiten sind ja nichts anderes als Dezimalzahlen, nur eben in einem anderen Format dargestellt. Jede Zeit entspricht einem Bruchteil von 1: So ist beispielsweise
      12:00 Uhr -> 0,5
      09:00 Uhr -> 0,25
      18:00 Uhr -> 0,75
      10:00 Uhr -> 0,4166
      Du musst also nur mal das Zahlenformat auf „Zahl“ ändern, dann siehst Du das sofort. Und somit kannst Du über eine einfache WENN-Formel das gewünschte Ergebnis erreichen:

      =WENN(D5+F5<0,417;"";D5+F5)

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Joachim Schöffler

    Hallo Martin, danke für die sehr gute Erkärung. Aber leider bin ich wohl unfähig diese für meine Belange umzusetzen. Ich habe eine Tabelle (ziemlich lang) dort stehen in der Spalte B die Lieferanten und in der Spalte C die Summen. Nun möchte ich die Summe für einen einzelnen Lieferanten ermitteln. Aber wie du schreibst sitzt das Problem vor dem Rechner. Wahrscheinlich ist die Lösung sehr einfach, aber ich bekomme es nicht hin. Kannst du oder jemand mir weiter helfen????? Danke scho mal und viele Grüße Joe

    • Avatar-Foto
      Martin Weiß

      Hallo Joachim,

      dafür kannst du die SUMMEWENN-Funktion so nutzen, wie oben im Beispiel 1 beschrieben. Angenommen, die gesuchte Lieferantennummer steht in Zelle A1 und die Tabelle geht von B5:C100, dann würde die Formel so aussehen:
      =SUMMEWENN(B5:B100;A1;C5:C100)
      Wichtig ist immer, dass der Kriterienbereich und der Summenbereich gleich groß angegeben werden. Hier also beispielsweise beide von Zeile 5 bis Zeile 100.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Babsi

    Hallo,

    ich versuche mich gerade an einer Funktion die folgende Kriterien erfüllt:
    Lagerbestandsliste mit Lagerbestand und Abgängen (inkl. Datum). Ich möchte nun heraus finden, wann mein Lagerbestand für die Bestellungen nicht mehr ausreicht.
    Kann ich das mit einer bedingten Formatierung hin kriegen?

    Ich bin schon leicht am verzweifeln…und würde mich über Hilfe sehr freuen

    Viele Grüße Babsi

    • Avatar-Foto
      Martin Weiß

      Hallo Babsi,

      ohne den Aufbau deiner Tabelle zu kennen, wird eine Hilfe etwas schwierig. Kannst du hier vielleicht etwas präziser mit deinen Angaben werden?

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Babsi

        Hallo Martin,

        danke für deine Nachricht. Meine Tabelle wäre folgend aufgebaut:

        Produkt Nächstes Lieferdatum Liefermenge Lagerbestand Nächste Produktion

        In der Spalte „nächste Produktion“ sollte dann das Datum stehen, wenn die Liefermenge (also aufsummiert) den Lagerbestand aufgebraucht hat.

        Viele Grüße Babsi

        • Avatar-Foto
          Martin Weiß

          Hallo Babsi,

          ich nehme an, in der Tabelle werden fortlaufend alle verschiedenen Produkte unsortiert eingetragen, so wie eben das Lieferdatum anfällt. Richtig?
          Das Problem ist nicht so trivial zu lösen und daher kann ich hier nur eine grobe Orientierung geben. Du brauchst irgendwo einen Anfangsbestand pro Produkt als Startwert. Danach kannst du über eine SUMMEWENN-Formel die Summe aller Liefermengen pro Produkt bestimmen und vom jeweiligen Startbestand abziehen. Und dann lässt sich die Zeile bestimmen, bei der der Lagerbestand zum letzten Mal noch positiv für das Produkt ist.

          Zum Beispiel so:
          Lagerbestände

          Aber das ist wirklich eine sehr rudimentäre Lösung, denn es wird nicht berücksichtigt, dass irgendwann die Bestände ja wieder aufgefüllt werden. Mehr kann ich dir an dieser Stelle leider nicht helfen. Aber vielleicht ist das ja zumindest ein kleiner Denkanstoß.

          Schöne Grüße,
          Martin

          • Avatar-Foto
            Babsi

            Super vielen Dank Martin!

            Ich probier es so mal aus. Die Liste wird jede Woche neu angelegt, da sich die Bestellungen ständig ändern, somit sollte es eigentlich klappen.

            Viele Grüße
            Babsi

          • Avatar-Foto
            Babsi

            Hallo Martin,

            nochmal vielen Dank für deine Hilfe. Die erste Formel klappt einwandfrei!

            Bei der zweiten Formel hab ich ein Problem mit dem „MINWENNS“ – da macht mein Excel nicht mit und zeigt mir immer einen Fehler an…Muss ich da noch etwas spezielles beachten?

            Viele Grüße Babsi

          • Avatar-Foto
            Martin Weiß

            Hallo Babsi,

            die MINWENNS-Funktion gibt es erst in Excel 2019 (und Microsoft 365). Wenn du eine andere Version im Einsatz, dann musst du eine andere Variante verwenden.

            Statt
            =WENN(D12=MINWENNS($D$5:$D$17;$A$5:$A$17;A12;$D$5:$D$17;“>0″);B12;““)
            nimmst du
            =WENN(D12=MIN(WENN(($A$5:$A$17=A12)*($D$5:$D$17>0);$D$5:$D$17;““));B12;““)

            Wichtig:
            Die Formel funktioniert nur, wenn du sie als Array-Funktion eingibst und die Eingabe mit Strg+Umschalt+Enter abschließt.

            Schöne Grüße,
            Martin