Über diese Übung
Ein Datensatz mit 300 Zeilen auf einem Tabellenblatt, 3 Antwortzellen, etwa 10 Minuten. Bewertet in Ihrem Browser: der Wert und die Formel dahinter, mit Feedback, das die Korrektur benennt.
So lösen Sie die Aufgabe
- G2
=SUMIFS($D$2:$D$301,$C$2:$C$301,$F2,$B$2:$B$301,">="&G$1,$B$2:$B$301,"<"&EDATE(G$1,1)) - M2
=SUM(G2:L2) - G7
=SUM(G2:G6)
A PivotTable that groups dates by Months puts every order into the calendar month it falls in, from the 1st to the last day. The SUMIFS rebuilds that bucket with two conditions on the same date column — ">="&G$1 (on or after the month's 1st) and "<"&EDATE(G$1,1) (before the next month's 1st) — plus the category condition. Mixed references let one formula fill all 30 cells, and the grand totals reconcile to SUM(D2:D301) because every order lands in exactly one cell.
The PivotTable route in Excel: Insert → PivotTable, Category to Rows, Order date to Columns, Revenue to Values (Sum). Excel 2016 and later group a date field automatically; to set the grouping yourself, right-click a date → Group → Months (the labels then read Jan, Feb, …; select Years as well when the data spans more than one year, or January of both years lands in one column). The pivot needs Refresh after new data; these formulas recalculate by themselves, as long as a new export fits in rows 2–301 (for a longer one, extend the ranges or keep the data in an Excel Table). Pitfall 1: the bounds. ">"&G$1 drops every order of the 1st (the deal day), "<="&EDATE(G$1,1) counts the next month's 1st in two months, "<"&EOMONTH(G$1,0) drops the last day (the B2B invoices). Pitfall 2: timestamps. An order at 31 Jan 14:30 is later than EOMONTH(G$1,0), which is midnight, so "<="&EOMONTH(…) misses it, while "<"&EDATE(G$1,1) is safe with times too. Pitfall 3: text headers such as "Jan" cannot be compared with dates; with real dates in row 1 the same formula works for any month and year, and changing G1 re-targets its whole column. In Excel 365, =SUMIFS(D2:D301,C2:C301,F2:F6,B2:B301,">="&G1:L1,B2:B301,"<"&EDATE(+G1:L1,1)) in G2 spills the whole grid from one cell — the + in front of G1:L1 matters, because Excel's EDATE rejects a plain range with #VALUE! but accepts an array. PIVOTBY builds a cross-tab with its own headers (=PIVOTBY(C2:C301,TEXT(B2:B301,"yyyy-mm"),D2:D301,SUM)); this practice grid does not support PIVOTBY.
Andere Wege, die funktionieren
- =SUMIFS($D$2:$D$301,$C$2:$C$301,$F2,$B$2:$B$301,">="&G$1,$B$2:$B$301,"<="&EOMONTH(G$1,0)) — the month end as the upper bound (fine while the dates carry no times)
- =SUMPRODUCT(($C$2:$C$301=$F2)*(TEXT($B$2:$B$301,"yyyy-mm")=TEXT(G$1,"yyyy-mm"))*$D$2:$D$301) — compare year and month as text
- =SUMIFS(D2:D301,C2:C301,F2:F6,B2:B301,">="&G1:L1,B2:B301,"<"&EDATE(+G1:L1,1)) in G2 — Excel 365, one formula spills the whole grid (keep the + before G1:L1)
- =SUMIFS($D$2:$D$301,$C$2:$C$301,$F2) in M2 — a category total straight from the data instead of adding up the row
Häufige Fehler
Writing ">"&G$1 instead of ">="&G$1: every order placed on the 1st — the monthly deal day — drops out of its month, so G2 already misses part of the January Accessories revenue.
So bewerten wir
Jede Formel, die das richtige Ergebnis liefert, wird akzeptiert, außer die Aufgabe nennt eine Funktion: Dann bringt eine andere Funktion die halbe Punktzahl. Wir vergleichen Werte mit einer kleinen Toleranz und ignorieren bei Text Groß- und Kleinschreibung sowie Leerzeichen. Eine richtige Zahl, die statt einer Formel von Hand eingetippt wurde, bringt die halbe Punktzahl. Aufgaben mit mehreren Zellen bringen die volle Punktzahl, wenn 100% der Zellen stimmen, und die halbe ab 50%. Wo es darauf ankommt, ändern wir außerdem eine Eingabezelle in einer Kopie Ihrer Tabelle und prüfen, ob Ihre Formel mitzieht.
Antworten nach den Regeln von Excel 365 berechnet und am 20. November 2026 in unserem Bewertungssystem geprüft. Verfasst von GridBelt team.