In diesem Lernprogramm wird der Unterschied zwischen den Funktionen SUMIF und SUMIFS in Bezug auf ihre Syntax und erläutert Verwendung und bietet eine Reihe von Formelbeispielen zum Summieren von Werten mit mehreren UND / ODER-Kriterien in Excel 2019, 2016, 2013, 2010, 2007, 2003 und niedriger.
Wie jeder weiß, bietet Microsoft Excel ein Array von Funktionen, um verschiedene Berechnungen mit Daten durchzuführen. Vor einigen Artikeln haben wir COUNTIF und COUNTIFS untersucht, die zum Zählen von Zellen basierend auf einer einzelnen Bedingung bzw. mehreren Bedingungen entwickelt wurden. Letzte Woche haben wir Excel SUMIF behandelt, das Werte hinzufügt, die die angegebenen Kriterien erfüllen. Jetzt ist es an der Zeit, die Pluralversion von SUMIF – Excel SUMIFS durchzugehen, mit der Werte nach mehreren Kriterien summiert werden können.
Diejenigen, die mit der SUMIF-Funktion vertraut sind, denken möglicherweise, dass die Konvertierung in SUMIFS nur einen zusätzlichen Aufwand erfordert „S“ und ein paar zusätzliche Kriterien. Dies scheint ziemlich logisch zu sein … aber „logisch“ ist es nicht immer der Fall, wenn es um Microsoft geht 🙂
- SUMIF-Funktion – Syntax & Verwendung
- SUMIFS-Funktion – Syntax & Verwendung
- Verwendung von SUMIFS in Excel (UND-Logik)
- Excel-SUMIF mit mehreren Kriterien (ODER-Logik)
- Excel-SUMIFS mit mehreren ODER-Bedingungen
- Verwenden von Excel-SUMIF in Array-Formeln
Excel SUMIF-Funktion – Syntax & Verwendung
Die SUMIF-Funktion wird verwendet, um Werte basierend auf einem einzelnen Kriterium bedingt zu summieren. Wir haben die SUMIF-Syntax im vorherigen Artikel ausführlich besprochen. Lassen Sie mich jetzt eine kurze Zusammenfassung geben.
-
range– der Bereich von Zellen, der nach Ihren Kriterien bewertet werden soll, erforderlich. -
criteria– die Bedingung, die erfüllt sein muss, erforderlich. -
sum_range– Die Zellen, die summiert werden sollen, wenn die Bedingung erfüllt ist, optional.
Wie Sie sehen, ist die Die Syntax der Excel SUMIF-Funktion lässt nur eine Bedingung zu. Wir sagen jedoch, dass Excel SUMIF verwendet werden kann, um Werte mit mehreren Kriterien zu summieren. Wie kann das sein? Durch Hinzufügen der Ergebnisse mehrerer SUMIF-Funktionen und Verwenden von SUMIF-Formeln mit Array-Kriterien, wie in den folgenden Beispielen gezeigt.
Excel SUMIFS-Funktion – Syntax & Verwendung
Sie verwenden SUMIFS in Excel, um eine bedingte Summe von Werten basierend auf mehreren Kriterien zu finden. Die SUMIFS-Funktion wurde in Excel 2007 eingeführt, sodass Sie sie in allen modernen Versionen von Excel 2019, 2016, 2013, 2010 und 2007 verwenden können.
Im Vergleich zu SUMIF ist die SUMIFS-Syntax etwas komplexer :
Die ersten drei Argumente sind obligatorisch, zusätzliche Bereiche und die zugehörigen Kriterien sind optional.
-
sum_range– eine oder mehrere zu summierende Zellen erforderlich. Dies kann eine einzelne Zelle, ein Zellbereich oder ein benannter Bereich sein. Es werden nur Zellen mit Zahlen summiert. Leer- und Textwerte werden ignoriert. -
criteria_range1– Der erste Bereich, der anhand der zugehörigen Kriterien bewertet wird, ist erforderlich. -
criteria1– die erste Bedingung, die erfüllt sein muss, erforderlich. Sie können die Kriterien in Form einer Zahl, eines logischen Ausdrucks, einer Zellreferenz, eines Textes oder einer anderen Excel-Funktion angeben. Sie können beispielsweise Kriterien wie 10, „> = 10“, A1, „Kirschen“ oder HEUTE () verwenden. -
criteria_range2, criteria2, …– Dies sind zusätzliche Bereiche und Kriterien, die damit verbunden sind, optional. Sie können bis zu 127 Bereichs- / Kriterienpaare in SUMIFS-Formeln verwenden.
Und jetzt lassen Sie “ Sehen Sie sich die Excel SUMIFS-Formel mit zwei Bedingungen an. Angenommen, Sie haben eine Tabelle mit den Obstsendungen verschiedener Lieferanten. Sie haben die Fruchtnamen in Spalte A, die Lieferantennamen in Spalte B und die Menge in Spalte C. Was Sie wollen, ist, eine Summe von Beträgen herauszufinden, die sich auf eine bestimmte Frucht und einen bestimmten Lieferanten beziehen, z Alle Äpfel werden von Pete geliefert.
Wenn Sie etwas Neues lernen, ist es immer eine gute Idee, mit einfachen Dingen zu beginnen. Definieren wir zunächst alle Argumente für unsere SUMIFS-Formel:
- Summenbereich – C2: C9
- Kriterienbereich1 – A2: A9
- Kriterien1 – „Äpfel“
- Kriterien_Bereich2 – B2: B9
- Kriterien2 – „Pete“
Stellen Sie nun die obigen Parameter zusammen und Sie erhält die folgende SUMIFS-Formel:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Zum Verfeinern In der weiteren Formel können Sie die Textkriterien „Äpfel“ und „Pete“ durch Zellreferenzen ersetzen.In diesem Fall müssen Sie die Formel nicht ändern, um die Menge anderer Früchte eines anderen Lieferanten zu berechnen:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
Verwenden von SUMIFS und SUMIF in Excel – Dinge, die Sie beachten sollten
Da das Ziel dieses Tutorials darin besteht, alle möglichen Möglichkeiten zum Summieren von Werten unter verschiedenen Bedingungen abzudecken, werden Formelbeispiele mit beiden Funktionen – Excel SUMIFS und SUMIF – erläutert Um sie richtig zu verwenden, müssen Sie klar verstehen, was diese beiden Funktionen gemeinsam haben und auf welche Weise sie sich unterscheiden.
Während der gemeinsame Teil klar ist – ähnliche Ziele und Parameter – die Unterschiede sind nicht so offensichtlich, obwohl sehr wichtig.
1. Die Reihenfolge der Argumente
In Excel SUMIF- und SUMIFS-Funktionen ist die Reihenfolge der Argumente unterschiedlich. Insbesondere ist sum_range die erste Parameter in SUMIFS, aber es ist 3. in SUMIF.
Auf den ersten Blick scheint es, dass Microsoft die Lernkurve für seine Benutzer absichtlich kompliziert. Howev Bei näherer Betrachtung werden Sie die Gründe dafür erkennen. Der Punkt ist, dass sum_range in SUMIF optional ist. Wenn Sie es weglassen, ist es kein Problem, dass Ihre SUMIF-Formel Werte im Bereich (erster Parameter) summiert.
In SUMIFS ist der sum_range sehr wichtig und obligatorisch, und deshalb steht er an erster Stelle. Wahrscheinlich dachten Microsoft-Leute, dass nach dem Hinzufügen des 10. oder 100. Bereichs / Kriterien-Paares jemand vergessen könnte, den zu summierenden Bereich anzugeben 🙂
2. Summenbereich und Kriterienbereich sollten gleich groß sein
In der SUMIF-Funktion muss das Argument sum_range nicht unbedingt dieselbe Größe wie das Bereichsargument haben, solange Sie die obere linke Zelle rechts haben. In Excel SUMIFS muss jeder Kriterienbereich die gleiche Anzahl von Zeilen und Spalten enthalten wie der Parameter sum_range.
Beispielsweise gibt =SUMIF(A2:A9,F1,C2:C18) das richtige Ergebnis zurück, da Excel dies berücksichtigt Nur die Zelle ganz links im Argument sum_range (in diesem Beispiel C2, was korrekt ist) und enthält dann so viele Spalten und Zeilen, wie im Bereichsargument enthalten sind.
Okay, genug Strategie (dh Theorie) Lassen Sie uns in die Taktik einsteigen (dh Formelbeispiele 🙂
Verwendung von SUMIFS in Excel – Formelbeispiele
Vor einem Moment haben wir eine einfache SUMIFS-Formel mit zwei Texten besprochen Auf die gleiche Weise können Sie Excel-SUMIFS mit mehreren Kriterien verwenden, die durch Zahlen, Daten, logische Ausdrücke und andere Excel-Funktionen ausgedrückt werden.
Beispiel 1. Excel-SUMIFS mit Vergleichsoperatoren
Angenommen, Sie möchten in unserer Obstlieferantentabelle alle Lieferungen von Mike mit einer Menge von 200 oder mehr summieren. Verwenden Sie dazu den Vergleichsoperator „größer als o“ r ist gleich „(> =) in Kriterien2 und erhält die folgende SUMIFS-Formel:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Bei der Erörterung der Excel SUMIF-Funktion haben wir alle möglichen Vergleichsoperatoren ausführlich behandelt, dieselben Operatoren kann in SUMIFS-Kriterien verwendet werden. Beispielsweise gibt die folgende Formel mit die Summe aller Werte in Zellen C2: C9 zurück, die größer oder gleich 200 und kleiner oder gleich 300 sind.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Beispiel 2. Verwenden von Excel-SUMIFS mit Datumsangaben
Wenn Sie Werte mit mehreren Kriterien basierend auf dem aktuellen Datum summieren möchten, verwenden Sie die Funktion TODAY () in Ihren SUMIFS Kriterien, wie unten gezeigt. Die folgende Formel summiert die Werte in Spalte D, wenn ein entsprechendes Datum in Spalte C innerhalb der letzten 7 Tage liegt, einschließlich heute:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
In ähnlicher Weise können Sie die Excel SUMIF-Funktion verwenden, um Werte in einem bestimmten Datumsbereich zu summieren. Die folgende SUMIFS-Formel fügt beispielsweise die Werte in den Zellen C2 hinzu: C9, wenn ein Datum in Spalte B zwischen dem 1. Oktober 2014 und dem 31. Oktober 2014 einschließlich liegt.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Das gleiche Ergebnis kann erzielt werden, indem die Differenz zweier SUMIF-Funktionen berechnet wird, wie in diesem Beispiel gezeigt – Verwendung von SUMIF zum Summieren von Werten in einem bestimmten Datumsbereich. Excel SUMIFS ist jedoch viel einfacher und verständlicher, nicht wahr?
Beispiel 3. Excel SUMIFS mit leeren und nicht leeren Zellen
Bei der Analyse von Berichten und anderen Daten sind Sie Möglicherweise müssen häufig Werte summiert werden, die entweder leeren oder nicht leeren Zellen entsprechen.
Nun wollen wir sehen, wie Sie eine SUMIFS-Formel mit den Kriterien „leer“ und „nicht leer“ für real verwenden können Daten.
Angenommen, Sie haben ein Bestelldatum in Spalte B, ein Lieferdatum in Spalte C und eine Menge. in Spalte D. Wie finden Sie die Gesamtzahl der Produkte, die noch nicht geliefert wurden? Das heißt, Sie möchten die Summe der Werte kennen, die nicht leeren Zellen in Spalte B und leeren Zellen in Spalte C entsprechen.
Die Lösung besteht darin, die SUMIFS-Formel mit zwei Kriterien zu verwenden:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Verwenden von Excel SUMIF mit mehreren ODER-Kriterien
Wie zu Beginn dieses Lernprogramms erwähnt, wurde die SUMIFS-Funktion mit UND-Logik entworfen. Was aber, wenn Sie Werte mit mehreren ODER-Kriterien summieren müssen, dh wenn mindestens eine der Bedingungen erfüllt ist?
Beispiel 1. SUMIF + SUMIF
Die einfachste Lösung ist das Summieren Die Ergebnisse werden von mehreren SUMIF-Funktionen zurückgegeben. Die folgende Formel zeigt beispielsweise, wie die Gesamtzahl der von Mike und John gelieferten Produkte ermittelt wird:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Wie Sie sehen, fügt die erste SUMIF-Funktion die Mengen hinzu, die „Mike“ entsprechen, die andere SUMIF-Funktion gibt die Mengen zurück, die sich auf „John“ beziehen, und dann fügen Sie diese 2 Zahlen hinzu.
Beispiel 2. SUM & SUMIF mit einem Array-Argument
Die obige Lösung ist sehr einfach und kann die Aufgabe schnell erledigen, wenn es nur solche gibt ein paar Kriterien. Eine SUMIF + SUMIF-Formel kann jedoch enorm wachsen, wenn Sie Werte mit mehreren ODER-Bedingungen summieren möchten. In diesem Fall besteht ein besserer Ansatz darin, ein Array-Kriterienargument in der SUMIF-Funktion zu verwenden. Lassen Sie uns diesen Ansatz jetzt untersuchen.
Sie können zunächst alle durch Kommas getrennten Bedingungen auflisten und dann die resultierende durch Kommas getrennte Liste in {geschweifte Klammern} einschließen, die technisch als Array bezeichnet wird.
Das aus 3 Werten bestehende Array-Argument zwingt Ihre SUMIF-Formel, drei separate Ergebnisse zurückzugeben. Da wir die Formel jedoch in eine einzelne Zelle schreiben, wird nur das erste Ergebnis zurückgegeben – dh die Gesamtzahl der von John gelieferten Produkte Damit dieser Ansatz mit Array-Kriterien funktioniert, müssen Sie noch einen kleinen Trick anwenden: Fügen Sie Ihre SUMIF-Formel in eine SUMME-Funktion ein, wie folgt:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Wie Sie sehen, macht ein Array-Kriterium die Formel im Vergleich zu SUMIF + SUMIF viel kompakter und Sie können so viele Werte wie möglich hinzufügen Sie mögen das Array.
Dieser Ansatz funktioniert sowohl mit Zahlen als auch mit Textwerten. Wenn Sie beispielsweise anstelle der Lieferantennamen in Spalte C Lieferanten-IDs wie 1, 2, 3 usw. hatten ., Dann würde Ihre SUMIF-Formel folgendermaßen aussehen:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
Im Gegensatz zu Textwerten müssen Zahlen nicht in doppelte Anführungszeichen gesetzt werden Array-Argumente.
Beispiel 3. SUMPRODUCT & SUMIF
Falls Sie es vorziehen, die Kriterien in einigen Zellen aufzulisten Geben Sie sie direkt in der Formel an. Sie können SUMIF in Verbindung mit der Funktion SUMPRODUCT verwenden, die Komponenten in den angegebenen Arrays multipliziert und die Summe dieser Produkte zurückgibt.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Wobei G2: G4 die Zellen sind, die Ihre Kriterien enthalten, in unserem Fall die Namen der Lieferanten, wie im folgenden Screenshot dargestellt.
Aber natürlich hindert Sie nichts daran Auflisten der Werte in einem Array-Kriterium Ihrer SUMIF-Funktion, wenn Sie:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
Das von beiden Formeln zurückgegebene Ergebnis ist identisch mit dem, was Sie im Screenshot sehen:
Excel SUMIFS mit Mehrere ODER-Kriterien
Wenn Sie Werte in Excel nicht nur mit mehreren ODER-Bedingungen, sondern mit mehreren Bedingungen bedingt summieren möchten, müssen Sie SUMIFS anstelle von SUMIF verwenden. Die Formeln sind sehr ähnlich zu dem, was wir gerade besprochen haben.
Wie üblich kann ein Beispiel helfen, den Punkt besser zu veranschaulichen. Fügen Sie in unserer Tabelle der Obstlieferanten das Lieferdatum (Spalte) hinzu E) und finden Sie die Gesamtmenge, die Mike, John und Pete im Oktober geliefert haben.
Beispiel 1. SUMIFS + SUMIFS
Die durch diesen Ansatz erzeugte Formel enthält viele Wiederholungen und Looks umständlich, aber leicht zu verstehen und funktioniert vor allem 🙂
Beispiel 2. SUM & SUMIFS mit einem Array-Argument
Ich habe versucht, das Wesentliche dieses Ansatzes im SUMIF-Beispiel zu erklären. Jetzt können wir diese Formel einfach kopieren, die Reihenfolge der Argumente ändern (wie Sie sich erinnern, unterscheidet sich SUMIF und SUMIFS) und zusätzliche Kriterien hinzufügen Die resultierende Formel ist kompakter als SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
Das von dieser Formel zurückgegebene Ergebnis ist genau das gleiche wie in der Screenshot oben.
Beispiel e 3. SUMPRODUCT & SUMIFS
Wie Sie sich erinnern, unterscheidet sich der SUMPRODUCT-Ansatz von den beiden vorherigen darin, dass Sie jedes Ihrer Kriterien in eine separate Zelle eingeben eher, dass sie direkt in der Formel angeben. Bei mehreren Kriteriensätzen reicht die Funktion SUMPRODUCT nicht aus, und Sie müssen auch ISNUMBER und MATCH verwenden.
Unter der Annahme, dass sich die Verbrauchsmaterialnamen in den Zellen H1: H3, das Startdatum in Zelle H4 und das Enddatum in Zelle H5 befinden, hat unsere SUMPRODUCT-Formel die folgende Form:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Viele Leute fragen sich, warum in SUMPRODUCT-Formeln der doppelte Bindestrich (-) verwendet wird. Der Punkt ist, dass Excel SUMPRODUCT alle außer numerischen Werten ignoriert, während die Vergleichsoperatoren in unserer Formel Boolesche Werte (TRUE / FALSE) zurückgeben, die nicht numerisch sind. Um diese Booleschen Werte in 1 „s und 0“ s zu konvertieren, verwenden Sie das doppelte Minuszeichen, das technisch als doppelter unärer Operator bezeichnet wird. Das erste Unary zwingt TRUE / FALSE zu -1/0. Das zweite Unary negiert die Werte, d. H. Kehrt das Vorzeichen um und wandelt sie in +1 und 0 um, was die SUMPRODUCT-Funktion verstehen kann.
Ich hoffe, die obige Erklärung ist sinnvoll. Und selbst wenn dies nicht der Fall ist, denken Sie an diese Faustregel: Verwenden Sie den doppelten unären Operator (-), wenn Sie Vergleichsoperatoren in Ihren SUMPRODUCT-Formeln verwenden.
Verwenden von Excel SUM in Array-Formeln
Wie Sie sich erinnern, hat Microsoft die SUMIFS-Funktion in Excel 2007 implementiert. Wenn noch jemand Excel 2003, 2000 oder früher verwendet, müssen Sie eine SUM-Array-Formel verwenden, um Werte mit mehreren UND-Kriterien hinzuzufügen. Natürlich dieser Ansatz funktioniert auch in modernen Versionen von Excel 2013 – 2007 und kann als altmodisches Gegenstück zur SUMIFS-Funktion angesehen werden.
In den oben beschriebenen SUMIF-Formeln haben Sie bereits Array-Argumente verwendet, jedoch eine Array-Formel ist etwas anderes.
Beispiel 1. Summe mit mehreren UND-Kriterien in Excel 2003 und früher
Kehren wir zum allerersten Beispiel zurück, in dem wir eine Summe der zugehörigen Beträge herausgefunden haben an eine bestimmte Frucht und einen bestimmten Lieferanten:
Wie Sie bereits wissen, ist diese Aufgabe mit einem leicht zu erledigen gewöhnliche SUMIFS-Formel:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Nun wollen wir sehen, wie dieselbe Aufgabe in frühen „SUMIFS-freien“ Versionen von Excel erfüllt werden kann . Zunächst notieren Sie alle Bedingungen, die erfüllt sein sollten, in Form von range = „condition“. In diesem Beispiel haben wir zwei Bereich / Bedingungspaare:
Bedingung 1: A2: A9 = „Äpfel“
Bedingung 2: B2: B9 = „Pete“
Dann schreiben Sie eine SUMMENformel, die alle Ihre Bereichs- / Bedingungspaare „multipliziert“, die jeweils in Klammern stehen. Der letzte Multiplikator ist der Summenbereich, C2: C9 in unserem Fall:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Wie im folgenden Screenshot dargestellt, die Formel Funktioniert perfekt in der neuesten Excel 2013-Version.
Beispiel 2. SUM-Array-Formeln in modernen Excel-Versionen
Even In modernen Versionen von Excel sollte die Leistung der SUM-Funktion nicht unterschätzt werden. Die SUM-Array-Formel ist nicht nur eine Gymnastik des Geistes, sondern hat einen praktischen Wert, wie im folgenden Beispiel gezeigt.
Angenommen Sie haben zwei Spalten, B und C, und Sie müssen zählen, wie oft Spalte C größer als Spalte B ist, wenn ein Wert in Spalte C größer oder gleich 10 ist. Eine sofortige Lösung, die Ihnen in den Sinn kommt, ist die Verwendung der SUMME Array-Formel:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Sehen Sie kein praktisches Anwendung auf die obige Formel? Denken Sie anders darüber nach 🙂
Angenommen, Sie haben die Bestellliste wie im folgenden Screenshot gezeigt und möchten wissen, wie viele Produkte bis zu einem bestimmten Datum nicht vollständig geliefert wurden. In die Sprache von Excel übersetzt haben wir die folgenden Bedingungen:
Bedingung 1: Ein Wert in Spalte B (Bestellte Artikel) ist größer als 0
Bedingung 2: Ein Wert in Spalte C (geliefert) in weniger als in Spalte B
Bedingung 3: Ein Datum in Spalte D (Fälligkeitsdatum) ist kleiner als der 01.11.2014.
Setzen der drei Bereiche / Bedingungspaare zusammen erhalten Sie die folgende Formel:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Nun, die in diesem Tutorial beschriebenen Formelbeispiele haben nur die Oberfläche der Funktionen von Excel SUMIFS und SUMIF zerkratzt. Hoffentlich haben sie Ihnen dabei geholfen, in die richtige Richtung zu weisen, und jetzt können Sie Werte in Ihren Excel-Arbeitsmappen summieren, unabhängig davon Wie viele komplizierte Bedingungen müssen Sie berücksichtigen?
- Verwendung von SUMIF in Excel – Formelbeispiele zum bedingten Summieren von Zellen
- Verwendung von Excel SUMIFS und SUMIF mit mehreren Kriterien – Formelbeispiele
- Verwendung von VLOOKUP & SUM- oder SUMIF-Funktionen in Excel
- SUMIF in Google Sheets mit Formelbeispielen
- COUNTIF in Excel – Anzahl, wenn nicht leer, größer als, doppelt oder eindeutig
- Zählen und Summieren von Zellen nach Farbe in Excel