Wieviele sind das eigentlich? 10

Die verschiedenen Zählfunktionen in Excel und wofür Du sie brauchen kannst.
 

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

ANZAHL, ANZAHL2 und ANZAHLLEEREZELLEN

Und hier siehst Du, was genau gezählt wird:

Was genau wird gezählt?

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

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)

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

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.

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

10 Gedanken zu “Wieviele sind das eigentlich?

  • Avatar-Foto
    Magdalena

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Magdalena

        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

        • Avatar-Foto
          Martin Weiß

          Hallo Magdalena,

          freut mich. Und im Zweifelsfall kommt die Eleganz natürlich immer zum Schluss 🙂

          Schöne Grüße,
          Martin

  • Avatar-Foto
    Peter Knoblich

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Andre

    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 ?

    • Avatar-Foto
      Martin Weiß

      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