Excel – SUMME über Bereich – AGGREGAT und INDEX

Es soll die Summe eines Bereichs gebildet werden. Der Bereich wird allerdings über zwei Markierungen neben der Tabelle definiert. Von der Zeile der ersten x-Markierung bis zur Zeile der zweiten x-Markierung soll summiert werden. Der Clou ist also, dass sich der Bereich für die Summenfunktion dynamisch zusammensetzen soll. Statt aber nun zum Beispiel =SUMME(B7:B12) einzugeben, werden die Zeilen für B7, also 7, und B12, also Zeile 12, über die Funktion INDEX ermittelt.

E361

=SUMME(INDEX(B:B;AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1);1):INDEX(B:B;AGGREGAT(14;4;ZEILE(C2:C32)*(C2:C32="x");1);1))

Dabei muss die Position, also die Zeile, des oberen x-Wertes ermittelt werden. Dies geschieht durch die Funktion: AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1). Nur in den Zellen, in denen ein x steht, wird auch durch WAHR geteilt – also in diesem Fall durch 1. Es werden also alle Zeilennummern des Bereichs genommen und durch 1 oder 0 geteilt. Von diesen Zeilennummern nimmt AGGREGAT(15;6;Array;1) die kleinste. Im Beispiel resultiert die Zahl 7.

Die Zeile des unteren x-Wertes wird durch AGGREGAT(14;4;ZEILE(C2:C32)*(C2:C32="x");1) ermittelt. Auch hier wird geprüft, ob ein x in einer der Zellen steht. Falls dies mit WAHR beantwortet werden kann, wird mit WAHR, also mit 1 multipliziert. Das Ergebnis ist eine List von Zeilennummern, die entweder mit 1 oder mit 0 bei FALSCH multipliziert werden. Von diesen Werten nimmt AGGREGAT(14;4;Array;1) den größten Wert. Im Beispiel resultiert die Zahl 12.

Statt nun B7:B12 in die Funktion SUMME als Bereich einzugeben, werden die Koordinaten über INDEX ermittelt. INDEX(B:B;AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1);1) führt also zu INDEX(B:B;7;1) und dies wiederum zur Zelle B7. Es wird die siebte Zelle aus Spalte B genommen. Das gleiche Prinzip gilt für B12. Die beiden INDEX-Funktionen werden über einen Doppelpunkt verbunden, so dass im Endeffekt B7:B12 resultiert.

Sollte kein x gesetzt sein, resultiert die Fehlermeldung #Zahl!
Hier könnte man die Formel noch einmal um die Funktion WENNFEHLER ergänzen:

=WENNFEHLER(SUMME(INDEX(B:B;AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1);1):INDEX(B:B;AGGREGAT(14;4;ZEILE(C2:C32)*(C2:C32="x");1);1));"")

Die Datei zum Download:
E361_SUMME_AGGREGAT_BEREICH

Das Video bei YouTube: