Wie Sie Datensätze nach Ihren Kriterien zählen

25. Mai 2016
Martin Althaus Von Martin Althaus, Excel, Office ...

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?

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.
  • Excel Vorlagen zum Sofort-Download
  • Geprüfte Vorlagen zum Sofort-Einsatz
  • Gratis Makros & Add-Ins im Downloadcenter

DDV Ehrencodex E-Mail-Marketing
  • Wir benötigen Ihre E-Mail-Adresse für die Zusendung des Newsletters.
  • Wir geben Ihre E-Mail-Adresse garantiert nicht an Dritte weiter.
  • Jederzeit abbestellbar durch einen Link im Newsletter.

Weitere Artikel zum Thema

Newsletter

Jetzt kostenlose Excel-Hilfe anfordern!

  • die besten Tipps
  • völlig kostenlos
  • jederzeit abbestellbar
DDV Ehrencodex E-Mail-Marketing
  • Wir benötigen Ihre E-Mail-Adresse für die Zusendung des Newsletters.
  • Wir geben Ihre E-Mail-Adresse garantiert nicht an Dritte weiter.
  • Jederzeit abbestellbar durch einen Link im Newsletter.

Unsere Datenschutzgarantie:

Wir benötigen Ihre E-Mail-Adresse für die Zusendung des Newsletters. Wir geben Ihre E-Mail-Adresse garantiert nicht an Dritte weiter. Jederzeit abbestellbar durch einen Link im Newsletter.

Unsere Experten

Rudolf Ring twittert für @Computerwissen

Die von Ihnen eingegebene E-Mail Adresse ist nicht korrekt. Bitte korrgieren Sie Ihre Eingabe und klicken Sie auf "OK":

Bitte wählen Sie mindestens einen Newsletter aus und klicken Sie auf "OK"