Beitrag aus SmartTools Excel Weekly
Zeilensumme bis zu einer durch die Überschrift bestimmten Spalte berechnen
Excel 365 2024 2021 2019 2016 2013
FRAGE Ich habe eine Tabelle angelegt, in der ich als Spaltenüberschriften die Monatsnamen "Januar" bis "Dezember" verwende. Darunter stehen die Kosten, die in den Monaten angefallen sind. Rechts neben diesen Monatszahlen möchte ich pro Zeile die Summe der Kosten bis zu einem bestimmten Monat berechnen. Den Monatsnamen gebe ich immer in Zelle N1 ein. Wenn ich dort zum Beispiel "März" eingebe, soll Excel nur die Kosten bis zur Märzspalte ausgeben. Können Sie mir sagen, wie ich eine Summenformel aufstellen kann, die vom Wert in Zelle N1 abhängig ist?
Diverse Anfragen
ANTWORT Sie müssen in der Formel eine Funktion einsetzen, die die Position des Monatsnamens innerhalb der Spaltenüberschriften ermittelt. Dann wissen Sie, wie viele Spalten in der Summenformel berücksichtigt werden sollen. Dies leistet die Tabellenfunktion VERGLEICH. Zur Angabe des Bereichs, der addiert werden soll, eignet sich dann am besten die Tabellenfunktion BEREICH.VERSCHIEBEN.
Die vollständige Summenformel hat folgendes Schema:
=SUMME(BEREICH.VERSCHIEBEN([erste zu addierende
Zelle];0;0;1;VERGLEICH($[Kriterienzelle];
$[Bereich der Spaltenüberschriften];0)))
Nehmen Sie dazu folgendes Beispiel: Die Spaltenüberschriften "Januar" bis "Dezember" stehen im Bereich A1:L1. Direkt darunter in A2:L2 sowie in weiteren Zeilen haben Sie die Kosten für die einzelnen Monate eingegeben. Außerdem tragen Sie in Zelle N1 den Monatsnamen als Kriterium für die Summenformel ein.
Wenn Sie dann für die zweite Zeile die Summe aller Kosten bis zu dem in N1 eingegebenen Monat berechnen wollen, geben Sie beispielsweise in N2 folgende Formel ein:
=SUMME(BEREICH.VERSCHIEBEN(A2;0;0;1;
VERGLEICH($N$1;$A$1:$L$1;0)))
VERGLEICH sucht im Bereich A1:L1 (= in den Spaltenüberschriften) nach der Position des Monatsnamens in N1. Die absoluten Bezüge sind sinnvoll, wenn Sie die Formel später in andere Zellen kopieren wollen. Wenn N1 etwa den Wert "März" enthält, ist das Ergebnis die Zahl "3".
BEREICH.VERSCHIEBEN definiert anschließend einen Bereich, der in Zelle A2 (= die erste zu addierende Zelle in der aktuellen Zeile) beginnt. Die Höhe des Bereichs beträgt "1" (Zeile) und die Breite entspricht dem Ergebnis der VERGLEICH-Funktion - im Beispielfall also "3" (Spalten). Somit addiert die Summenfunktion die ersten 3 Spalten in Zeile 2.