Dieses Tutorial zeigt, wie Sie die Exzel SUMIF und SUMIFS Funktiongen in Excel und Google Sheets, um Daten zu summieren, die bestimmte Kriterien erfüllen.
Übersicht über die SUMIF-Funktion
Sie können die SUMIF-Funktion in Excel verwenden, um Zellen zu summieren, die einen bestimmten Wert enthalten, Zellen zu summieren, die größer oder gleich einem Wert sind usw.
(Beachten Sie, wie die Formeleingaben angezeigt werden)
Syntax und Argumente der SUMIF-Funktion:
| 1 | =SUMIF (Bereich, Kriterien, [sum_range]) |
Bereich - Der Zellbereich, auf den Sie die Kriterien anwenden möchten.
Kriterien - Die Kriterien, anhand derer bestimmt wird, welche Zellen hinzugefügt werden sollen.
Summenbereich - [optional] Die Zellen, die addiert werden sollen. Wenn sum_range weggelassen wird, werden die Zellen im Bereich stattdessen addiert.
Was ist die SUMIF-Funktion?
Die SUMIF-Funktion ist eine der älteren Funktionen, die in Tabellenkalkulationen verwendet werden. Es wird verwendet, um einen Bereich von Zellen zu durchsuchen, um nach einem bestimmten Kriterium zu suchen, und dann Werte in einem Bereich zu addieren, der diesen Werten entspricht. Die ursprüngliche SUMIF-Funktion war auf nur ein Kriterium beschränkt. Nach 2007 wurde die SUMIFS-Funktion geschaffen, die eine Vielzahl von Kriterien zulässt. Der Großteil der allgemeinen Verwendung bleibt zwischen den beiden gleich, aber es gibt einige kritische Unterschiede in der Syntax, die wir in diesem Artikel besprechen werden.
Falls noch nicht geschehen, können Sie viele ähnliche Strukturen und Beispiele im Artikel ZÄHLENWENN nachlesen.
Einfaches Beispiel
Betrachten wir diese Liste der aufgezeichneten Verkäufe, und wir möchten das Gesamteinkommen kennen.
Da wir eine Ausgabe hatten, den negativen Wert, können wir nicht einfach eine Grundsumme machen. Stattdessen wollen wir nur die Werte summieren, die größer als 0 sind. „größer als 0“ ist unser Kriterium in einer SUMIF-Funktion. Unsere Formel, um dies zu sagen, ist
| 1 | =SUMIF(A2:A7, ">0") |
Zweispaltiges Beispiel
Während die ursprüngliche SUMIF-Funktion so konzipiert wurde, dass Sie ein Kriterium auf den zu summierenden Zahlenbereich anwenden können, müssen Sie in den meisten Fällen ein oder mehrere Kriterien auf andere Spalten anwenden. Betrachten wir diese Tabelle:
Wenn wir nun die ursprüngliche SUMIF-Funktion verwenden, um herauszufinden, wie viele Bananen wir haben (aufgelistet in Zelle D1), müssen wir den gewünschten Bereich angeben Summe als letztes Argument, und so wäre unsere Formel
| 1 | =SUMIF(A2:A7, D1, B2:B7) |
Als Programmierer jedoch schließlich erkannten, dass Benutzer mehr als ein Kriterium angeben wollten, wurde die SUMIFS-Funktion entwickelt. Um eine Struktur zu erstellen, die für eine beliebige Anzahl von Kriterien funktioniert, erfordert SUMIFS, dass der Summenbereich zuerst aufgeführt wird. In unserem Beispiel bedeutet dies, dass die Formel
| 1 | =SUMIFS(B2:B7, A2:A7, D1) |
HINWEIS: Diese beiden Formeln liefern dasselbe Ergebnis und können ähnlich aussehen. Achten Sie daher genau darauf, welche Funktion verwendet wird, um sicherzustellen, dass Sie alle Argumente in der richtigen Reihenfolge auflisten.
Arbeiten mit Datumsangaben, mehreren Kriterien
Beim Arbeiten mit Datumsangaben in einer Tabellenkalkulation ist es zwar möglich, das Datum direkt in die Formel einzugeben, es empfiehlt sich jedoch, das Datum in einer Zelle zu haben, damit Sie einfach auf die Zelle in einer Formel verweisen können. Dies hilft dem Computer beispielsweise zu wissen, dass Sie das Datum 27.05.2020 und nicht die Zahl 5 geteilt durch 27 geteilt durch 2022 verwenden möchten.
Schauen wir uns unsere nächste Tabelle an, in der alle zwei Wochen die Anzahl der Besucher einer Website aufgezeichnet wird.
Wir können den Start- und Endpunkt des Bereichs angeben, den wir in D2 und E2 betrachten möchten. Unsere Formel zur Summe der Besucherzahlen in diesem Bereich könnte dann lauten:
| 1 | =SUMIFS(B2:B7, A2:A7, ">="&D2, A2:A7, "<="&E2) |
Beachten Sie, wie wir die Vergleiche von „=“ mit den Zellbezügen verketten konnten, um die Kriterien zu erstellen. Auch wenn beide Kriterien auf denselben Zellbereich (A2:A7) angewendet wurden, müssen Sie den Bereich zweimal schreiben, einmal pro Kriterium.
Mehrere Spalten
Wenn Sie mehrere Kriterien verwenden, können Sie sie auf denselben Bereich anwenden, wie wir es im vorherigen Beispiel getan haben, oder Sie können sie auf verschiedene Bereiche anwenden. Lassen Sie uns unsere Beispieldaten in dieser Tabelle kombinieren:
Wir haben einige Zellen eingerichtet, damit der Benutzer in die Zellen E2 bis G2 eingeben kann, wonach er suchen möchte. Wir brauchen also eine Formel, die die Gesamtzahl der im Februar gepflückten Äpfel addiert. Unsere Formel sieht so aus:
| 1 | =SUMIFS(C2:C7, B2:B7, ">="&F2, B2:B7, "<="&G2, A2:A7, E2) |
SUMIFS mit OR-Logik
Bis zu diesem Punkt waren die von uns verwendeten Beispiele alle UND-basierte Vergleiche, bei denen wir nach Zeilen suchen, die alle unsere Kriterien erfüllen. Nun betrachten wir den Fall, wenn Sie nach der Möglichkeit suchen möchten, dass eine Zeile das eine oder andere Kriterium erfüllt.
Schauen wir uns diese Verkaufsliste an:
Wir möchten den Gesamtumsatz von Adam und Bob addieren. Dazu haben Sie mehrere Möglichkeiten. Am einfachsten ist es, zwei SUMIFS wie folgt zusammenzufügen:
| 1 | =SUMIFS(B2:B7, A2:A7, "Adam")+SUMIFS(B2:B7, A2:A7, "Bob") |
Hier haben wir den Computer unsere individuellen Punktzahlen berechnen lassen und dann addieren wir sie.
Unsere nächste Option ist gut, wenn Sie mehr Kriterienbereiche haben, sodass Sie nicht die gesamte Formel wiederholt neu schreiben müssen. In der vorherigen Formel haben wir den Computer manuell angewiesen, zwei verschiedene SUMIFS zusammenzuzählen. Sie können dies jedoch auch tun, indem Sie Ihre Kriterien wie folgt in ein Array schreiben:
| 1 | =SUMME(SUMIFS(B2:B7, A2:A7, {"Adam", "Bob"})) |
Sehen Sie sich an, wie das Array innerhalb der geschweiften Klammern aufgebaut ist. Wenn der Computer diese Formel auswertet, weiß er, dass wir für jedes Element in unserem Array eine SUMIFS-Funktion berechnen möchten, um so ein Array von Zahlen zu erstellen. Die äußere SUM-Funktion nimmt dann dieses Array von Zahlen und wandelt es in eine einzelne Zahl um. Wenn Sie die Formelauswertung durchgehen, würde sie so aussehen:
| 123 | =SUMME(SUMIFS(B2:B7, A2:A7, {"Adam", "Bob"}))=SUMME(27401, 43470)=70871 |
Wir erhalten das gleiche Ergebnis, aber wir konnten die Formel etwas prägnanter formulieren.
Umgang mit Leerzeichen
Manchmal enthält Ihr Datensatz leere Zellen, die Sie entweder finden oder vermeiden müssen. Das Einrichten der Kriterien dafür kann etwas schwierig sein, also schauen wir uns ein anderes Beispiel an.
Beachten Sie, dass Zelle A3 wirklich leer ist, während Zelle A5 eine Formel enthält, die eine Null-Länge-Zeichenfolge von "" zurückgibt. Wenn wir die Gesamtsumme von finden wollen wirklich leere Zellen, würden wir ein Kriterium von „=“ verwenden und unsere Formel würde so aussehen:
| 1 | =SUMIFS(B2:B7,A2:A7,"=") |
Auf der anderen Seite, wenn wir die Summe für alle Zellen erhalten möchten, die optisch leer aussieht, ändern wir das Kriterium auf „“, und die Formel sieht so aus
| 1 | =SUMIFS(B2:B7,A2:A7,"") |
Drehen wir es um: Was ist, wenn Sie die Summe der nicht leeren Zellen ermitteln möchten? Leider lässt Sie das aktuelle Design die Null-Länge-Zeichenfolge nicht vermeiden. Sie können ein Kriterium von „“ verwenden, aber wie Sie im Beispiel sehen können, enthält es immer noch den Wert aus Zeile 5.
| 1 | =SUMIFS(B2:B7,A2:A7,"") |
Wenn Sie keine Zellen zählen müssen, die Zeichenfolgen der Länge Null enthalten, sollten Sie die LEN-Funktion in einem SUMPRODUCT verwenden
SUMIF in Google Tabellen
Die SUMIF-Funktion funktioniert in Google Sheets genauso wie in Excel:
