SUMIFS-Funktion: Verwendung der Funktion zum Summieren mehrerer Bedingungen in Excel

Die Summenfunktion in Excel ist äußerst nützlich, wenn Sie viele Bedingungen summieren müssen. Nachfolgend finden Sie Einzelheiten zur Verwendung der Sumifs-Funktion in Microsoft Excel .

Die SUMIFS-Funktion in Excel ist eine der grundlegenden Excel-Funktionen , eine häufig verwendete Berechnungsfunktion in Excel. Um die Summe in Excel zu berechnen, verwenden wir die SUMME-Funktion . Wenn wir der Summenfunktion eine bestimmte Bedingung hinzufügen möchten, verwenden wir die SUMMEWENN-Funktion . In Fällen, in denen die Datentabelle eine Summe mit vielen Bedingungen erfordert, müssen wir die SUMIFS-Funktion verwenden. Im folgenden Artikel erfahren Sie, wie Sie die SUMIFS-Funktion in Excel verwenden.

• MS Excel - Lektion 5: Excel-Formeln und -Funktionen
• So kombinieren Sie die Sumif-Funktion und die Vlookup-Funktion in Excel
• 3 Möglichkeiten, Summen in Excel zu berechnen
• So beheben Sie den Fehler, dass die SUM-Funktion in Excel nicht hinzugefügt wird

Inhaltsverzeichnis des Artikels

• SUMIFS-Funktionssyntax in Excel
• Beachten Sie, wenn Sie die SUMIFS-Funktion in Excel verwenden
• Anweisungen zur Verwendung der SUMIFS-Funktion in Excel
  • Verwenden Sie Platzhalter
  • Verwenden Sie benannte Bereiche mit der SUMIFS-Funktion
  • Beispiel einer SUMIFS-Funktion in Excel
• Einschränkungen der SUMIFS-Funktion

SUMIFS-Funktionssyntax in Excel

Die Syntax der SUMIFS-Funktion in Excel lautet =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…).

Da drin:

• Sum_range: sind die Zellen, die in der Datentabelle summiert werden müssen, leere Werte und Textwerte werden ignoriert, erforderliche Parameter.
• Kriterienbereich1: Der Bereich, der anhand des Kriteriums Kriterien1 getestet werden muss, ist ein erforderlicher Wert.
• Kriterien1: Bedingung, die auf Kriterien_Bereich1 angewendet wird, kann eine Zahl, ein Ausdruck oder eine Zellreferenz sein, um zu bestimmen, welche Zellen in Kriterien_Bereich1 summiert werden, außerdem ein erforderlicher Wert.
• Kriterien_Bereich2, Kriterien2,…: optionale zusätzliche Bereiche und Bedingungen, maximal 127 Paare von Kriterien_Bereich, Kriterien.

Beachten Sie, wenn Sie die SUMIFS-Funktion in Excel verwenden

• Für jede Zelle im Bereich wird der sum_range-Bereich summiert, wenn alle entsprechenden Bedingungen, die für diese Zelle als wahr ermittelt wurden, erfüllt sind.
• Zellen in sum_range, die TRUE enthalten, werden als 1 betrachtet, Zellen mit FALSE werden als 0 betrachtet.
• Jeder Kriterienbereich muss die gleiche Auswahlgröße wie der Summenbereich haben, Kriterien_Bereich und Summenbereich müssen die gleiche Anzahl an Zeilen und die gleiche Anzahl an Spalten haben.
• Kriterien können ein Fragezeichen (?) anstelle eines einzelnen Zeichens und ein Sternchen (*) anstelle einer Zeichenfolge verwenden. Wenn die Bedingung ein Fragezeichen oder ein Sternchen ist, müssen Sie ein ~-Zeichen davor eingeben, der Bedingungswert ist der in „ eingeschlossene Text.
• Mehrere Bedingungen werden mithilfe der UND-Logik angewendet, d. h. Kriterium1 UND Kriterium2 usw.
• Jeder zusätzliche Bereich muss die gleiche Anzahl an Zeilen und Spalten haben wie der Gesamtbereich, die Bereiche müssen jedoch nicht zusammenhängend sein. Wenn Sie nicht übereinstimmende Bereiche angeben, erhalten Sie einen #VALUE-Fehler.
• Die Textzeichenfolge in den Kriterien muss in Anführungszeichen („“) eingeschlossen werden, z. B. „apple“, „>32“, „jap*“
• Zellverweise in Kriterien können nicht in Anführungszeichen gesetzt werden, d. h. „<>“.
• SUMIF und SUMIFS können Bereiche, aber keine Arrays verarbeiten. Das bedeutet, dass Sie keine anderen Funktionen wie YEAR für den Kriterienbereich verwenden können, da das Ergebnis ein Array ist. Wenn Sie diese Funktionalität benötigen, verwenden Sie die SUMPRODUCT-Funktion.
• Die Reihenfolge der Argumente zwischen den Funktionen SUMIFS und SUMIF ist unterschiedlich. Der Summenbereich ist das erste Argument in SUMIFS, aber das dritte Argument in SUMIF.

Anweisungen zur Verwendung der SUMIFS-Funktion in Excel

Verwenden Sie Platzhalter

Platzhalterzeichen wie „*“ und „?“ kann im Kriterienargument verwendet werden, wenn die SUMIFS-Funktion verwendet wird. Mithilfe dieser Platzhalter können Sie ähnliche, aber keine exakten Übereinstimmungen finden.

Sternchen (*) – Entspricht jeder Zeichenfolge, die nach, vor oder mit umgebenden Kriterien verwendet werden kann, um eine teilweise Verwendung von Suchkriterien zu ermöglichen.

Wenn Sie beispielsweise die folgenden Kriterien in der SUMIFS-Funktion anwenden:

• N * – Impliziert alle Zellen im Bereich, die mit N beginnen
• * N – Impliziert alle Zellen im Bereich, die mit N enden
• *N* – Zellen, die N enthalten

Fragezeichen (?) – Entspricht einem beliebigen einzelnen Zeichen. Angenommen, Sie wenden N?r als Kriterium an. „?“ Hier wird ein einzelnes Zeichen ersetzt. N?r entspricht North, Nor usw. Der Name wird jedoch nicht berücksichtigt.

Was passiert, wenn die angegebenen Daten ein Sternchen oder ein echtes Fragezeichen enthalten?

In diesem Fall können Sie die Tilde (~) verwenden. In diesem Fall müssen Sie „~“ vor dem Fragezeichen eingeben.

Verwenden Sie benannte Bereiche mit der SUMIFS-Funktion

Benannter Bereich ist der beschreibende Name einer Reihe von Zellen oder eines Zellbereichs in einem Arbeitsblatt. Sie können benannte Bereiche verwenden, während Sie die SUMIFS-Funktion verwenden.

Beispiel einer SUMIFS-Funktion in Excel

Wir berechnen die Gesamtsumme anhand der folgenden Datentabelle unter verschiedenen Bedingungen.

Beispiel 1 : Berechnen Sie die Gesamtzahl der Produkte, die der Mitarbeiter von Hoai mit einem Stückpreis von weniger als 400.000 VND verkauft.

Im Ergebniseingabefeld geben wir die Formel =SUMIFS(D2:D7,C2:C7,"Hoai",E2:E7,"<400000") ein und drücken dann="">

Da drin:

• D2:D7 ist der Bereich, in dem der Gesamtposten berechnet werden muss.
• C2:C7 ist der Wertebereich der Bedingung für den Mitarbeiternamen.
• „Hoai“ ist die Bedingung für den Namen des Mitarbeiters, der die Anzahl der im Bereich C2:C7 enthaltenen Artikel berechnen muss.
• E2:E7 ist der Zustand des Artikels.
• „400.000“ ist die Bedingung für die Aufnahme von Gegenständen in den Bereichen E2:E7.

Das Ergebnis ist die Gesamtzahl der Elemente, wie unten dargestellt.

Beispiel 2 : Berechnen Sie die Gesamtzahl der von russischen Mitarbeitern verkauften Artikel mit der Seriennummer <>

Im Ergebniseingabefeld geben wir die Formel =SUMIFS(D2:D7,C2:C7,"=Russia",A2:A7,"<5") ein und drücken dann="" enter.="" at = "" name="" multiplication="" tablet="" "nga"="" enter="" sign="or" remove="" sign="even">

Als Ergebnis erhalten wir die Gesamtzahl der von russischen Mitarbeitern mit Seriennummern < 5="" verkauften Artikel, also 550 Artikel="">

Beispiel 3 : Berechnen Sie die Gesamtzahl der von russischen Mitarbeitern verkauften Produkte, ohne Schalprodukte.

Im Ergebniseingabefeld geben wir die Formel =SUMIFS(D2:D7,C2:C7,“Russland”,B2:B7,“<>Schal“) ein. Dabei wird das <>-Zeichen verwendet, um ein bestimmtes Objekt im Datenbereich als Bedingung auszuschließen.

Das Ergebnis ist die Gesamtzahl der Produkte, wie unten dargestellt.

Beispiel 4:

Berechnen Sie die Summe aller Statistiken für Wasser-Pokémon der Generation 1:

Die Bedingung hier ist, dass Wasser und Erzeugung 1 sind.

Hinweis: Der vollständige Datensatz wird nach Zeile 14 bis hinunter zu Zeile 759 fortgesetzt.

Hier sind die Schritt-für-Schritt-Details:

1. Wählen Sie Zelle H3 aus .
2. Geben Sie =SUMIFS ein.
3. Doppelklicken Sie auf den Befehl SUMIFS.
4. Wählen Sie den Gesamtbereich als aus C2:C759.
5. Typ,
6. Bereich für erste Bedingung auswählen B2:B759(Wert vom Typ 1)
7. Kriterien auswählen (Zelle F3, mit Wert Water)
8. Typ ,
9. Wählen Sie den Bereich für die zweite Bedingung aus D2:D759(Generierungswert)
10. Typ,
11. Bestimmen Sie die Kriterien (Zelle G3 hat den Wert 1).
12. Drücken Sie Enter .

Hinweis: Sie können weitere Bedingungen hinzufügen, indem Sie die Schritte 9 bis 12 wiederholen, bevor Sie die Eingabetaste drücken.

Diese Funktion berechnet nun die Summe der Gesamtstatistiken für Wasserpokemon der Generation 1. Diese Funktion kann für zukünftige Generationen wiederholt werden, um sie zu vergleichen.

Oben finden Sie einige Beispiele für die Verwendung der SUMIFS-Funktion zur Berechnung des Gesamtwerts mit vielen kombinierten Bedingungen. Wir müssen die Bereiche und Begleitbedingungen richtig anordnen, damit Excel die Funktionsformel für die Berechnung erkennen kann.

Einschränkungen der SUMIFS-Funktion

Die SUMIFS-Funktion weist einige Einschränkungen auf, die Sie beachten sollten:

• Die Bedingungen in SUMIFS werden mithilfe der UND-Logik kombiniert. Mit anderen Worten: Alle Bedingungen müssen WAHR sein, damit eine Zelle in die Gesamtsumme einbezogen wird. Um Zellen mithilfe der ODER-Logik zu summieren, können Sie in einfachen Fällen eine alternative Lösung verwenden.
• Die SUMIFS-Funktion erfordert tatsächliche Bereiche für alle Bereichsargumente; Sie können kein Array verwenden. Dies bedeutet, dass Sie beispielsweise das Jahr nicht aus einem Datum innerhalb der SUMIFS-Funktion extrahieren können. Um die im Bereichsargument angezeigten Werte vor der Anwendung der Kriterien zu ändern, ist die SUMPRODUCT-Funktion eine flexible Lösung.
• Bei SUMIFS wird die Groß-/Kleinschreibung nicht beachtet. Um Werte basierend auf einer Bedingung unter Berücksichtigung der Groß- und Kleinschreibung zu summieren, können Sie eine Formel verwenden, die auf der SUMPRODUCT-Funktion mit der EXACT-Funktion basiert.

Die gängigste Möglichkeit, die oben genannten Einschränkungen zu umgehen, ist die Verwendung der SUMPRODUCT-Funktion. In aktuellen Excel-Versionen besteht eine weitere Möglichkeit darin, die neueren Funktionen BYROW und BYCOL zu verwenden.

So beheben Sie den #VALUE-Fehler in der SUMIFS-Funktion von Excel

Problem: Die Formel bezieht sich auf Zellen in einer geschlossenen Arbeitsmappe

Die SUMIF-Funktion, die auf eine Zelle oder einen Zellbereich in einer geschlossenen Arbeitsmappe verweist, führt zu einem #WERT!-Fehler.

Hinweis: Dies ist ein häufiger Fehler. Diese Meldung wird auch häufig angezeigt, wenn Sie andere Excel-Funktionen wie ZÄHLENWENN, ZÄHLENWENN, ZÄHLENBLANK usw. verwenden.

Lösung:

Öffnen Sie die in dieser Formel enthaltene Arbeitsmappe und drücken Sie F9, um die Formel zu aktualisieren. Sie können das Problem auch beheben, indem Sie sowohl die SUM- als auch die IF-Funktion in einer Array-Formel verwenden.

Problem: Die Kriterienzeichenfolge ist länger als 255 Zeichen

Die SUMIFS-Funktion gibt falsche Ergebnisse zurück, wenn Sie versuchen, Zeichenfolgen mit mehr als 255 Zeichen zu kombinieren.

Lösung:

Kürzen Sie die Saite nach Möglichkeit. Wenn dies nicht möglich ist, verwenden Sie die CONCATENATE-Funktion oder den &-Operator, um den Wert in mehrere Zeichenfolgen aufzuteilen. Zum Beispiel:

=SUMIF(B2:B12,"long string"&"another long string")

Problem: In SUMIFS stimmt das Argument „kriteriums_range“ nicht mit dem Argument „sum_range“ überein.

Das Bereichsargument muss in SUMIFS immer dasselbe sein. Das bedeutet, dass sich die Argumente „criteria_range“ und „sum_range“ auf die gleiche Anzahl von Zeilen und Spalten beziehen sollten.

Im folgenden Beispiel gibt die Formel den gesamten täglichen Apfelverkauf in Bellevue zurück. Das Argument „sum_range“ (C2:C10) entspricht jedoch nicht der Anzahl der Zeilen und Spalten in „criteria_range“ (A2:A12 und B2:B12). Die Verwendung einer Formel =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)führt zu einem #VALUE-Fehler.

Lösung:

Wechseln Sie sum_rangezu C2:C12 und versuchen Sie es erneut.

Ich wünsche Ihnen viel Erfolg!