Kevin muss eine Formel erstellen, die jede vierte Zelle in Folge summiert. Er weiß, dass er eine Formel wie = A6 + E6 + I6 + M6 usw. verwenden kann, aber dies wird umständlich, wenn das Arbeitsblatt viele Spalten enthält.

Es gibt verschiedene Möglichkeiten, wie Sie dieses Problem angehen können. Eine Möglichkeit besteht darin, dem Arbeitsblatt einige zusätzliche Informationen hinzuzufügen, um festzulegen, welche Zellen in der Summe enthalten sein sollen. In diesem Beispiel möchten Sie beispielsweise die Zellen in Zeile 6 des Arbeitsblatts summieren. Wenn Sie in Zeile 5 einige Indikatoren hinzufügen können, können diese als „Trigger“ in einer Formel verwendet werden. Setzen Sie beispielsweise die Nummer 1 über jede Zelle, die in der Summe enthalten sein soll (Spalten A, E, I, M usw.). Dann können Sie eine Formel wie die folgende verwenden:

=SUMPRODUCT(A5:X5, A6:X6)

Die Formel multipliziert grundsätzlich alles, was in Zeile 5 steht, mit Zeile 6 und summiert dann die Ergebnisse. Da die zu summierenden Spalten nur 1s enthalten, sind dies alles, was in der endgültigen Summe enthalten ist.

Wenn Sie Ihrem Arbeitsblatt keine Indikatorzeile hinzufügen möchten, müssen Sie sich verschiedene Lösungen ansehen. Sie können die SUMPRODUCT-Funktion weiterhin in einer Formel wie der folgenden verwenden:

=SUMPRODUCT((MOD(COLUMN(6:6),4)=1)*(6:6))

Diese Formel basiert auf der MOD-Funktion, um den Rest einer Division zurückzugeben. In diesem Fall wird die Spaltennummer einer Zelle durch den Wert 4 geteilt. Dies führt zu einem Rest von entweder 0, 1, 2 oder 3. Jede vierte Zelle in einer Zeile hat den gleichen Rest. Somit hat Spalte A (auch als Spalte 1 bekannt) einen MOD-Wert von 1 (1 geteilt durch 4 ist 0, wobei 1 übrig bleibt), ebenso wie Spalten E, I, M usw.

Beachten Sie, dass die Formel vergleicht, ob der MOD-Wert 1 ist oder nicht. Wenn dies der Fall ist, gibt der Vergleich True (1) zurück. Ist dies nicht der Fall, wird False (0) zurückgegeben. Dies wird dann mit der Zelle in der sechsten Reihe multipliziert.

Schließlich summiert SUMPRODUCT alle diese Multiplikationen und liefert das gewünschte Ergebnis.

Während diese Formel die Summe jeder vierten Zelle in der sechsten Zeile liefert, kann sie leicht geändert werden, um die Summe für jede dritte Zelle, fünfte Zelle oder ein beliebiges Intervall bereitzustellen. Ändern Sie einfach die 4 in der MOD-Funktion auf das gewünschte Intervall.

Wenn Sie in jedem „Cluster“ von vier zu summierenden Zellen eine andere Zelle auswählen möchten, müssen Sie lediglich den zu vergleichenden Wert in der MOD-Funktion ändern. In diesem Beispiel hat nur die erste Zelle in jedem Vierercluster einen MOD von 1 (A, E, I, M usw.). Wenn Sie stattdessen jede vierte Zelle summieren möchten, beginnend beispielsweise mit Zelle C, würden Sie den Vergleichswert von 1 auf 3 ändern. Warum? Weil C die dritte Zelle im Cluster ist und einen MOD von 3 hat, ebenso wie jede vierte Zelle danach (G, K, O usw.).

Das einzige „Gottcha“ dieser allgemeinen Regel ist, wenn Sie die vierte Zelle in jedem Cluster mit vier Zellen summieren möchten. Beispielsweise möchten Sie möglicherweise die Zellen D, H, L, P usw. summieren. In diesem Fall wäre der verwendete Vergleichswert nicht 4, da bei einer MOD-Operation, bei der durch 4 geteilt wird, niemals ein Rest von 4 angezeigt wird Stattdessen wäre der Vergleichswert 0, wie im Folgenden:

=SUMPRODUCT((MOD(COLUMN(6:6),4)=0)*(6:6))

Wenn Sie lieber mit Array-Formeln arbeiten, können Sie eine etwas kürzere Variante der obigen Formel verwenden:

=SUM(IF(MOD(COLUMN(6:6),4)=1,6:6))

Beachten Sie, dass die Formel durch Drücken von Strg + Umschalt + Eingabetaste eingegeben werden sollte.

Es wird dann in der Formelleiste mit geschweiften Klammern (\ {}) um die Formel angezeigt. Hier gelten die gleichen Änderungshinweise zum MOD-Divisor und zum Vergleichswert wie bei der Funktion SUMPRODUCT.

Beide formelhaften Ansätze (SUMPRODUCT und die Array-Formel)

summiere jede vierte Zelle in der gesamten Reihe. Wenn Sie stattdessen die Zellen, aus denen die Summe abgeleitet wird, auf einen Teil der Zeile beschränken möchten, ersetzen Sie einfach 6: 6 (beide Instanzen) durch den richtigen Bereich. Wenn Sie also nur jede vierte Zelle im Bereich von A6: Z6 summieren möchten, würden Sie diesen Bereich in der Formel verwenden.

Wenn Sie auf diese Weise viel summieren und diese nicht nur auf Bereiche in einer Zeile, sondern auch auf Bereiche in einer Spalte anwenden, sollten Sie eine benutzerdefinierte Funktion erstellen, um die Summierung durchzuführen. Die folgende einfache Funktion erledigt den Trick:

Function SumEveryFourth(MyRange As Range)

Dim x As Integer     SumEveryFourth = 0     For x = 1 To MyRange.Cells.Count         If (x Mod 4) = 1 Then             SumEveryFourth = SumEveryFourth + MyRange.Cells(x).Value         End If     Next x End Function

Die Funktion untersucht den an sie übergebenen Bereich und summiert dann jede vierte Zelle beginnend mit der ersten Zelle im Bereich. Wenn Sie es vorziehen, jede zweite Zelle im Bereich zu summieren, ändern Sie den Vergleichswert in der If-Anweisung, wie weiter oben in diesem Tipp erläutert. (Da die Mod-Operation in dieser Funktion verwendet wird und dieselbe Funktion wie die MOD-Arbeitsblattfunktion ausführt, spielen dieselben Vergleichswerte eine Rolle, um zu bestimmen, welche Zelle in jedem Cluster summiert werden soll.)

Die benutzerdefinierte Funktion funktioniert entweder für Zellen in einer Zeile oder für Zellen in einer Spalte einwandfrei. Sie müssen lediglich sicherstellen, dass Sie den gewünschten Bereich überschreiten, wie hier gezeigt:

=SumEveryFourth(C3:C57)

Falls gewünscht, können Sie das Makro noch flexibler gestalten, indem Sie den Wert „step“ zu einer Variablen machen, die an die Funktion übergeben wird. Dieses geänderte Makro könnte dann verwendet werden, um jede 2, 3, 4, 5 usw. Zelle zu summieren.

Function SumEveryNth(MyRange As Range, N As Integer)

Dim x As Integer     SumEveryFourth = 0     For x = 1 To MyRange.Cells.Count         If (x Mod N) = 1 Then             SumEveryNth = SumEveryNth + MyRange.Cells(x).Value         End If     Next x End Function

Diese Funktion wird wie die vorherige Funktion aufgerufen, mit der Ausnahme, dass Sie den gewünschten Schrittwert hinzufügen. Dies führt beispielsweise dazu, dass jede dritte Zelle im Bereich C3: C57 summiert wird:

=SumEveryNth(C3:C57, 3)

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (3234) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier:

Summiert jede vierte Zelle in einer Reihe.