Beitrag aus SmartTools Excel Weekly
Jede n-te Zelle einer Spalte addieren
Excel 365 2024 2021 2019 2016 2013
FRAGE Sie hatten vor kurzem eine Lösung zum Summieren jeder zweiten Zelle einer Spalte vorgestellt. Die Lösung gefällt mir gut, und ich setze sie auch schon ein. Aber was ist, wenn zum Beispiel jede dritte, vierte oder fünfte Zelle einer Spalte aufsummiert werden soll? Die Formel in Ihrem Beispiel lautete: =SUMMENPRODUKT(C4:C35;--(REST(ZEILE(C4:C35);2)=0)). Wenn ich etwa jede fünfte Zelle addieren will, muss ich dann die "2" durch eine "5" ersetzen? Und muss ich auch noch den Wert hinter dem Gleichheitszeichen anpassen und die "0" durch eine "1", "2", "3" oder "4" ersetzen? Wäre der Einsatz von SUMMEWENN hier nicht einfacher?
S. Bijani
ANTWORT Die Funktion SUMMEWENN scheidet aus, weil sie gewissermaßen nur statische Kriterien auswerten kann. Sie ist nicht in der Lage, für jede Zelle ein variables Kriterium zu berechnen.
Eine weniger komplexe Formel für die Summenbildung jeder n-ten Zelle können Sie nur in Excel 365 und 2024 einsetzen, und zwar unter Anwendung neuer dynamischer Arrayfunktionen. Dazu später mehr.
Hier zunächst eine Erweiterung der Formel aus unserem vorherigen Beispiel, die Sie in allen Excel-Versionen einsetzen können. Die Formel, - die Sie auch in Ihrer Frage erwähnt haben, - summiert im Grunde nicht jede zweite Zelle, sondern eigentlich nur jede Zelle mit einer geraden Zeilennummer. Hier ist der Rest der ganzzahligen Division der Zeilennummer und dem Wert "2" gleich "0". Wenn Sie Zellen in ungeraden Zeilen addieren wollen, müssten Sie hinter dem Gleichheitszeichen den Wert "1" eingeben, weil bei einer ganzzahligen Division der Rest 1 übrigbleibt.
Insofern erfordert das Summieren jeder n-ten Zeile eine etwas komplexere Formel. Grundsätzlich folgt diese Formel aber demselben Prinzip: Sie prüfen darin den Rest einer Division aus Zeilennummer und dem Wert "n" – zum Beispiel "5", wenn Sie jede fünfte Zelle einer Spalte summieren wollen.
Der Rest dieser Division ist für die erste Zelle des Wertebereich aber nicht automatisch "0". Wenn der Wertebereich in Zeile 2 beginnt und Sie jede fünfte Zelle summieren wollen, erhalten Sie für die erste Zelle einen Rest von "2". Je nachdem, in welcher Zeile der Wertebereich beginnt, müssten Sie immer ausprobieren, welche Formel zuverlässig die Summe des 1., 6., 11., 16. usw. Werts liefert.
Mit einer kleinen Formelerweiterung erhalten Sie plausiblere Ergebnisse: Indem Sie die Zeilennummern so versetzen, dass der Wertebereich immer in der n-ten Zeile beginnt, wird bei einem Rest von "0" immer die 1., 6., 11., 16. usw. Zelle addiert (angenommen: n = 5).
Die allgemeingültige Formelsyntax lautet:
=SUMMENPRODUKT([Wertebereich];--(REST(ZEILE([Wertebereich])+
([n]-ZEILE($[erste Zelle]));[n])=0))
Angenommen, der "Wertebereich" befindet sich in C4:C83 und Sie wollten jeden fünften Wert (n = 5) addieren. Die Summe des 1., 6., 11., 16. usw. Werts erhalten Sie dann mit folgender Formel:
=SUMMENPRODUKT(C4:C83;--(REST(ZEILE(C4:C83)+
(5-ZEILE($C$4));5)=0))
Für die Summe des 2., 7., 12., 17. usw. Werts müssen Sie nur den Wert, mit dem das Ergebnis der REST-Funktion verglichen wird, ändern – in "1" statt "0":
=SUMMENPRODUKT(C4:C83;--(REST(ZEILE(C4:C83)+
(5-ZEILE($C$4));5)=1))
Beachten Sie, dass Sie im Formelteil, der den passenden Versatz der ersten Zelle berechnet, mit Hilfe von $-Zeichen einen absoluten Bezug auf die erste Zelle des Wertebereichs verwenden müssen – hier "$C$4". Nur so bleibt der Bezug auf die Startzelle bei jedem Berechnungsschritt erhalten.
Mit den neuen dynamischen Arrayfunktionen von Excel 365 und 2024 lässt sich die Formel für die Summe jeder n-ten Zelle einer Spalte deutlich kompakter gestalten.
Wenn Sie wie oben für einen Wertebereich in den Zellen C4:C83 die Summe jeder fünften Zelle berechnen wollen, genügt in den jüngsten Excel-Versionen folgende Formel:
=SUMME(ZEILENWAHL(SPALTENUMBRUCH(C4:C83;5);1))
Mit dem letzten Formelargument – hier "1" – bestimmen Sie, dass die Summenbildung mit der ersten Zelle beginnt und in diesem Fall mit der 6., der 11., der 16. usw. Zelle fortgesetzt wird. Wenn Sie beim Summieren jeder fünften Zelle mit der zweiten Zelle des Wertebereichs beginnen wollen, verwenden Sie folgende Formel:
=SUMME(ZEILENWAHL(SPALTENUMBRUCH(C4:C83;5);2))
Kurz zur Erläuterung: Die Funktion SPALTENUMBRUCH splittet einen einspaltigen Wertebereich in eine Matrix aus mehreren Spalten auf, wobei nach jedem n-ten Wert eine neue Matrixspalte beginnt. Die Zeilen der Matrix enthalten somit die Werte aus jeder n-ten Zelle des Originalbereichs.
Mit der Funktion ZEILENWAHL bestimmen Sie dann, welche Zeile der SPALTENUMBRUCH-Matrix summiert werden soll. Bei der Suche nach jeder fünften Zelle enthält die erste Zeile der Matrix zum Beispiel die Werte aus der 1., der 6., der 11. usw. Zelle. Die zweite Zeile der Matrix enthält die Werte aus der 2., der 7., der 12. usw. Zelle…
Die Funktion SUMME liefert schließlich die Summe der ausgewählten Zeilenwerte.