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
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)
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
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
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
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
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.
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.
Pingback: Bedingte Summen in Excel (Teil 2) | Der Tabellen-Experte
Hervorragend.
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
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
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
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
Kleiner Nachtrag: Ergebnis sollte sein, unter jedem Rohr die entsprechende Klasse automatisch angezeigt zu bekommen.
Hallo David,
das war eine ziemlich kniffelige Aufgabe und hat deshalb etwas länger gedauert. Eine mögliche Lösung könnte so aussehen:
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
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
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
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
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
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!
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
as described, thanks
Super! ! ! Expdition rapide et excellent produit. Merci ! !
Expdition rapide! grande chaussure
Beautiful person and fast shipping
A+++ seller with fast shipping! Very pleased!
Beautiful handshoe! Fast ship and packaged well! Thanks!
Quick Ship… Love it!!!!
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
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
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
Sehr guter Punkt, das hatte ich auch übersehen!
Schöne Grüße,
Martin
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
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
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
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
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
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
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
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
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
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:
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
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
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
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
Hallo Martin,
kann ich SUMMEWENN auch so einschränken/ kombinieren, dass er einerseits die bedingte Summe berechnet, ABER nur von den gefilterten/ vom Filter angezeigten Werten?
Sprich: Ich habe eine schöne SUMMEWENN-Funktion geschrieben…, nun wird die Tabelle gefiltert und er zeigt immer noch die bedingte Summe über alle (auch ausgeblendete) Zeilen an 😉 und das will ich natürlich nicht… Gibt es sowas wie eine AGGREGATWENN-Funktion?
Hallo Matt,
ich weiß was du meinst. Eine spezielle Funktion dafür gibt es leider nicht, aber die AGGREGAT-Funktion kann dabei helfen. Du könntest deine SUMMEWENNS-Funktion um eine Bedingung erweitern, die prüft, ob eine Zeile sichtbar ist oder eben nicht. Dazu ergänzt du deine Tabelle um eine Hilfsspalte (nennen wir sie „Sichtbar“), die den Wert 1 für sichtbare und 0 für ausgeblendete Zeilen enthält. Diese Werte 1 oder 0 werden mit Hilfe der AGGREGAT-Funktion berechnet. Ich habe das in einem anderen Zusammenhang im Schritt 3 in diesem Artikel beschrieben:
Dynamik und Komfort: Formeln per Datenschnitt steuern
Damit sollte es funktionieren.
Schöne Grüße,
Martin