Bedingungen mit SUMIFS kombinieren

Sie wissen bereits, dass SUMIF Werte summiert, die einer einzelnen Regel entsprechen, etwa alle Verkäufe aus der Region Ost. Aber Fragen aus der Praxis sind meist komplexer: Welche Umsätze wurden in der Region Ost im Januar erzielt? Das sind zwei Bedingungen gleichzeitig.

Hier spielt SUMIFS seine Stärke aus. Das angehängte S bedeutet, dass mehrere Kriterien miteinander kombiniert werden können. Eine Zeile wird nur dann zur Summe hinzugefügt, wenn sie jeden von Ihnen vorgegebenen Test erfüllt.

In dieser Lektion lernen Sie die Reihenfolge der Argumente kennen, erstellen Ihre erste Summe mit mehreren Kriterien und vermeiden die klassischen Fehler, die häufig zu Problemen führen.

SUMIFS verwendet eine andere Reihenfolge, als Sie sie vielleicht von SUMIF erwarten. Die zu addierenden Zahlen stehen zuerst; danach wird jede Bedingung als Paar angegeben.

• sum_range — die zu summierenden Werte
• criteria_range1, criteria1 — der erste Test
• criteria_range2, criteria2 — der zweite Test

Sie können bis zu 127 Bedingungen weitere Bereich-und-Kriterien-Paare hinzufügen. Lesen Sie das folgende Muster laut vor: Addiere diesen Bereich, wenn dieser gleich jenem ist und wenn dieser gleich jenem ist.

Stellen Sie sich eine Tabelle vor, in der Spalte A die Region, Spalte B den Monat und Spalte C den Betrag enthält. Sie möchten die Gesamtumsätze für die Region Ost im Januar ermitteln.

Die zu addierenden Beträge stehen in C:C. Die erste Bedingung prüft A:A auf "Ost", die zweite B:B auf "Januar". Eine Zeile wird nur berücksichtigt, wenn beide Bedingungen erfüllt sind.

Dies ist der häufigste Fehler bei SUMIFS. sum_range und jeder criteria_range müssen identische Dimensionen haben — also dieselbe Anzahl von Zeilen und Spalten.

Wenn Ihr sum_range C2:C100 umfasst, ein Kriterienbereich aber A2:A99, gibt die Formel den Fehler #VALUE! zurück, weil die Zeilen nicht übereinstimmen.

Am sichersten ist es, für alle Bereiche dieselben Start- und Endzeilen zu verwenden oder durchgängig ganze Spalten wie A:A einzusetzen.

Die feste Eingabe von "Ost" in Anführungszeichen funktioniert, aber in einem flexiblen Bericht kann die Benutzerin oder der Benutzer die Auswahl selbst treffen. Tragen Sie die Region in Zelle F1 und den Monat in F2 ein und verwenden Sie diese Zellen als Kriterien.

Wenn Sie nun F1 oder F2 ändern, wird die Summe sofort neu berechnet. Beachten Sie, dass eine einfache Zellreferenz keine Anführungszeichen benötigt — Anführungszeichen werden nur für wörtlichen Text verwendet, der innerhalb der Formel eingegeben wird.

Kriterien sind nicht auf exakte Textübereinstimmungen beschränkt. Für Zahlen können Sie Vergleichsoperatoren verwenden, indem Sie sie in Anführungszeichen setzen.

• ">100" — größer als 100
• "<=50" — kleiner oder gleich 50
• "<>0" — ungleich null

Hier summieren Sie die Beträge in der Region Ost, berücksichtigen aber nur Zeilen, deren Betrag selbst größer als 100 ist. Beachten Sie, dass sum_range und ein Kriterienbereich dieselbe Spalte sein können.

Was ist, wenn der Grenzwert in einer Zelle steht, statt direkt eingegeben zu werden? Sie können nicht einfach ">F1" schreiben — damit wird nach dem wörtlichen Text F1 gesucht. Stattdessen verbinden Sie den Operator mit der Zelle, und zwar mit dem Symbol &.

">"&F1 erstellt also das Kriterium größer als der Wert in F1. Dieser Verkettungstrick ist für dynamische, benutzergesteuerte Berichte unverzichtbar.

SUMIFS lässt sich problemlos erweitern. Fügen Sie für jede neue Regel ein weiteres Bereich-und-Kriterien-Paar hinzu. Angenommen, Spalte D enthält die Verkaufsperson. Dann können Sie die Verkäufe aus der Region Ost im Januar summieren, die von "Maria" getätigt wurden.

Jede Bedingung schränkt das Ergebnis weiter ein. Da SUMIFS die UND-Logik verwendet, muss eine Zeile alle drei Tests erfüllen, um in die Summe aufgenommen zu werden.

Textkriterien unterstützen Platzhalter. Das Sternchen * entspricht beliebig vielen Zeichen, das Fragezeichen ? genau einem Zeichen.

• "North*" entspricht North, Northeast und Northwest
• "*east*" entspricht allem, was east enthält

Damit summieren Sie die Beträge für jede Region, die mit "North" beginnt. Das ist besonders praktisch, wenn Ihre Regionsnamen denselben Anfang haben.

Es lohnt sich, den Unterschied auswendig zu lernen, da die Argumentreihenfolge umgekehrt ist:

• SUMIF: range, criteria, [sum_range] — der zu prüfende Bereich steht zuerst; sum_range ist optional und steht am Ende.
• SUMIFS: sum_range, criteria_range1, criteria1, ... — sum_range steht immer an erster Stelle.

Tipp: Sobald Sie mehr als eine Bedingung haben, greifen Sie direkt zu SUMIFS. Viele verwenden SUMIFS auch bei nur einem Kriterium, um ein einheitliches Muster beizubehalten.

Wenn SUMIFS 0 zurückgibt, bedeutet das normalerweise, dass keine Zeile alle Bedingungen erfüllt hat — nicht, dass die Formel fehlerhaft ist. Suchen Sie nach überflüssigen Leerzeichen im Text, abweichender Schreibweise oder einer als Text gespeicherten Zahl.

Eine schnelle Diagnose besteht darin, jeweils eine Bedingung zu entfernen. Wenn die Summe erscheint, sobald Sie ein Kriterium weglassen, war dieses Kriterium dafür verantwortlich, dass keine Zeile berücksichtigt wurde. Mit dieser Methode des Isolierens und Testens lassen sich Formeln mit mehreren Kriterien schnell debuggen.

Testen Sie Ihr Verständnis der Argumentreihenfolge von SUMIFS.

Sie können jetzt Zahlen summieren, die gleichzeitig mehrere Bedingungen erfüllen:

• SUMIFS(sum_range, range1, crit1, range2, crit2, ...) — sum_range steht an erster Stelle.
• Alle Bereiche müssen gleich groß sein, andernfalls erhalten Sie #VALUE!.
• Verwenden Sie ">"&F1, um mit einer Zelle zu vergleichen, sowie * und ? für Platzhalter.
• Bedingungen verwenden die UND-Logik — eine Zeile muss alle Bedingungen erfüllen.

Als Nächstes zählen Sie mit COUNTIFS Zeilen, die mehrere Bedingungen erfüllen.