Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie die SUMIFS-Funktion verwenden, um Daten zu summieren, die bestimmten Monaten in Excel und Google Sheets entsprechen.
Summe wenn nach Monat
Zuerst zeigen wir, wie Daten summiert werden, die Daten entsprechen, die in einen bestimmten Monat und ein bestimmtes Jahr fallen.
Wir können die SUMIFS-Funktion zusammen mit den DATE-, YEAR-, MONTH- und EOMONTH-Funktionen verwenden, um die Anzahl der Verkäufe innerhalb jeden Monat.
| 1 | =SUMIFS(C3:C9,B3:B9,">="&DATUM(YEAR(E3),MONTH(E3),1),B3:B9,"<="&EOMONTH(E3,0)) |
Um die obige Formel zu erstellen, definieren wir zunächst den Datumsbereich für jeden Monat. Wir verwenden die DATE-Funktion, um den ersten Tag des Monats zu definieren (z. B. 01.05.2021). Wir können dies tun, indem wir das Datum in die Formel „hartkodieren“:
| 1 | ">="&DATUM(2021,5,1) |
Oder noch besser, wir können die Kriterien flexibel gestalten, indem wir auf ein Datum in der Zelle E3 verweisen, das Jahr und den Monat des Datums extrahieren (und den Tag gleich 1 setzen), wie im obigen Beispiel gezeigt:
| 1 | ">="&DATUM(JAHR(E3),MONAT(E3),1) |
Um den letzten Tag des Monats zu definieren, können wir die EOMONTH-Funktion verwenden:
| 1 | "<="&EOMONTH(E3,0) |
Wenn wir all diese Kriterien zusammenfassen, können wir die folgende SUMIFS-Formel schreiben:
| 1 | =SUMIFS(C3:C9,B3:B9,">="&DATUM(YEAR(E3),MONTH(E3),1),B3:B9,"<="&EOMONTH(E3,0)) |
Zellenreferenzen sperren
Um unsere Formeln leichter lesbar zu machen, haben wir die Formeln ohne gesperrte Zellbezüge gezeigt:
| 1 | =SUMIFS(C3:C9,B3:B9,">="&DATUM(YEAR(E3),MONTH(E3),1),B3:B9,"<="&EOMONTH(E3,0)) |
Diese Formeln funktionieren jedoch nicht richtig, wenn Sie sie an anderer Stelle in Ihrer Datei kopieren und einfügen. Stattdessen sollten Sie gesperrte Zellbezüge wie folgt verwenden:
| 1 | =SUMIFS($C$3:$C$9,$B$3:$B$9,">="&DATUM(JAHR(E3),MONAT(E3),1),$B$3:$B$9,"<=" &EUMONAT(E3,0)) |
Lesen Sie unseren Artikel zum Sperren von Zellreferenzen, um mehr zu erfahren.
Formatieren von Monatswerten
In diesem Beispiel haben wir Monate in Spalte E aufgelistet. Diese Monatswerte sind eigentlich Datumsangaben, die so formatiert sind, dass der Tag mit benutzerdefinierter Zahlenformatierung weggelassen wird.
Das benutzerdefinierte Datenformat ist "mmm yyyy", um Mai 2022 anzuzeigen.
Summe nach Monat über mehrere Jahre
Im obigen Beispiel wurden Daten mit Datumsangaben zusammengefasst, die in einen bestimmten Monat und ein bestimmtes Jahr fielen. Stattdessen können Sie mit der SUMPRODUCT-Funktion Daten mit Daten summieren, die innerhalb eines Monats in einem beliebigen Jahr liegen.
| 1 | =SUMMENPRODUKT(C3:C8,--(MONAT(B3:B8)=MONAT(G3))) |
In diesem Beispiel verwenden wir die SUMPRODUCT-Funktion, um komplizierte „Summe if“-Berechnungen durchzuführen. Gehen wir die Formel durch.
Dies ist unsere endgültige Formel:
| 1 | =SUMMENPRODUKT(C3:C8,--(MONAT(B3:B8)=MONAT(G3))) |
Zuerst listet die SUMPRODUCT-Funktion die Anzahl der Verkäufe für jeden Verkaufsdatum und vergleicht dann den Monat von jedem Verkaufsdatum gegen die angegebenen Monat, gibt TRUE zurück, wenn die Monate übereinstimmen, oder FALSE, wenn sie nicht übereinstimmen:
| 1 | =SUMMENPRODUKT({30;42;51;28;17;34},--({WAHR;FALSCH;WAHR;FALSCH;WAHR;FALSCH})) |
Als nächstes wandeln die Doppelstriche (-) die TRUE- und FALSE-Werte in 1s und 0s um:
| 1 | =SUMMENPRODUKT({30;42;51;28;17;34},{1;0;1;0;1;0}) |
Die SUMPRODUCT-Funktion multipliziert dann jedes Paar von Einträgen in den Arrays:
| 1 | =SUMMENPRODUKT({30;0;51;0;17;0}) |
Schließlich werden die Zahlen im Array summiert:
| 1 | =98 |
Weitere Details zur Verwendung von Booleschen Anweisungen und dem Befehl „-“ in einer SUMPRODUCT-Funktion finden Sie hier
Summe, wenn nach Monat in Google Tabellen
Diese Formeln funktionieren in Google Sheets genauso wie in Excel.
