Wie zählt man in Excel gefilterte Zellen, die Text enthalten?
In Excel ist das Zählen von Zellen mit Text kinderleicht – doch sobald gefilterte Daten ins Spiel kommen, wird es knifflig. In diesem Tutorial stellen wir Ihnen drei bewährte Methoden vor, um Textzellen in einer gefilterten Liste präzise zu zählen.
Gefilterte Textzellen mithilfe einer Hilfsspalte zählen
Gefilterte Textzellen mithilfe einer Hilfsspalte zählen
Mithilfe der COUNTIF-Funktion und einer Hilfsspalte lassen sich gefilterte Textzellen ganz einfach zählen. Gehen Sie dazu wie folgt vor:
1. Kopieren Sie die folgende Formel in Zelle D2 und drücken Sie die Eingabetaste, um das erste Ergebnis zu erhalten.
=SUBTOTAL (103, A2)
Hinweis: Die Hilfsspalte mit der TEILERGEBNIS-Formel prüft, ob die Zeile gefiltert ist. Dabei steht „103“ im Argument „function_num“ für die Funktion „ANZAHL2“.
2. Ziehen Sie anschließend den Ausfüllknauf nach unten auf die Zellen, auf die Sie die Formel ebenfalls anwenden möchten.
3. Kopieren Sie die folgende Formel in Zelle „F2“ und drücken Sie die Eingabetaste, um Ihr endgültiges Ergebnis zu erhalten.
=COUNTIFS(A2:A18,"*", D2:D18, 1)
Wie wir sehen können, enthalten die gefilterten Daten „4“ Zellen mit Text.
Gefilterte Textzellen mithilfe der Funktionen SUMMENPRODUKT, TEILERGEBNIS, INDIREKT, ZEILE und ISTTEXT zählen
Eine weitere Möglichkeit, gefilterte Zellen mit Text zu zählen, bietet die Kombination der Funktionen „SUMMENPRODUKT“, „TEILERGEBNIS“, „INDIREKT“, „ZEILE“ und „ISTTEXT“. Gehen Sie dazu wie folgt vor.
Kopieren Sie die folgende Formel in Zelle „E2“ und drücken Sie die Eingabetaste, um das Ergebnis anzuzeigen.
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18)))
Formelerklärung:
- „ROW(A2:A18)“ gibt die jeweilige Zeilennummer innerhalb des Bereichs A2:A18 zurück.
- „INDIRECT(„A“&ROW(A2:A18))“ liefert gültige Zellbezüge aus dem angegebenen Bereich zurück.
- Die Formel „SUBTOTAL(103;INDIREKT(„A“&ZEILE(A2:A18)))“ prüft, ob eine Zeile gefiltert ist, und gibt für sichtbare Zellen den Wert 1 sowie für ausgeblendete oder leere Zellen den Wert 0 zurück.
- „ISTEXT(A2:A18)“ prüft, ob jede Zelle im Bereich A2:A18 Text enthält, und liefert „WAHR“ für Zellen mit Text sowie „FALSCH“ für alle anderen. Der doppelte unäre Operator (--) wandelt diese WAHR-/FALSCH-Werte in 1 und 0 um.
- „SUMPRODUCT(SUBTOTAL(103, INDIRECT(„A“&ROW(A2:A18))), --(ISTEXT(A2:A18)))“ kann als „SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1})“ aufgefasst werden. Anschließend multipliziert SUMPRODUCT die beiden Arrays miteinander und gibt die Summe der Produkte zurück – nämlich 4.
Gefilterte Textzellen mithilfe der Funktionen SUMMENPRODUKT, TEILERGEBNIS, BEREICH.VERSCHIEBEN, MIN, ZEILE und ISTTEXT zählen
Die dritte Methode zum Zählen von Zellen mit Text in gefilterten Daten kombiniert die Funktionen „SUMMENPRODUKT“, „TEILERGEBNIS“, „BEREICH.VERSCHIEBEN“, „MIN“, „ZEILE“ und „ISTTEXT“. Gehen Sie dazu wie folgt vor:
Kopieren Sie die folgende Formel in Zelle „E2“ und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ISTEXT(A2:A18)))
Formelerklärung:
- „OFFSET(A2:A18; ROW(A2:A18)-2 – MIN(ROW(A2:A18)-2); ;1)“ gibt die einzelnen Zellbezüge aus dem Bereich A2:A18 zurück.
- Die Formel „SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1))“ prüft, ob eine Zeile gefiltert ist, und gibt für sichtbare Zellen 1 sowie für ausgeblendete oder leere Zellen 0 zurück.
- „ISTEXT(A2:A18)“ prüft, ob jede Zelle im Bereich A2:A18 Text enthält, und liefert „WAHR“ für Zellen mit Text sowie „FALSCH“ für alle anderen. Der doppelte unäre Operator (--) wandelt diese WAHR- und FALSCH-Werte in 1 und 0 um.
- Die Formel „SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 - MIN(ROW(A2:A18)-2),,1)), --(ISTEXT(A2:A18)))“ lässt sich als „SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1})“ auffassen. Anschließend multipliziert SUMPRODUCT die beiden Arrays elementweise und gibt die Summe der Produkte – also 4 – zurück.
Weitere Vorgänge (Artikel)
Kombinieren Sie COUNTIF und LINKS, um Zellen zu zählen, die in Excel mit einem bestimmten Zeichen beginnen
Möchten Sie herausfinden, wie viele Zellen in Excel mit einem bestimmten Zeichen beginnen? Kombinieren Sie einfach die Funktionen COUNTIF und LINKS! Tatsächlich gibt es mehrere Möglichkeiten, dies zu erreichen – in diesem Tutorial führen wir Sie Schritt für Schritt durch den gesamten Prozess.
Wie zählt man Zellen, die Zahlen enthalten – oder eben nicht – in Excel?
Wenn Sie einen Bereich mit Zellen haben, von denen einige Zahlen und andere Text enthalten, wie können Sie dann schnell die Zellen zählen, die Zahlen enthalten – oder eben nicht – in Excel?
Wie zählt man Zellen, wenn eine von mehreren Bedingungen erfüllt ist, in Excel?
Möchten Sie Zellen zählen, die mindestens eine von mehreren Bedingungen erfüllen? Hier zeige ich Ihnen effektive Methoden, um in Excel Zellen zu zählen, die X oder Y oder Z – oder eine beliebige dieser Optionen – enthalten.
Wie zählt man Zellen mit bestimmtem Text und Schriftformat oder Schriftfarbe in Excel?
Wissen Sie, wie man Zellen anhand mehrerer Bedingungen zählt? Zum Beispiel die Anzahl der Zellen, die sowohl einen bestimmten Text als auch eine bestimmte Schriftart oder Füllfarbe enthalten? Dieser Artikel zeigt Ihnen die Lösung.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins