Excel: Zellen in gefilterten Listen zählen

Problem:

Sie möchten ermitteln, in wie vielen Zellen einer Spalte ein Eintrag steht. Beispielsweise haben Sie in einer Spalte vermerkt, ob eine Rechnung schon bezahlt ist. Sie möchten herausfinden, bei wie vielen Rechnungen das der Fall ist. Das Ganze soll aber auch funktionieren, wenn die Liste gefiltert ist, etwa wenn Sie wissen wollen, wie viele Rechnungen bestimmte Kunden bereits bezahlt haben.

Lösung:

Solange die Liste nicht gefiltert ist, funktioniert das Zählen mit der Funktion ANZAHL2. So können Sie also etwa mit =ANZAHL2(B2:B200) die Anzahl der bezahlten Rechnungen ermitteln, die in der Spalte B einen entsprechenden Eintrag erhalten haben.

Gefilterte Liste: Sobald Sie Zeilen ausblenden, erfüllt das Ergebnis nicht mehr die Anforderungen. Excel zählt nämlich auch die ausgeblendeten Zeilen mit, sofern sie in Spalte B einen Wert enthalten. Verwenden Sie in diesem Fall die Funktion TEILERGEBNIS, die sehr vielseitig und dadurch etwas unübersichtlich ist. Sie verwendet Funktionen wie SUMME, MITTELWERT, MAX oder MIN, berücksichtigt allerdings nur die sichtbaren Zellen. In der Formel selbst tauchen diese Funktionen allerdings nicht mit ihren Namen, sondern nur mit Nummern auf. Für unser Beispiel benötigen wir die Funktion ANZAHL2, die die Nummer 3 trägt. Verwenden Sie also die Formel =TEILERGEBNIS(3; B2:B200), um die bezahlten Rechnungen in den sichtbaren Zellen einer gefilterten Liste zu zählen.

2. Problem: Sie möchten Daten in einer gefilterten Liste zählen. Mit der Formel des vorherigen Tipps kommen Sie nicht ans Ziel. Die Spalte enthält nämlich verschiedene Einträge, von denen Sie nur einen bestimmten berücksichtigen wollen. Beispielsweise möchten Sie ermitteln, wie viele Rechnungen einer bestimmten Abteilung zuzuordnen sind.

Lösung: Für diesen Zweck benötigen Sie die Matrixfunktion SUMMENPRODUKT. Damit multiplizieren Sie die einzelnen Elemente zweier Matrizen und addieren diese Ergebnisse. Nützlich ist diese Funktion vor allem, wenn Sie verschiedene Bedingungen miteinander verknüpfen und dann zählen, wie oft diese Bedingungen alle gleichzeitig zutreffen.

Die erste Bedingung: Die Zelle, auf die sich die Berechnung bezieht, muss sichtbar sein. Dazu verwenden Sie die Funktion TEILERGEBNIS (siehe Tipp oben). Für die Matrixfunktion SUMMENPRODUKT benötigen Sie eine Matrix, die für jede sichtbare Zeile den Wert „1“ hat. Unsichtbare Zeilen haben den Wert „0“. Deswegen wenden Sie TEILERGEBNIS jeweils nur auf eine Zelle an. Sie erreichen das mit der Funktion INDIREKT, die aus einem Text einen Zellbezug macht, und der Funktion ZEILE, die die Zeilennummer einer Zelle ermittelt. Die gesamte erste Bedingung lautet demnach TEILERGEBNIS(3;INDIREKT(“B”&ZEILE(B2: B200)))=1

Die zweite Bedingung: In unserem Beispiel müssen Sie als zweite Bedingung überprüfen, ob in der betreffenden Zelle der Name der Abteilung steht, etwa „Einkauf“. Das geht über B2:B200 = “Einkauf” Beide Bedingungen liefern jeweils eine Matrix mit 199 Wahrheitswerten. Diese beiden Bedingungen verbinden Sie zu folgender Funktion: =SUMMENPRODUKT((TEILERGEBNIS(3;INDIRE KT(“B”&ZEILE(B2:B200)))=1)* (B2: B200 = “Einkauf”) * 1) So wird der erste Wert der ersten Matrix mit dem ersten Wert der zweiten Matrix und mit „1“ multipliziert, ebenso nacheinander alle restlichen Werte der Matrix. Diese Multiplikationen ergeben jeweils genau dann den Wert 1, wenn beide Matrixelemente den Wert „wahr“ haben. Deswegen summiert die Funktion SUMMENPRODUKT immer dann den Wert „1“, wenn beide Bedingungen zutreffen, ansonsten wird „0“ dazugezählt. Das Ergebnis ist also die Zahl der Zeilen, in denen beide Bedingungen zutreffen. In unserem Beispiel ermittelt Excel also die Anzahl der Zellen, die sichtbar sind und den Eintrag „Einkauf“ in der Spalte B enthalten.