So zählen Sie Zellen, die nicht leer sind, mit Countif (2023)

Was ist ZÄHLENWENN?

Countif ist eine beliebte Funktion zum Zählen nicht leerer Zellen. Diese Funktion ist mit allen Tabellenkalkulationsprogrammen wie Excel, Google Sheets oder Numbers kompatibel.

Zählen Sie Zellen, die nicht leer sind, mit Countif

Diese Funktion ist in der Lage, Datumsformate, Zahlen, Textwerte, Leerzeichen, Nicht-Leerzeichen oder Suchvorgänge zu zählen, z. B. Zellen, die bestimmte Zeichen usw. enthalten. Kurz gesagt handelt es sich um eine Funktion, die die Anzahl der Zellen zählt, die eine bestimmte Bedingung erfüllen .

In diesem Artikel werden wir die „Not Blank“-Funktion von COUNTIF besprechen. Wie der Name schon sagt, wird ZÄHLENWENN mit dem Kriterium „Nicht leer“ in Tabellenkalkulationen verwendet, um nicht leere Zellen in einer Spalte zu zählen. In dieser Funktion werden Zellen mit Daten gezählt, während leere Zellen ausgeschlossen und nicht gezählt werden.

Formel von COUNTIF.

In der einfachsten Form der ZÄHLENWENN-Formel müssen Sie den Bereich und die Kriterien angeben. Diese Formel filtert im Wesentlichen die Anzahl der Zellen gemäß den von Ihnen genannten Kriterien.

=countif(Bereich,Kriterien)

Countif mit der Annotation „Not Blank“.

Das Folgende ist die generische Form der Countif-Formel mit nicht leeren Kriterien:

=countif(range,”<>“)Countif nicht leere Formel

In dieser Formel geben wir Countif an, um alle Zellen im angegebenen Bereich zu zählen, die nicht gleich Null sind (dargestellt durch das Symbol <>).

Beispiel Nr. 1: Einzelne Spalte

Im Bild unten haben wir zwei Spalten. Monat und Budget. Um gefüllte Zellen in der Spalte „Budget“ (C4 bis C15) zu finden, können Sie diese Formel verwenden:

=countif(C4:C15,”<>“)Zählen leerer Zellen im Bereich C4 bis C15

Das Ergebnis lautet nun 7, was zeigt, dass es im angegebenen Bereich 7 nicht leere Zellen gibt.

Beispiel Nr. 2: Mehrere Spalten

Um Zellen zu finden, die in diesen beiden Spalten (B4 bis C15) nicht leer sind, müssen Sie die folgende Formel eingeben:

=countif(B4:C15,”<>“)Zählen leerer Zellen im Bereich B4 bis C15

Das Ergebnis lautet 19, was zeigt, dass der Bereich 19 nicht leere Zellen enthält.

Einführung der CountA-Funktion

Es gibt eine alternative Funktion, mit der Sie nicht leere Zellen in einem Bereich zählen können:

=COUNTA(B4:C15)Verwenden der CountA-Formel zum Zählen nicht leerer Zellen

Zeigt das Ergebnis von 19, dasselbe wie die Funktion „Countif Not Blank“.

Beachten Sie, dass die CountA-Funktion nicht mehr als ein Argument akzeptieren kann. Die Countif-Funktion ist besser zu verwenden, wenn Sie vorhaben, andere Argumente mit den Daten zu verwenden.

Der Vorteil von CountA: Mehrere Bereiche

Die CountA-Funktion hat einen Vorteil gegenüber der Countif-Funktion, da sie mehrere Bereiche umfassen kann. Wenn Sie beispielsweise nicht leere Zellen in mehreren Bereichen im Datensatz zählen möchten, kann CountA hilfreich sein.

Geben Sie beispielsweise die folgende Formel in die Zelle I5 ein:

=ANZAHL(B4:C15,D4:H5)Verwendung mehrerer Bereiche in der CountA-Formel

Dies zeigt das Ergebnis von 25, das zwei verschiedene Bereiche hat, nämlich den Bereich B4:C15 und D4:H5.

Verwenden von Countif für mehrere Bereiche und Kriterien

Countif kann weiterhin für mehrere Bereiche verwendet werden, ist jedoch etwas komplizierter als CountA.

Sehen Sie sich beispielsweise die folgende Formel in der Spalte J4 an:

=COUNTIFS(B4:B15,”<>“&””,C4:C15,”<10000")Verwenden Sie mehrere Kriterien mit der CountIFs-Formel

Diese Formel zählt Zellen in den angegebenen Bereichen, in denen Zellen nicht leer und kleiner als 10.000 sind, also 6. Wenn Sie beim Zählen nicht leerer Zellen Nullen ausschließen möchten, können Sie die folgende Formel verwenden:

=ZÄHLENWENN(A1:A10,”<>0″,A1:A10,”<>“)

Wenn Sie die nicht leeren Zellen neben einer bestimmten Zelle zählen möchten, können Sie Folgendes versuchen:

=ZÄHLENWENN(A:A,”B”,B:B,”>0″)

Beachten Sie, dass Countifs nur die Werte zählt, die alle Kriterien erfüllen. Sie können die Funktion DCountA auch verwenden, um nicht leere Zellen in einem Feld anhand bestimmter Kriterien zu berechnen.

Verwenden Sie mehrere Countif-Funktionen

Wenn Sie mit Countifs nicht vertraut sind oder es nicht funktioniert, können Sie mehrere Countif-Funktionen verwenden, um dasselbe zu erreichen. Schauen Sie sich die Formel unten an:

=(COUNTIF(B4:B15,”<>“)+COUNTIF(C4:C15,”<>“)+COUNTIF(D4:D15,”<>“))

Diese Formel zählt alle leeren Zellen in den drei verschiedenen Bereichen. Sie können auch unterschiedliche Kriterien für unterschiedliche Countif-Funktionen verwenden.

Problem 1: Die unsichtbaren, nicht leeren Zellen

Das Problem mit den Funktionen Countif, Countifs und CountA besteht darin, dass sie Zellen berechnen, die Leerzeichen, leere Zeichenfolgen oder Apostrophe (‘) enthalten.

Dies kann dazu führen, dass die Zählung falsch ist und die auf der Grundlage dieser Daten getroffenen Entscheidungen ebenfalls falsch sind. Dies ist eines der häufigsten Probleme bei diesen Formeln. Sie können es anhand der folgenden Formel und des folgenden Bildes besser verstehen:

=COUNTIF(B4:C15,”<>“)Problem mit nicht sichtbaren, nicht leeren Zellen

Im Bild können Sie nun sehen, dass die Gesamtzahl der Zellen in B4:C15 24 beträgt.

Die im Bild gezeigten leeren Zellen sind 5 (C6, C9, C11, C13 und C14). Die nicht leeren Zellen sollten also 19 (24-5) sein, aber das Ergebnis in D4 zeigt 20.

Es ist 20, weil Zelle C13 ein Leerzeichen enthält und die Formel sie auch als nicht leer zählt.

Platz in Zelle C13 führt zu einer falschen Zählung nicht leerer Zellen

Schritt 1: Finden Sie unsichtbare, nicht leere Zellen mithilfe der Längenformel

Im obigen Beispiel enthält Zelle C13 ein Leerzeichen.

Wir können es mithilfe der Längenformel herausfinden. Geben Sie in Anlehnung an das obige Beispiel die folgende Formel in D4 ein:

=LEN(C4)Finden der Zelle mit einem Leerzeichen mithilfe der LEN-Formel

Kopieren Sie nun die Formel bis zur Zelle D15. Danach werden Sie feststellen, dass in der Zelle D13 1 Zeichen angezeigt wird, in Zelle C13 jedoch kein Zeichen, was uns zeigt, dass sich in der Zelle C13 ein unsichtbares Zeichen befindet.

Wählen Sie nun die Zelle C13 aus und drücken Sie die Entf-Taste. Sie werden feststellen, dass die D4-Zelle 19 als nicht leere Zellen anzeigt, was die richtige Antwort ist.

Schritt 2: Überprüfen der nicht leeren Anzahl

Wir können die endgültige Anzahl der nicht leeren Zellen bestätigen, indem wir die Leerstellen zählen und sie mit der Gesamtgröße des Datensatzes vergleichen.

Hier ist zunächst die Formel zum Zählen von Leerzeichen mithilfe des Countif:

=COUNTIF(B4:C15,””)

Dies zeigt das Ergebnis von 5 in Zelle G4. Sie können auch die Formel =COUNTBLANK(B4:C15) verwenden.

Zählen Sie nun die Gesamtzahl der Zellen im Bereich wie folgt:

=ZEILEN(B4:C15)*SÄULEN(B4:C15)Bestätigen des Countif Not Blank-Ergebnisses

Dies zeigt das Ergebnis von 24 in Zelle H4.

Jetzt können wir bestätigen, dass der Countif mit dem Parameter „non-blank“ die korrekte Anzahl von Zellen als 19 anzeigt.

24 – 5 = 19

Problem 2: Das versteckte Apostroph-Problem

Ebenso wie Leerzeichen in einer Zelle wird auch ein versteckter Apostroph nicht in der Zelle angezeigt. Wir können die Längenfunktion nicht verwenden, da der Apostroph ausgeblendet ist und die Längenfunktion den Apostroph nicht als Zeichen anzeigt.

Um das Problem zu verstehen, sehen Sie sich die Formel in Zelle D4 im Bild unten an:

=COUNTIF(B4:C15,”<>“)

Die Zelle zeigt, dass es 20 nicht leere Zellen gibt, aber wir wissen bereits (aus dem zuvor besprochenen Beispiel), dass es 19 nicht leere Zellen gibt.

Versuchen wir es nun mit der Längenformel, aber diese zeigt für alle leeren Zellen die Zeichen 0 an.

Countif Blank zeigt falsches Ergebnis, während die Längenformel die Länge leerer Zellen als Null anzeigt

Lösung: Verwenden Sie „Mit 1 multiplizieren“, um den versteckten Apostroph zu finden

Wir können das Format des Apostrophs nutzen, um herauszufinden, ob sich einer in einer Zelle versteckt. Da es sich um einen Textwert handelt, führt die Multiplikation mit 1 zu einem Wertfehler.

Geben Sie in Zelle F4 die folgende Formel ein:

=C4*1Legen Sie in der Zelle F4 eine Formel zum Multiplizieren mit 1 fest

Kopieren Sie nun die Formel bis zur Zelle F15. Dann werden Sie einen #Wert-Fehler in der F9-Zelle bemerken.

Wählen Sie nun die Zelle C9 aus und Sie werden ein verstecktes Apostroph in der Bearbeitungsleiste bemerken.

Drücken Sie die Löschtaste und das Ergebnis in der D4-Zelle wird als 19 angezeigt, was gemäß unseren vorherigen Erkenntnissen die richtige Antwort ist.

Kopieren Sie die Formel „Mit 1 multiplizieren“ in andere Zellen und geben Sie einen Wertfehler aufgrund eines Apostrophs in der C9-Zelle ein

Problem 3: Das Problem mit der leeren Zeichenfolge (=““)

Ebenso wie Leerzeichen und Apostrophe wird auch die leere Zeichenfolge (=““) nicht in einer Zelle angezeigt.

Die Längenfunktion zeigt nicht die Länge der leeren Zeichenfolge an, aber die Methode „Mit 1 multiplizieren“, wie oben im Abschnitt „Verborgener Apostroph“ beschrieben, funktioniert.

Um das Problem besser zu verstehen, fahren wir mit dem oben diskutierten Beispiel fort. Geben Sie in die Zelle C14 die folgende Formel ein:

=””

Jetzt werden Sie feststellen, dass die Ausgabe von Countif not blank um 1 gestiegen ist und 20 beträgt, die C14-Zelle jedoch sichtbar leer ist. Geben Sie nun die folgende Formel in die Zelle E4 ein:

=LEN(C4)

Kopieren Sie dann die Formel in die Zelle E15, aber die Zelle E14 zeigt keine Zeichen an, dh es wird nicht berücksichtigt, dass die leere Zeichenfolge ein Zeichen enthält, aber der Countif zählt die Zelle als nicht leer.

Lösung: Verwenden Sie „Mit 1 multiplizieren“, um die leere Zeichenfolge zu finden

Geben Sie nun die folgende Formel in die F4-Zelle ein:

=C4*1

Kopieren Sie dann die Formel bis zur Zelle F15 und Sie werden sofort feststellen, dass in der Zelle F14 ein #Wert-Fehler angezeigt wurde.

Wählen Sie nun die Zelle C4 aus und Sie werden eine leere Zeichenfolge (=““) in der Bearbeitungsleiste der Tabellenkalkulationsanwendung bemerken.

Finden einer leeren Zeichenfolgenzelle durch Multiplikation mit einer Formel

Löschen Sie nun die leere Zeichenfolge aus der F14-Zelle und die Countif not blank-Formel in der D4-Zelle zeigt nun das genaue Ergebnis von 19 an.

Sie können auch die Methode „Mit 1 multiplizieren“ verwenden, um die Zellen mit Leerzeichen zu finden.

Workaround für alle Probleme: Verwendung von SUMPRODUCT

Die oben genannten Problemumgehungen zur Behebung der Dateninkonsistenzen sind sehr effizient, können sich jedoch als zu langwierig erweisen, wenn Sie mit großen Datenmengen arbeiten. Im folgenden Beispiel haben wir das gleiche Problem wie zuvor verwendet und Zellen enthalten versteckte Werte (leere Zeichenfolgen und versteckte Apostrophe).

Um diese manuelle Arbeit zu umgehen, geben Sie die folgende Formel in die F4-Zelle ein, die SUMPRODUCT verwendet:

=SUMPRODUCT((TRIM(B4:C15)<>“”)*1)Summenproduktfunktion zeigt die richtige Antwort an, während Countif nicht leer ist, und zeigt aufgrund unsichtbarer, nicht leerer Zellen ein falsches Ergebnis an

Jetzt sehen Sie die F4-Zelle, die das Ergebnis von 19 anzeigt, das tatsächliche Ergebnis der nicht leeren Zellen im angegebenen Bereich, wie wir zuvor festgestellt haben.

• In dieser Formel wird TRIM(B4:C15) verwendet, um Leerzeichen aus der Ausgabe zu entfernen.
• TRIM(B4:C15)<>““ wird verwendet, um zu erkennen, dass die Zellen nicht leer sind.
• Dann wandelt (TRIM(B4:C15)<>““)*1 die boolesche Ausgabe (True für nicht leer, False für leer) in ihr algebraisches Gegenstück um, d. h. 1 für True und 0 für False.
• Jetzt multipliziert und summiert SUMPRODUCT Arrays, was hier 19 ergibt.

Wenn dies die Anforderungen nicht erfüllt, können Sie Ihre Daten in eine Tabelle umwandeln und die strukturierteren Formeln verwenden, um Leerzeichen einfach zu zählen.