Excel Formeln – Zellen zählen wenn ein Teilstring vorhanden ist

In diesem Artikel werde ich ihnen zeigen, wie man in Excel Zellen zählt, in denen ein bestimmter String vorhanden ist.

Mit der Excelfunktion ANZAHL2 kann man in einem Bereich die Zellen zählen, in denen Inhalt steht. Dabei ist aber völlig irrelevant, womit die Zellen gefüllt sind, es wird nur geschaut, ob in den Zellen überhaupt etwas steht. Möchten Sie aber, dass nur Zellen gezählt werden, in denen etwas Bestimmtes steht, so lesen Sie diesen Artikel.

Beginn:

Schreiben Sie zu Beginn in die Spalte A erst einmal alle Strings, die auf den Inhalt überprüft werden sollen. Bei mir waren es die Begriffe „123Text“, „123text“ und „Hallo“. Das eine Mal habe ich „Text“ kleingeschrieben, um ihnen im weiteren Verlauf die Wirkungsweise der Formeln besser zu verdeutlichen. Das Wort „Hallo“ habe ich dazu genommen, dass Sie sehen, dass die Formel nicht bei allen Begriffen anschlägt.

Teilstring befindet sich hinten:

Groß- und Kleinschreibung irrelevant:

Nun können Sie damit anfangen, die Anzahl an Zellen auszugeben, in denen der gesuchte Begriff auftritt. Dazu gibt es drei verschiedene Gruppen, in die man das Vorhaben aufteilen kann. Zum ersten die, dass der gesuchte Text sich hinten im String befindet, zum anderen dass er sich vorne befindet und zum dritten, dass er sich irgendwo in der Zelle befindet. Dabei kann man jeweils noch unterscheiden, ob zwischen Groß- und Kleinschreibung unterschieden werden soll. Jetzt werde ich mich um den Fall kümmern, dass sich der Teilstring hinten befindet und die Groß- und Kleinschreibung nicht relevant ist. Für dieses Vorhaben benötigen Sie natürlich eine Formel. Schreiben Sie jetzt in die Zelle B1 (es kann auch eine andere Zelle sein, die Zellen aus der Spalte A sollten es aber nicht sein, da alle Zellen dort für die zu untersuchenden Werte benutzt wurden und  Sie folglich die Zellbezüge noch verändern müssten) die folgende Formel hinein:

=ZÄHLENWENN(A:A;"*Text")

Nachdem Sie die Formel eingegeben haben, wird ihnen umgehend angezeigt, wie viele Zellen in der Spalte A den Suchbegriff enthalten. Dabei wird wie gesagt nicht auf die Groß- und Kleinschreibung geachtet, deshalb werden sowohl die Zelle A1 als auch A2 angesprochen und die Zahl „2“ ausgegeben. Den Suchbegriff ändern Sie natürlich dadurch, indem Sie in der Formel das Wort „Text“ durch ein anderes ersetzen. Möchten Sie nun, dass die Groß- und Kleinschreibung doch Auswirkungen hat, so lesen Sie den nächsten Abschnitt.

(Zur Darstellung in Originalgröße Abbildungen anklicken)

Unterscheidung zwischen Groß- und Kleinschreibung:

Jetzt zeige ich ihnen eine Formel, bei der es einen Unterschied macht, ob in der Spalte A der Suchbegriff groß- oder kleingeschrieben wird.  Dafür benötigen Sie natürlich wieder eine neue Formel. Dieses Mal ist es im Gegensatz zum Beispiel des vorherigen Punktes eine Matrixformel. Um diese einzugeben, löschen Sie aber erst einmal den Inhalt der Zelle B1. Nun schreiben Sie folgendes hinein:

=SUMME(IDENTISCH(RECHTS(A1:A100;Länge("Text"));"Text")*1)

Wie ich schon gesagt habe, ist diese Formel ja eine Matrixformel. Das bedeutet, dass es nicht reicht, wenn Sie die Formel einfach eingeben und Enter drücken. Um sie richtig auszuführen, müssen Sie nun folgendes machen: klicken Sie zuerst nochmals in die Zelle B1. Anschließend müssen Sie oben in die Bearbeitsungszeile der Formel klicken. Jetzt drücken Sie auf ihrer Tastatur bitte gleichzeitig die Tasten STRG+SHIFT+Enter. Die Formel in der Zeile hat sich nun in folgende verwandelt:

{=SUMME(IDENTISCH(RECHTS(A1:A100;Länge("Text"));"Text")*1)}

Ist das geschehen, so hat sich auch der Inhalt der Zelle verändert. Angezeigt wird ihnen nun der richtige Wert der Formel. Haben Sie das gleiche Beispiel wie ich gewählt, so sehen Sie hier nun eine „1“. Aktuell werden die Zellen nämlich nur darauf überprüft, dass sie an ihrer rechten Seite das Wort „Text“ enthalten. Auch die Großschreibung ist wichtig, sodass nur die erste Zelle A1 von der Formel betroffen ist. Möchten Sie, dass ein anderes Wort überprüft wird, oder es kleingeschrieben wird, so ändern Sie in der Formel das Wort „Text“ einfach entsprechend ab.

Teilstring befindet sich vorne:

Nichtbeachtung von Groß- und Kleinschreibung:

Nun haben Sie die Wege kennengelernt, wie man die Anzahl an Zellen zählt in denen ein bestimmter Text vorkommt unter der Bedingung, dass er am Ende steht. In diesem Teil der Anleitung werde ich ihnen erklären, wie Sie das gleiche machen können wenn der Teilstring am Anfang des kompletten Strings in der Zelle steht. Ändern Sie dazu nun die Zellen in der Spalte A ab. Möchten Sie meinem Beispiel exakt folgen, so tragen Sie in die Zelle A1 „Text123“ ein, in die Zelle A2 kommt „text123“. Das „Hallo“ in der Zelle A3 wird wieder so gelassen, um zu demonstrieren, dass die Formel nicht auf alle Einträge anspringt.  Auch die aktuelle Formel in der Zelle B1 löschen Sie nun. Eintragen werden Sie hier folgendes:

=ZÄHLENWENN(A:A;"Text*")

Wie schon bei im ersten Teil der Anleitung, bei der der Text rechts stand und Groß-und Kleinschreibung auch außenvorgelassen wurden, ist diese Formel keine Matrixformel. Das heißt sofort nachdem Sie die Formel eingegeben haben, erscheint in der Zelle selbst der richtige Wert. Haben Sie alles korrekt gemacht, so ist das auch der Fall. Bei mir wurde nun also die Zahl „2“ ausgegeben. Soll nicht das Wort „Text“ untersucht werden, sondern ein anderes, so ändern Sie einfach das Wort „Text“ in der Formel in das gewünschte ab.

Beachtung von Groß- und Kleinschreibung:

Steht der Teilstring weiterhin links, nun soll aber auch ein Unterschied gemacht werden, ob das zu suchende Wort groß- oder kleingeschrieben wird, so ist hier wieder eine neue Formel von Nöten. Wie Sie sich wahrscheinlich schon denken können sieht die dafür korrekte Formel der Formel aus Punkt 2 (Beachtung von Groß- und Kleinschreibung; Teilstring hinten) sehr ähnlich. Sie lautet wie folgt:

=SUMME(IDENTISCH(LINKS(A1:A100;Länge("Text"));"Text")*1)

Natürlich ist auch diese wieder eine Matrixformel, was bedeutet, dass Sie die Formel nicht einfach so eingeben können, sondern zuerst noch die Zelle B1 anklicken und danach die Formelbearbeitungszeile anklicken müssen. Haben Sie das gemacht, so drücken Sie nun noch STRG+SHIFT+Enter gleichzeitig auf der Tastatur. Nun sehen Sie unten in der Zelle den richtigen Wert. Auch die Formel hat sich verändert, sie sieht jetzt so aus:

{=SUMME(IDENTISCH(LINKS(A1:A100;Länge("Text"));"Text")*1)}

Vergleichen Sie die Formeln von hier und Schritt 2 nochmals genau, so sehen Sie, dass sich lediglich das „LINKS“ in der Formel geändert hat. Vorher stand hier „RECHTS“. Wenn Sie nach einem anderen Text als den Standardtext „Text“ suchen möchten, so müssen Sie die beiden Wörter „Text“ in der Formel mit ihrem gewünschten ersetzen.

Teilstring befindet sich irgendwo:

Ignorieren von Groß- und Kleinschreibung:

Die dritte Möglichkeit mit der man die Zellen zählen kann, die einen Teilstring enthalten, wird wohl die am häufigsten benutzte sein. Bei dieser Art ist es nämlich egal, wo der Text in dem String steht, der String muss also nach keinem bestimmten Muster aufgebaut sein. Zuerst werde ich ihnen wieder zeigen, wie die Formel aussieht, wenn nicht auf Groß- oder Kleinschreibung geachtet wird. Löschen Sie jetzt erst einmal wieder den Inhalt der Zelle B1. Schreiben Sie hier nun die folgende Formel hinein:

=ZÄHLENWENN(A:A;"*Text*")

Diese Formel ist natürlich auch wieder wie die anderen beiden aufgebaut, der einzige Unterscheid liegt darin, dass nun vor und nach dem Wort „Text“ ein Sternchen zu finden sind. Da bedeutet, dass vor „Text“ und nach „Text“ noch andere Zeichen folgen können. Nachdem die Formel eingegeben wurde, wird ihnen wieder umgehend die Anzahl der Zellen geliefert, die das gesuchte Wort enthalten. Bei mir war es das Wort „Text“, welches zwei Mal aufgetreten ist. Natürlich können Sie auch nach einem anderen Wort suchen, jedoch müssen Sie das dann in der Formel angeben, indem Sie „Text“ entsprechend ersetzen.

Unterscheidung zwischen Groß- und Kleinschreibung:

Natürlich haben Sie auch die Möglichkeit, die Anzahl an Zellen auszugeben, in denen das gewünschte Wort  in der Mitte steht und dabei zwischen einem kleinen und einem großen Anfangsbuchstaben unterschieden wird. Für dieses Anliegen ist die Formel fast komplett anders aufgebaut, als die in Schritt 2 und 4. Eine Gemeinsamkeit, die alle drei besitzen ist aber die, dass es alles Matrixformeln sind. Schreiben Sie jetzt in die Zelle B2 bitte folgendes:

=SUMME(ISTZAHL(FINDEN("Text";A1:A100))*1)

Wie schon gesagt, ist es eine Matrixformel. Das bedeutet, dass Sie, nachdem Sie die Formel eingegeben haben, wieder auf die Zelle und anschließend auf die Bearbeitungszeile der Formel oben klicken. Danach müssen Sie auf der Tastatur noch die Tasten STRG+SHIFT+Enter drücken. Wie für eine Matrixformel üblich, stimmt der Wert in der Zelle nun. Desweiteren hat sich die Formel natürlich wieder in folgende verändert:

{=SUMME(ISTZAHL(FINDEN("Text";A1:A100))*1)}

Möchten Sie hier nach einem anderen Wort suchen, so müssen Sie „Text“ in der Formel einfach entsprechend verändern. Dabei müssen Sie nicht darauf achten, dass vor oder nach dem Wort ein Sternchen zu sehen ist.

Suche nach mehreren Teilstrings:

Nun werde ich ihnen noch eine weitere Sache zeigen, die extrem hilfreich ist. Aktuell haben Sie zwar die Möglichkeit einen Teilstring innerhalb eines Strings zu suchen, egal wo er steht, jedoch geht es eben nur mit einem String. Möchten Sie aber nach mehreren Begriffen suchen, so benötigen Sie auch mehrere Formeln und Zellen. Jetzt zeige ich ihnen aber einen Weg, wie man das mit einer Formel und dementsprechend auch mit nur einer Zelle machen kann. Löschen Sie nun also zuerst einmal wieder den Inhalt in der Zelle B1. In die gleiche Zelle geben Sie nun die folgende Formel ein:

=SUMME(ZÄHLENWENN(A:A;{"*Text*";"*Hallo*"}))

Diese Formel sucht nun nach Zellen in der Spalte A, in denen entweder das Wort „Text“ oder das Wort „Hallo“ zu finden sind. Anschließend wird die Anzahl dieser Zellen ausgegeben. Die Groß- und Kleinschreibung wird bei dieser Formel nicht beachtet, deshalb lautet die Ausgabe auch „3“ (mit Zelle A2) anstatt „2“. Möchten Sie nach anderen Wörtern suchen, so passen Sie die Begriffe oben an, achten Sie jedoch darauf, dass Sie die Sternchen vor und nach dem Wort nicht vergessen. Wenn Sie die Formel angeschaut haben und dich wegen der geschweiften Klammern gewundert haben, so kann ich ihnen sagen, dass es keine Matrixformel ist und sie mit geschrieben werden müssen.

Nun wissen Sie über verschiedene Möglichkeiten Bescheid, wie man die Zellen zählt in den ein gewünschter Teilstring auftritt. Dabei haben Sie verschiedene Möglichkeiten um mit der Position des Teilstrings umzugehen.