Eine häufige Aufgabenstellung ist es, die Anzahl von Einträgen in einer Liste oder Tabelle zu ermitteln: Wie viele Produkte enthält meine Preisliste? Wieviel Adressen sind in meiner Adressenliste? Und bei wievielen davon fehlt die PLZ?
Dazu bietet Excel mit einer Reihe von Standardfunktionen alles, was man als „Erbsenzähler“ braucht, um solche oder ähnliche Fragestellungen einfach zu lösen.
In diesem Beitrag stelle ich folgende Funktionen vor:
- ANZAHL()
- ANZAHL2()
- ANZAHLLEEREZELLEN()
- ZÄHLENWENN()
- ZÄHLENWENNS()
Und so geht’s:
ANZAHL, ANZAHL2 und ANZAHLLEEREZELLEN
Du hast eine sehr lange Tabelle vor Dir und musst wissen, wieviele Zahlen in einem bestimmten Bereich enthalten sind? Dazu bietet Excel die ANZAHL-Funktion. Die Funktion ist denkbar einfach und erwartet nur den zu zählenden Bereich als Parameter: =ANZAHL(Bereich).
Dabei ist es natürlich auch möglich, mehrere voneinander getrennte Bereiche anzugeben: =ANZAHL(Bereich1; Bereich2; …). Damit wird also die Gesamtanzahl aller Zahlen in den genannten Bereichen ermittelt.
Wichtig: Gezählt werden nur Zellen, die Zahlen enthalten. Dazu zählen auch Zellen mit Datum und Uhrzeiten. Ignoriert werden bei der Zählung hingegen Leerzellen, und solche, die einen Text enthalten.
Benötigst Du jedoch die Anzahl sämtlicher gefüllter Zellen in einem Bereich (egal ob mit Zahlen oder Texten gefüllt), dann hilft die Funktion =ANZAHL2.
Willst Du jedoch wissen, wieviele leere Zellen ein bestimmter Bereich enthält (z.B. die fehlenden Postleitzahlen in Deiner Adressenliste)? Die Lösung verrät Dir die Funktion =ANZAHLLEEREZELLEN (aufpassen: Es heißt tatsächlich ANZAHLLEEREZELLEN, also mit „E“ nach „LEER“).
Das folgende Bild zeigt Dir nochmal an einem Beispiel, wie die drei Funktionen eingesetzt werden. Der gelbe Bereich enthält die Liste, in der gezählt werden soll:
ANZAHL, ANZAHL2 und ANZAHLLEEREZELLEN
Und hier siehst Du, was genau gezählt wird:
Was genau wird gezählt?
Wie man sieht, wird die Datumszelle auch als Zahl interpretiert.
Und wenn ich jetzt wissen möchte, wieviele Zellen in meiner Tabelle sind, die nur einen Text enthalten (also keine Zahlen)? Kombiniere dazu einfach die beiden Funktionen: =ANZAHL2(Bereich)-ANZAHL(Bereich)
ZÄHLENWENN
Was ist aber, wenn Du nur die Anzahl von Zellen brauchst, die ein ganz bestimmtes Suchkriterium erfüllen?
Dann benötigst Du die Funktion =ZÄHLENWENN. Sie erwartet zwei Parameter:
1. den Bereich, der gezählt werden soll
2. das Suchkriterium
Im folgenden Beispiel werden in der Tabelle nur die Zellen gezählt, die den Eintrag „Blau“ enthalten:
Mit einer Bedingung zählen
Groß- und Kleinschreibung spielen dabei keine Rolle, ansonsten wird aber der gesamte Zellinhalt verglichen. Führende Leerzeichen im Suchbegriff oder zusammengesetzte Wörter werden somit nicht gezählt.
Handelt es sich beim Suchkriterium um eine Zahl, dürfen auch die Operanden >, <, >= und <= angegeben werden. Im folgenden Beispiel möchte ich wissen, wieviele Zahlen in der Liste enthalten sind, die größer als 15 sind:
Mit einer Bedingung zählen (2)
Anstelle eines Zellverweises hätte ich das Suchkriterium auch direkt in die Funktion eingeben können, eingeschlossen in Anführungszeichen:
=ZÄHLENWENN(E16:E25;“>=15″)
ZÄHLENWENNS
Wer stattdessen mehrere Suchkriterien benötigt, muss zur Funktion =ZÄHLENWENNS greifen (mit „S“ am Ende).
Die Anwendung sieht folgendermaßen aus:
=ZÄHLENWENNS(Bereich1; Suchkriterium1; Bereich2; Suchkriterium2; …)
Die zu durchsuchenden Bereiche müssen dabei die gleiche Anzahl an Zeilen und Spalten haben, brauchen aber nicht zwingend direkt nebeneinander zu liegen. Gezählt wird nur, wie oft alle Kriterien zutreffen. Hört sich kompliziert an?
Das folgende Beispiel veranschaulicht das Ganze hoffentlich etwas. Gegeben ist eine Liste mit Mitarbeitern, deren Abteilung und ihrem Geschlecht. Ich möchte nun wissen, wieviele Frauen in der Buchhaltung arbeiten und habe somit die zwei Suchkriterien Abteilung=“Buchhaltung“ und Geschlecht=“weiblich“:
Mit mehreren Bedingungen zählen
BONUS-TIPP:
In den Suchkriterien lassen sich auch die Platzhalter ? und * verwenden. Das Fragezeichen steht dabei für genau 1 Zeichen, der Stern für eine beliebige Anzahl von Zeichen. Somit hätte mein Suchkriterium auch „Buch*“ lauten können.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Vielen Dank, das sind tolle und brauchbare Erklärungen.
Danke sehr, gern geschehen 🙂
Schöne Grüße,
Martin
Hallo Martin,
ich habe folgendes Problem und dachte eigentlich, dass die Zählenwenns-Funktion da die richtige Lösung wäre, aber es funktioniert irgendwie nicht.
Und zwar habe ich eine Spalte (AA), in der in 5 nicht zusammenhängenden, einzelnen Zellen entweder „Ja“ oder „Nein“ steht und ich will mir darunter die Anzahl an Zellen ausgeben lassen, in denen „Ja“ steht.
Zwischen den 5 Zellen liegen jeweils mehrere Zellen mit „Ja“ oder „Nein“-Angaben, die bestimmen, ob in den 5 zu zählenden Zellen „Ja“ oder „Nein“ steht. Diese sollen in der Zählung nicht berücksichtigt werden (daher will ich auch getrennte Zellen berechnen, was mir Probleme bereitet).
Die Ausgabe-Zelle ist unterhalb der 5 zu zählenden Zellen gelegen.
Zur besseren Verständlichkeit hier mal die Zellenbezeichungen, der Zellen die gezählt werden sollen:
AA1414; AA1426; AA1432; AA1439 und AA1450. Die Ausgabezelle ist die AA1456 (hier soll ausgegeben werden, in wie vielen Zellen „Ja“ steht, die Anzahl an „Neins“ interessiert mich nicht).
In den ersten drei Zellen steht jeweils „Nein“ und in den letzten beiden „Ja“ (ändert sich immer in den nachfolgenden Spalten, aber wenn ich die Formel habe ist das dann ja egal).
Ich hoffe, Sie können mir helfen und bedanke mich schon mal im Vorraus.
Mit freundlichen Grüßen,
Magdalena
Hallo Magdalena,
die ZÄHLENWENN-Funktion kann offensichtlich nicht mit nichtzusammenhängenden Bereichen umgehen. Da es in Ihrem Fall nur 5 Zellen sind, die berücksichtigt werden sollen, schlage ich folgende Formel vor:
=(AA1414=“Ja“)+(AA1426=“Ja“)+(A1432=“Ja“)+(AA1439=“ja“)+(AA1450=“Ja“)
Nicht superelegant, aber es funktioniert.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die Antwort, es hat tatsächlich funktioniert und das ist für mich wichtiger als die Eleganz 🙂
Vielen Dank dafür!
Schöne Grüße,
Magdalena
Hallo Magdalena,
freut mich. Und im Zweifelsfall kommt die Eleganz natürlich immer zum Schluss 🙂
Schöne Grüße,
Martin
Hallo Martin.
Bei mir bekommt ab B3 jede 2. Zeile unter B einen Wert (oder auch nicht) aus einer anderen Tabelle. Jetzt möchte ich wissen, wieviel Werte die TabelleA und wieviel Werte die TabelleB gesendet haben.
Also in B1 soll berechnet werden die ANZAHL(B3,B5,B7,B9,usw)
und in B2 soll berechnet werden die ANZAHL(B4,B6,B8,B10,usw)
Du hast eine schöne Summenprodukt-Formel mit der Rest-Formel kombiniert. Da wird z.B. nur jede 2. Zeile addiert. Leider funktioniert das nicht mit =ANZAHL(B3:B49;(REST(ZEILE(B3:B49)-1;2))).
Also das Ergebnis in B1 soll 3 sein, wenn in B3=5, B5=““,B7=1,B9=99,B11=““ als Zellwert steht.
Danke und schöne Grüße, Peter
Hallo Peter,
du kannst dafür auch die SUMMENPRODUKT-Funktion nutzen:
=SUMMENPRODUKT(–((B3:B49)<>„“);REST(ZEILE(B3:B49)-2;2))
Im ersten Teil wird nur geprüft, ob die Zelle gefüllt ist. Wenn ja, gibt das den Wert 1, wenn nein, dann 0. Und der zweite Teil mit der REST-Funktion sorgt dafür, dass nur jede zweite Zeile benutzt wird. Da die Zählung erst ab Zeile 3 beginnt, wird der Wert 2 (für die ersten beiden Zeilen) abgezogen.
Schöne Grüße,
Martin
Hallo Martin,
ich suche mit ZÄHLENWENN(A1:A25;“*#*″) nach „#“ in den Zellen der Spalte A.
A1 „Text5“
A2 „#Text1, #Text2, #Text3“
A3 „Text4, #Text8, #Text6“
A4 „Text7, Text9“
…
Hier bringt mir Excel das Ergebnis 2, da in 2 Zeilen „#“ vorhanden ist,
aber berücksichtigt NICHT das mehrfache Vorkommen in A2 und A3 !
Wie kann ich hier das korrekte Ergebnis 5 (Anzahl aller „#“) erreichen ?
Hallo Andre,
um die Anzahl eines bestimmten Zeichens innerhalb einer Zelle zu bestimmen, muss man auf einen Trick zurückgreifen, den ich mal im Quickie 115 beschrieben habe. Dabei wird die Textlänge bestimmt, dann das betreffende Zeichen entfernt und dann erneut die Textlänge bestimmt. Durch die Differenz sieht man dann, wie viele Zeichen es waren.
Die Formel für eine einzelne Zelle lautet:
=LÄNGE(A1)-LÄNGE(WECHSELN(A1;“#“;““))
Du könntest das für jede Zeile berechnen und am Ende die Summe bilden.
Oder alles in eine einzige Array-Formel packen:
=SUMME(LÄNGE(A1:A25)-LÄNGE(WECHSELN(A1:A25;“#“;““)))
Wenn du Microsoft 365 nutzt, kannst du die Formel einfach so eingeben. In anderen Excel-Versionen musst du sie mit Strg+Umschalt+Eingabetaste abschließen, so dass dann eine Array-Formel daraus wird.
Schöne Grüße,
Martin
Hallo Martin,
wir suchen schon seit mehreren Stunden nach einer passenden EXEL-Formel. Ich hoffe Du kannst uns helfen.
Wir versuchen herauszufinden wie wir was sortieren können. Wir haben eine Liste erstellt mit in A(Namen) und B(Durchschnitt) C(Werte aus gespielten Spiele). Nun kommen jede Woche in Spalte D etc. neue Werte hinzu. Nun ist die Frage welche Formel wir anwenden müssen, wenn wir EXEL auffordern wollen, automatisch die Spalten B,C und dann D usw. von hoch zu niedrig zu sortieren aber dazu müssen dann die Namen eben auch mit sortiert werden. Ich hoffe ich konnte es die verständlich erklären.
Hallo Nancy,
ich fürchte, das wird nicht automatisch möglich sein. Denn dazu müsste zuerst jede Zeile für sich spaltenweise sortiert werden und anschließend alle Zeilen. Das lässt sich meines Erachtens nicht lösen.
Schöne Grüße,
Martin