Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Zählfunktionen

Beitrag: Zählenwenn in gefilterter Liste und MEDIAN

Aufgabe
Eine Liste ist nach einem Kriterium in Spalte B gefiltert. Nun sollen alle Zellen gezählt werden, die in Spalte C ein bestimmtes Suchkriterium enthalten. Dabei sollen nur die sichtbaren Zellen berücksichtigt werden.
ZÄHLENWENN kann in einer gefilterten Liste nicht zwischen sichtbaren und unsichtbaren Zellen unterscheiden.


Lösung
Liste geht von A1:C1000

Liste ist nach Suchkriterium in Spalte B gefiltert (z.B. 5 größte Elemente)

Gezählt werden sollen alle sichtbaren "X" in Spalte C

=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("C"&ZEILE(2:999)))*(C2:C999="X"))

und den statistischen Median - NICHT Mittelwert - da würde ja =TEILERGEBNIS(1;B1:B999) langen, liefert hier für Spalte B:
{=MEDIAN(WENN(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(1:999)))>0;B1:B999))}



Erläuterung
Bastel Dir erst mal ein kleineres Beispiel bis Zeile 20 und C20, um Formelteile sichtbar zu machen.

Markiere TEILERGEBNIS(3;INDIREKT("C"&ZEILE(2:20))) drücke F9 und Du siehst entweder 0 oder 1 je nachdem, ob die Zeile rausgefiltert ist oder nicht.
Die Funktion TEILERGEBNIS wird also für jede einzelne Zeile angewandt.
Die Einser summiert ist die Anzahl der sichtbaren Einträge in Spalte C.
Da könntest Du auch die Normalformel =TEILERGEBNIS(3;C:C) nehmen.

Du willst aber nur die sichtbaren X zählen.
Markiere (C2:C20="X") drücke F9 und Du siehst entweder FALSCH oder WAHR je nachdem, ob in Spalte C ein X steht oder nicht.

Die Nullen und Einser mit den FALSCH und WAHR multipliziert ergeben 1 nur bei 1*WAHR - siehe:
TEILERGEBNIS(3;INDIREKT("C"&ZEILE(2:20)))*(C2:C20="X") markiert und F9
Die Summe davon sind die sichtbaren X.

Warum INDIREKT("C"&ZEILE(2:20)) - C2:C20 ist doch genau dasselbe ?
Stimmt: F9 drüber und beides ist identisch.
Das ist aber der dirty trick - in Kombination mit Teilergebnis ergibt das einen Unterschied.
Kann einer von uns vor Jahren nur durch Zufall drauf gekommen sein !?