Excel - Formeln und Funktionen - Tipp Nr. 1695

Nur jede 2.(3.) Zeile einer Spalte addieren

Tipp getestet unter Excel 2003, 2007, 2010, 2013;

Immer wieder müssen wir Excel Listen auswerten, die nicht "bearbeitungsfreundlich" erstellt wurden. Hier eine besonders "bösartige" Datenbasis für eine Summenbildung:

• Untereinander sind jeweils Artikelnummer, Datum und Menge aufgelistet. Es gilt die Summe der Mengen zu bilden. Wenn Sie in B32 die Formel

  =SUMME(B1:B31)

  verwenden, werden nicht nur die "Mengen", sondern auch die Werte von "Artikel" und "Datum" addiert.

Lösung 1: SUMMEWENN

• Vorausgesetzt die Bezeichnung "Menge" in Spalte A ist in allen Mengenzeichen exakt gleich, geht es recht einfach mit der Funktion SUMMEWENN:

  =SUMMEWENN(A1:A31;"Menge";B1:B31)

Lösung 2: Matrixformel und Funktion REST

Sobald aber für die Bezeichnung der "Menge" verschiedene Begriffe verwendet werden, wird es kompliziert:

1. Die Funktion ZEILE gibt die jeweis aktive Zeilennummer zurück.
2. Mit REST(Dividend;Divisor) ermitteln Sie die wievielte Zeile (im Beispiel: Divisor 3 = jede 3. Zeile) es auszuwerten gilt.
3. Die Bedingung der WENN-Funktion legt über den gesuchten Divisionsrest fest, welche Zeilen summiert werden sollen (0=Menge, 1=Artikel, 2=Datum, wobei in unserem Beispiel natürlich nur 0 sinnvolle Ergebnisse liefert).
4. Gesucht ist die SUMME dieser Ausdrücke, daher:

   =SUMME(WENN(REST(ZEILE(B1:B31);3)=2;B1:B31;""))

5. Das Ganze ist dann auch noch eine Matrixformel, da Bereiche als Argumente dienen ("B1:B31"). Sie müssen die Formeleingabe daher mit + + abschließen.
   Excel setzt die Formel dann automatisch in geschwungene Klammern:

   {=SUMME(WENN(REST(ZEILE(B1:B31);3)=2;B1:B31;""))}

6. Wenn Sie vor der Ergebniszeile (32) eine Leerzeile (31) in die Formel mit einbeziehen, erweitert sich der Formelbereich automatisch, wenn Sie neue Zeilen einfügen.