Wie Sie Datensätze nach Ihren Kriterien zählen
© sdecoret - Adobe Stock

Wie Sie Datensätze nach Ihren Kriterien zählen

Das Filtern von Listen gibt Ihnen einen schnellen Überblick über die Daten, die Ihren Filterkriterien entsprechen. Aber wie sorgen Sie dafür, dass Sie die angezeigten Datensätze auch anderweitig aus­ werten und zählen können?
Veröffentlicht am | 
Zuletzt aktualisiert am

Berechnungen in gefilterten Listen können Sie nur über die Tabellenfunktion TEILERGEBNIS durchführen, die auch nur die gefilterten Zeilen berücksichtigen. Alle anderen Funktionen liefern ein Ergebnis unabhängig von den aktuellen Filtereinstellungen. Durch den Einsatz der Tabellenfunktion TEILERGEBNIS erhalten Sie zudem Resultate, die sich sofort verändern, wenn Sie andere Filterkriterien einstellen.

Zwar können Sie über TEILERGEBNIS in Ihren gefilterten Listen zählen, allerdings nur ohne eine Bedingung bzw. ohne ein Kriterium.

Wenn Sie in Ihren gefilterten Listen aber anhand eines Kriteriums zählen möchten, dann verwenden Sie die folgende Matrixformel:

=SUMMENPRODUKT((Kriterienbereich=Kriterium) *(TEILERGEBNIS(3;BEREICH.VERSCHIEBEN (ErsteZelle;ZEILE(Kriterienbereich)-ZEILE(ErsteZelle);;))))

Sie übergeben der Matrixformel drei Argumente: Mit Kriterienbereich übergeben Sie die komplette Spalte der Liste, die Sie auf das Kriterium prüfen möchten. Über Kriterium übergeben Sie das Kriterium, das in Kriterienbereich erfüllt sein muss, damit die Formel die Zeile zählt. Das dritte Argument ErsteZelle übergibt die erste Zelle des Kriterienbereichs.
Die Formel zählt alle gefilterten Zeilen, die in Kriterienbereich mit Kriterium übereinstimmen. Es handelt sich bei der Formel um eine Matrixformel, die Sie nach der Eingabe über die Tastenkombination Strg+Umschalt+Eingabe bestätigen.

In der abgebildeten Arbeitsmappe liefert die folgende Matrixformel in Zelle B21 das Ergebnis 2:

=SUMMENPRODUKT(($C$4:$C$18=A21) *(TEILERGEBNIS(3;BEREICH.VERSCHIEBEN ($C$4;ZEILE($C$4:$C$18)-ZEILE($C$4);;))))

Nur die beiden gefilterten Zeilen 7 und 9 stimmen mit dem Suchkriterium „Offen“ aus Zelle A21 überein.

 

So zählt die Formel die Anzahl der gesuchten Datensätze

Über die folgende Methode zählt die Matrixformel die gesuchte Anzahl anhand des Kriteriums:

 

  • Die TEILERGEBNIS-Funktion setzt durch das zweite Argument von drei die Funktion ANZAHL2 ein. Das heißt, es werden alle gefüllten Zellen gezählt, die gefiltert sind.
  • Über BEREICH.VERSCHIEBEN erzeugen Sie für jede Zeile des Bereichs C4:C18 einen einzeiligen Bezug. Diesen einzeiligen Bezug übergeben Sie der TEILERGEBNIS-Funktion.
  • Dadurch liefert TEILERGEBNIS nur dann die Anzahl 1, wenn die Zeile gefiltert, als 0 eingeblendet ist.
  • Bei ungefilterten Zeilen liefert TEILERGEBNIS den Wert 0.
  • Die gezählten Einsen übergeben Sie der SUMMEN-PRODUKT-Funktion.
  • Dort werden sie mit dem Ergebnis des Vergleichs zwischen Zelle A21 und dem jeweiligen Inhalt aus der entsprechenden Zeile des Bereichs C4:C18 multipliziert.
  • Nur wenn der Vergleich positiv ausfällt, wird die übergebene 1 mit WAHR multipliziert und das Ergebnis von ebenfalls 1 fließt in die gelieferte Summe von SUMMENPRODUKT ein.