Zusammenfassung
Um in Spalten nach Monat zu summieren, können Sie die Funktion SUMIFS zusammen mit der Funktion EOMONTH verwenden. Im gezeigten Beispiel lautet die Formel in G5:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Diese Formel verwendet drei benannte Bereiche: "Betrag" (D5: D15), "Kunde" (B5: B15) und "Datum" (C5: C15).
Erläuterung
Die SUMIFS-Funktion dient zum Summieren von Werten in einem Bereich, der auf einem oder mehreren Kriterien basiert. In diesem Fall benötigen wir drei Kriterien:
- Clientname = Client in Spalte F.
- Datum> = erster Monat (ab Datum in Zeile 4)
- Datum <= Monatsende (ab Datum in Zeile 4)
Entscheidender Punkt: Die Monatsnamen in G4: I4 sind tatsächlich gültige Daten, die mit dem benutzerdefinierten Zahlenformat "mmm" formatiert sind. Dies ermöglicht es uns, die Datumswerte in G4: I4 direkt für die obigen Kriterien 2 und 3 zu verwenden.
Zurück in SUMIFS wird das erste Bereich / Kriterien-Paar verwendet, um den Client-Namen zu überprüfen:
client,$F5
Hinweis F5 ist eine gemischte Referenz, bei der die Spalte gesperrt ist. Dadurch kann sich die Zeile ändern, wenn die Formel durch die Tabelle kopiert wird. Der Clientname stammt jedoch immer aus Spalte F.
Das zweite Bereich / Kriterien-Paar wird verwendet, um Daten mit dem ersten des Monats zu vergleichen:
date,">="&G$4
Wie oben erwähnt, sind die Werte in G4: I4 tatsächlich Daten: 1. Januar 2019, 1. Februar 2019 und 1. März 2019. Bei diesem Kriterium wird also einfach nach Daten gesucht, die größer sind als die Daten des ersten Monats in Zeile 4. Beachten Sie dies Dies ist eine weitere gemischte Referenz, diesmal mit gesperrter Zeile. Dadurch kann sich die Spalte ändern, wenn die Formel über die Tabelle kopiert wird, die Zeilennummer bleibt jedoch unverändert. Die Verkettung mit einem kaufmännischen Und (&) ist erforderlich, wenn Kriterien erstellt werden, die einen logischen Operator und einen Wert aus einer anderen Zelle verwenden.
Das dritte Bereich / Kriterien-Paar wird verwendet, um Daten mit dem letzten Tag des Monats zu vergleichen:
date,"<="&EOMONTH(G$4,0)
Um den letzten Tag eines jeden Monats abzurufen, verwenden wir die EOMONTH-Funktion am Datum aus der Spaltenüberschrift in Zeile 4. Wenn für das Argument month die Null angegeben ist, gibt EOMONTH den letzten Tag desselben Monats zurück. Auch hier wird der Verweis auf G4 gemischt, um zu verhindern, dass sich die Zeile ändert.
Pivot Table-Lösung
Eine Pivot-Tabelle wäre eine hervorragende Lösung für dieses Problem, da sie automatisch ohne Formeln nach Monaten gruppiert werden kann. In diesem Video finden Sie einen direkten Vergleich von Formeln und Pivot-Tabellen: Warum Pivot-Tabellen.
