Die Funktion SUMIFS() in Excel: Daten mit mehreren Kriterien summieren

Die Funktion SUMIFS() in Excel addiert Zahlen, die auf mehreren Bedingungen basieren, um uns eine genaue Kontrolle über die Berechnungen zu geben. Im Gegensatz zu SUMIF(), das eine Bedingung prüft, behandelt SUMIFS() mehrere Bedingungen auf einmal.

In diesem Artikel zeige ich dir, wie du SUMIFS() mit fortgeschrittenen Beispielen und Tipps zur Fehlerbehebung nutzen kannst. Am Ende wirst du wissen, wie du SUMIFS() nutzen kannst, um deine Berechnungen genauer und effizienter zu machen.

Die schnelle Antwort

So verwendest du die Funktion SUMIFS() in Excel:

• Gib =SUMIFS( ein, um die Funktion zu starten.

• Wähle den Bereich aus, der die zu summierenden Werte enthält.

• Wähle den Bereich aus, auf den die Bedingung angewendet werden soll.

• Gib die Bedingung ein, die erfüllt werden soll.

• Schließe die Klammern und drücke Enter.

Hier habe ich alle Verkäufe zusammengezählt, die größer als 200 sind.

=SUMIFS(B2:B8, B2:B8, ">200")

Summiere die Werte mit der Funktion SUMIFS(). Bild vom Autor.

Die Funktion SUMIFS() verstehen

Die Funktion SUMIFS() in Excel summiert Werte nur, wenn alle angegebenen Bedingungen erfüllt sind. Sie unterstützt logische Operatoren wie größer als >, kleiner als <, gleich = und ungleich <> sowie Platzhalterzeichen für Teiltreffer. Die Funktion funktioniert mit Zahlen, Text und Datum.

Syntax von SUMIFS()

Die Funktion SUMIFS() definiert zunächst den Wertebereich, der summiert werden soll, gefolgt von Paaren von Bedingungsbereichen und Kriterien. Beachte, dass SUMIFS() mehr als eine logische Bedingung verarbeiten kann.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Hier:

• sum_range ist der Bereich der Zellen, die du hinzufügen möchtest.

• criteria_range1 ist der erste Bereich, in dem wir auf eine Bedingung prüfen.

• criteria1 ist die Bedingung, die für criteria_range1 gilt.

• [criteria_range2, criteria2], … sind optional. Du kannst weitere Paare von Bereichen und Bedingungen hinzufügen. Jedes Paar fügt eine weitere Bedingung hinzu, die erfüllt werden muss.

Beachte, dass die criteria_range (der Bereich, in dem die Bedingungen geprüft werden) die gleiche Anzahl an Zeilen und Spalten haben muss wie die sum_range (der Bereich, der summiert wird).

SUMIFS()-Beispiel

Lass uns ein einfaches Beispiel ausprobieren. Ich habe einen Datensatz von Früchten mit ihren Mengen und Verkäufen. Und ich möchte die Gesamtverkäufe von Äpfeln finden, bei denen die Menge größer als 5 ist. Meine Formel würde also lauten:

=SUMIFS(C2:C8, A2:A8, "Apples", B2:B8, ">5")

Hier ist C2:C8 der Wertebereich, über den summiert werden soll (Sales ($)). A2:A8, "Apples" ist die Bedingung, die nur Zeilen einschließt, in denen das Produkt Äpfel ist. Und B2:B8, ">5" ist die Bedingung, um nur Zeilen einzuschließen, in denen die Menge größer als 5 ist.

SUMIFS()-Formel, um die Verkäufe zu einer Bedingung zu summieren. Bild vom Autor.

SUMIF() vs. SUMIFS()

Der Hauptunterschied zwischen SUMIF() und SUMIFS() ist die Anzahl der Bedingungen, die sie verwenden. Die SUMIF() behandelt eine einzige Bedingung. Aber SUMIFS() kann mit mehreren Bedingungen umgehen. 

Anwendungsfälle für SUMIFS() in Excel

Nachdem du nun weißt, wie die Funktion SUMIFS() funktioniert, wollen wir uns einige fortgeschrittene Fälle ansehen, in denen wir sie in der realen Welt einsetzen können.

Verwendung von SUMIFS() mit verschiedenen Datentypen

SUMIFS() kann mit verschiedenen Datentypen wie Zahlen, Text und Datum arbeiten. Nehmen wir an, ich habe einen Umsatzbericht, aber in einigen Zeilen fehlen Kundennamen. Und ich möchte nur die zusammenfassen, bei denen wir den Namen des Kunden haben. Dafür verwende ich die folgende Formel:

=SUMIFS(C2:C5, A2:A5, "<>")

Diese Formel betrachtet die Spalte " Kunde", sucht nach nicht leeren Zellen und summiert dann die entsprechenden Umsätze.

Fasse alle nicht leeren Zellen mit der Funktion SUMIFS() zusammen. Bild vom Autor.

Um die Verkäufe zusammenzufassen, bei denen die Kundennamen fehlen, verwende ich diese Formel:  

=SUMIFS(C2:C10, A2:A10, "", B2:B10, "")

Addiere die leeren Zellen mit der Funktion SUMIFS(). Bild vom Autor.

Anstatt die Bedingung fest zu kodieren, können wir sie in einer anderen Zelle speichern. Wenn ich die Umsätze von Sarah Lee summieren möchte, gebe ich sie in Zelle E3 ein und verwende diese Formel:

=SUMIFS(C2:C5, A2:A5, E3)

Immer wenn sich der Wert in E3 ändert, wird die Summe automatisch aktualisiert. 

Verwende SUMIFS(), um die Werte unter Verwendung des Zellbezugs zu summieren. Bild vom Autor.

Anwendung der OR-Logik in SUMIFS()

Wir wissen, dass SUMIFS() nur die Logik von AND verwendet, was bedeutet, dass alle Bedingungen, die wir setzen, wahr sein müssen, um die Werte zu addieren. Wenn wir jedoch die OR Logik verwenden wollen, die Werte auch dann addiert, wenn eine von mehreren Bedingungen erfüllt ist, können wir dies tun, indem wir mehrere SUMIFS() kombinieren.

Wenn ich zum Beispiel die Umsätze für John Doe ODER Drake White zusammenfassen möchte, kombiniere ich zwei SUMIFS() Formeln wie diese:

=SUMIFS(C2:C8, A2:A8, "John Doe") + SUMIFS(C2:C8, A2:A8, "Drake White")

Da Drake White nicht auf der Liste ist, ergibt dieser Teil der Formel 0. Die Summe enthält nur die Verkäufe für John Doe, was zeigt, wie die Logik von OR funktioniert.

Verwende die ODER-Logik in der Funktion SUMIFS(). Bild vom Autor.

In Excel 365 oder höher können wir dies mit einer Array-Formel vereinfachen, die das gleiche Ergebnis liefert.

=SUMIFS(C2:C8,A2:A8,{"John Doe","Drake White"})

Verwende die Array-Formel SUMIFS(). Bild vom Autor.

SUMIFS() mit benannten Bereichen

Wir können auch die benannten Bereiche anstelle von Zellbezügen wie A2:A5 oder C2:C5 verwenden. In diesem Beispiel wähle ich A2:A5 und nenne es Customer_list und wähle C2:C5 und nenne es Sales_list. Jetzt um den Gesamtumsatz von John, verwende ich diese Formel:

=SUMIFS(Sales_list, Customer_list, "John")

Summiere die Werte mit SUMIFS() unter Verwendung der genannten Bereiche. Bild vom Autor.

Verwendung von Platzhaltern für Teilabgleiche

Wildcards sind Sonderzeichen (* oder ?), die unbekannte oder variable Zeichen bei der Textsuche und beim Mustervergleich darstellen: 

• * passt auf eine beliebige Anzahl von Zeichen (einschließlich Null).

• ? entspricht genau einem Zeichen.

So funktionieren sie: 

• A* passt auf alle Zellen, die mit A beginnen.

• *A passt auf alle Zellen, die mit A enden.

• *A* passt auf alle Zellen, die irgendwo A enthalten.

SUMIFS() Funktion bietet die Möglichkeit, Text mit Hilfe von Platzhaltern zu finden. Sie identifiziert Werte, die mit bestimmten Textmustern beginnen, enden oder diese enthalten. Nehmen wir an, ich möchte die Umsätze für den Produktcode, der mit ABC beginnt, summieren. In diesem Fall sieht meine Formel wie folgt aus:

=SUMIFS(D2:D8, C2:C8, "ABC*")

Um die Verkäufe zusammenzufassen, die mit 00 enden, schreibe ich:

=SUMIFS(B2:B7, A2:A7, "*00")

Verwende Wildcards in SUMIFS(). Bild vom Autor.

Summierung auf Basis von Datumsbereichen

SUMIFS() kann auch Werte innerhalb eines bestimmten Datumsbereichs summieren. Wenn ich alle Verkäufe ab Januar 2023 wissen will, verwende ich die folgende Formel: 

=SUMIFS(D2:D8, B2:B8, ">=01-Jan-2023", B2:B8, "<=31-Jan-2023") 

Diese Formel prüft die Daten vom 1. Januar 2023 bis zum 31. Januar 2023 und summiert dann alle Verkäufe. 

Summiere den Datumsbereich mit der Funktion SUMIFS(). Bild vom Autor.

Summieren von Werten mit mehreren numerischen Bedingungen

SUMIFS() Funktion kann Werte summieren, die in bestimmte Zahlenbereiche fallen. Ich habe einen Zufallsdatensatz und möchte die Werte summieren, bei denen der Umsatz größer als 100 und kleiner als 300 ist. Dafür verwende ich die folgende Formel:  

=SUMIFS(C2:C5, C2:C5, ">100", C2:C5, "<300")

Diese Formel gibt als erstes Argument den Bereich der Zellen an, die summiert werden sollen (C2:C5). Dann wendet sie die beiden oben beschriebenen Bedingungen auf denselben Bereich an. Die Funktion SUMIFS() prüft jede Zelle des Bereichs gleichzeitig nach beiden Kriterien. Und du siehst, dass nur Zellen mit Werten zwischen 100 und 300 (exklusiv) in die Summe einbezogen werden. 

Fasse die Werte innerhalb eines Zahlenbereichs mit SUMIFS() zusammen. Bild vom Autor.

Fehlersuche, wenn SUMIFS() nicht funktioniert

Wenn deine SUMIFS() Funktion nicht wie erwartet funktioniert, überprüfe die folgenden Punkte:

Nicht übereinstimmende Bereichsgrößen 

Wenn deine SUMIFS() Formel einen Fehler liefert, kann das an nicht übereinstimmenden Bereichsgrößen liegen. In SUMIFS() müssen alle Bereiche, die du auswählst, wie sum_range und criteria_ranges, die gleiche Anzahl von Zeilen und Spalten haben.

In dieser Formel ist der Summenbereich zum Beispiel B2:B8, aber der Kriterienbereich ist A2:A7, der eine Zeile weniger hat:

=SUMIFS(B2:B8, A2:A7, "Drake White")

Diese Unstimmigkeit führt zu einem #VALUE! Fehler. Stelle also sicher, dass alle Bereiche die gleiche Größe haben, etwa so:

=SUMIFS(B2:B8, A2:A8, "John Doe")

Falsche Formatierung der Textkriterien 

In SUMIFS() musst du Textkriterien in Anführungszeichen setzen. Wenn du das nicht tust, kann es zu falschen Ergebnissen kommen. Diese Formel gibt zum Beispiel 0 zurück, weil Excel Drake White als Namen oder Variable und nicht als Text betrachtet :

=SUMIFS(B2:B8, A2:A8, Drake White)

Um das Problem zu beheben, schließe den Drake White in Anführungszeichen ein, so dass die Ausgabe korrekt ist:

=SUMIFS(B2:B8, A2:A8, "Drake White")

Unerwartete Nullwerte

Wenn die Formel 0 zurückgibt, obwohl du ein Ergebnis erwartest, könnte das an der falschen Formatierung von numerischen oder datumsbasierten Bedingungen liegen. In SUMIFS() müssen Bedingungen wie >100 oder <01-Jan-2023 als Textstrings in Anführungszeichen geschrieben werden. Andernfalls wird ein Fehler oder 0 zurückgegeben, weil die Bedingung nicht in Anführungszeichen steht.

Oder wir können das Datum in einer separaten Zelle speichern (z.B. C1) und diese verwenden:

=SUMIFS(B2:B8, A2:A8, ">="&C1)

SUMIFS() funktioniert nicht mit Array-Transformationen

SUMIFS() unterstützt bestimmte Transformationen nicht, z. B. das Extrahieren bestimmter Komponenten aus Daten innerhalb der Formel. Wenn ich zum Beispiel die Umsätze von Januar und März zusammenzählen will, funktioniert SUMIFS() nicht direkt mit den Array-Kriterien {"Januar", "März"}. 

Um das zu verdeutlichen, nehme ich an, ich habe Monate in Spalte A (Januar, Februar, März, April) und Umsatzwerte in Spalte B (100, 200, 300, 400). Die Formel

=SUMIFS(B2:B5, A2:A5, {"January","March"})

wird nicht die erwartete Summe von 400 (100 + 300) liefern. Stattdessen können wir entweder SUMPRODUCT() verwenden:

=SUMPRODUCT((A2:A5={"January","March"})*B2:B5)

oder wickle SUMIFS() mit SUM() nach dieser Formel ein:

=SUM(SUMIFS(B2:B5, A2:A5, {"January","March"}))

Beide Lösungen behandeln die Array-Kriterien korrekt und geben die Summe der Verkäufe für Januar und März zurück.

Best Practices für die Verwendung von SUMIFS()

Um das Beste aus der Funktion SUMIFS() herauszuholen, müssen wir häufige Fehler vermeiden, indem wir diese Best Practices befolgen: 

• Verwende Wildcards für eine flexible Filterung: Es ist eine gute Praxis, beim Filtern der Daten Platzhalter zu verwenden, denn manchmal sind unsere Daten nicht perfekt organisiert. Vielleicht sind die Produktnamen nicht immer gleich geschrieben oder es gibt zusätzliche Leerzeichen. Mit Platzhaltern kannst du Muster abgleichen, ohne dass du den genauen Text benötigst.

• Vermeide es, Kriterien fest zu kodieren: Anstatt die Bedingungen direkt in deine Formel einzugeben, speichere sie in einer separaten Zelle. Wenn du eine Bedingung wie "Drake White" oder ">100" fest einträgst, musst du die Formel jedes Mal bearbeiten, wenn du sie ändern willst. Mit den Zellbezügen wird die Formel jedoch automatisch aktualisiert.

• Verwende beim Kopieren von Formeln absolute Referenzen: Wenn du eine SUMIFS() Formel in andere Zellen kopierst, passt Excel die Zellbezüge automatisch an. Wenn du das nicht möchtest, verwende absolute Referenzen, indem du $ hinzufügst, um die Zellen zu fixieren.

Schlussgedanken

SUMIFS() ist eine Excel-Funktion, die uns hilft, Daten zu analysieren, indem sie Zahlen filtert und addiert, die bestimmten Kriterien entsprechen. Der Lernpfad eignet sich hervorragend, um z.B. Verkäufe nach Region zu verfolgen, Ausgaben nach Kategorie zusammenzufassen oder den Bestand nach Produktart zu zählen. Wenn du dich erst einmal mit SUMIFS() vertraut gemacht hast, wirst du feststellen, dass es dir Zeit spart und dir hilft, wichtige Trends in deinen Daten zu erkennen, ohne dass du komplizierte Workarounds anwenden musst.

Wenn du diese Fähigkeiten weiter ausbauen möchtest, schau dir unseren Kurs Datenanalyse in Excel, Datenaufbereitung in Excel und Datenvisualisierung in Excel oder den Lernpfad Excel-Grundlagen an. Sie werden dir helfen, deine vorhandenen Fähigkeiten aufzufrischen.