Ich habe ein Blatt, in dem ich zunächst einen Filter auf eine bestimmte Spalte anwende, um „Nur eindeutige Datensätze“ anzuzeigen. Jetzt habe ich eine Spalte, die beispielsweise Werte wie diese enthält:
Applepie
Applesauce
Crabapple
Banana
Mango
Was ich brauche, ist die Anzahl aller sichtbaren Zellen, die das Wort „Apple“ enthalten. Beachten Sie, dass dabei die ausgeblendeten (herausgefilterten) Zellen ausgeschlossen sein sollten.
Jetzt habe ich eine Formel gefunden:
`=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!A:A,ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A)),,1))*(Sheet1!A:A="<Text to search>"))`
Das Problem dabei ist jedoch, dass diese Formel nach dem gesamten Text sucht. NICHT nur nach einem Teil davon. Wie ändere ich diese Formel, um die Anzahl der Zellen zurückzugeben?mit "Apfel"d. h. für dieses Beispiel sollte das Ergebnis 3 sein.
Antwort1
COUNTIFmit Platzhaltern funktioniert allein, um alle Zeilen zu zählen, in denen irgendwo im Text „Apple“ vorkommt … es ist jedoch nicht auf sichtbare Zeilen beschränkt und kann nicht COUNTIFin Verbindung mit Ihrer SUBTOTALFormel verwendet werden.
Passen Sie Ihre Formel wie folgt mit SEARCHder Funktion an
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!A:A,ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A)),,1)),ISNUMBER(SEARCH("Apple",Sheet1!A:A))+0)
Hinweis: Diese Version kann langsam sein und auf die gesamte Spalte verweisen. Es ist besser, die Bereiche einzuschränken, wenn Sie können.
Alternativer Ansatz
Wenn Sie diese lange Formel vermeiden möchten, können Sie eine Hilfsspalte verwenden, um anzugeben, ob jede Zeile gefiltert ist oder nicht. Verwenden Sie beispielsweise in Sheet1 Z2 diese nach unten kopierte Formel
=SUBTOTAL(3,A2)
Jetzt können Sie eine COUNTIFSFunktion verwenden, um sichtbare Zeilen zu zählen, die „Apple“ enthalten.
=COUNTIFS(A:A,"*Apple*",Z:Z,1)
Antwort2
Test: =COUNTIF(A2:A10;"Apple*")ändernrange