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

Was ist Countif?

Countif ist eine beliebte Funktion zum Zählen von Zellen, die nicht leer sind. 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 kann Datumsformate, Zahlen, Textwerte, Leerzeichen, keine Leerzeichen oder Suchvorgänge wie Zellen mit bestimmten Zeichen usw. zählen. Kurz gesagt, es ist eine Funktion zum Zählen der Anzahl von Zellen, die eine Bedingung angeben.

In diesem Artikel werden wir über die „Not Blank“-Funktion von COUNTIF sprechen. Wie der Name schon sagt, wird COUNTIF mit Not Blank-Kriterien in Tabellenkalkulationen verwendet, um nicht leere Zellen in einer Spalte zu zählen. In dieser Funktion werden die Zellen mit Daten gezählt, während leere Zellen ausgeschlossen und nicht gezählt werden.

Formel von Countif

In der einfachsten Form der Countif-Formel müssen Sie den Bereich und die Kriterien angeben. Diese Formel filtert im Grunde 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 Not Blank-Formel

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

Beispiel Nr. 1: Einzelne Spalte

In der Abbildung 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

Jetzt wird das Ergebnis 7 sein, was zeigt, dass es 7 nicht leere Zellen im angegebenen Bereich 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

Und das Ergebnis wird als 19 ausgegeben, was zeigt, dass es 19 nicht leere Zellen im Bereich gibt.

Einführung der CountA-Funktion

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

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

Das zeigt das Ergebnis von 19, genau wie die Countif Not Blank-Funktion.

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, wo sie mehrere Bereiche enthalten kann. Wenn Sie beispielsweise nicht leere Zellen in mehreren Bereichen des Datensatzes zählen möchten, kann sich CountA als nützlich erweisen.

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

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

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

Verwenden von Countif für mehrere Bereiche und Kriterien

Countif kann immer noch für mehrere Bereiche verwendet werden, ist aber etwas komplizierter als CountA.

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

=ZÄHLENWENNS(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 10000 sind, was 6 ist. Wenn Sie Nullen aus der Zählung ausschließen möchten, während Sie auf Nicht-Leerzeichen zählen, können Sie die folgende Formel verwenden:

=ZÄHLENWENNS(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ÄHLENWENNS(A:A,”B”,B:B,”>0″)

Beachten Sie, dass Countifs nur die Werte zählt, die alle Kriterien erfüllen. Sie können auch die DCountA-Funktion verwenden, um nicht leere Zellen in einem Feld nach bestimmten 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 folgende Formel an:

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

Diese Formel zählt alle leeren Zellen in den drei verschiedenen Bereichen. Sie können auch verschiedene Kriterien für verschiedene 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 die Zählung falsch machen und die Entscheidungen, die auf diesen Daten getroffen werden, sind ebenfalls falsch. Dies ist eines der häufigsten Probleme mit diesen Formeln. Sie können es besser durch die folgende Formel und das folgende Bild verstehen:

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

Auf dem 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 es auch als nicht leer zählt.

Leerzeichen in Zelle C13, wodurch die Anzahl der nicht leeren Zellen falsch wird

Schritt 1: Finden Sie unsichtbare, nicht leere Zellen mithilfe der „Längen“-Formel

Im obigen Beispiel enthält Zelle C13 ein Leerzeichen.

Wir können es mit der Längenformel herausfinden. Geben Sie nach dem obigen 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 die D13-Zelle 1 Zeichen anzeigt, aber das C13 kein Zeichen anzeigt, was uns zeigt, dass sich in der C13-Zelle ein unsichtbares Zeichen befindet.

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

Schritt 2: Überprüfen der Non-Blank-Zählung

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

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

=ZÄHLENWENN(B4:C15,””)

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

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

=ZEILEN(B4:C15)*SPALTEN(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

Wie Leerzeichen in einer Zelle wird auch ein versteckter Apostroph nicht in der Zelle angezeigt. Wir können die Längenfunktion nicht verwenden, da das Apostroph ausgeblendet ist, die Längenfunktion zeigt das Apostroph nicht als Zeichen an.

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

=ZÄHLENWENN(B4:C15,”<>“)

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

Lassen Sie uns nun die Längenformel ausprobieren, die jedoch für alle leeren Zellen die Zeichen 0 anzeigt.

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

Lösung: Verwenden Sie Multiplizieren mit 1, um den versteckten Apostroph zu finden

Wir können das Format des Apostrophs verwenden, 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 F4-Zelle eine Multiplizieren-mit-1-Formel 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 einen versteckten Apostroph in der Bearbeitungsleiste bemerken.

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

Kopieren Sie die Formel „Multiplizieren mit 1“ in andere Zellen und einen Wertfehler aufgrund eines Apostrophs in der C9-Zelle

Problem 3: Das Problem mit der leeren Zeichenkette (=””)

Wie Leerzeichen und Apostrophe wird auch die leere Zeichenkette (=””) nicht in einer Zelle angezeigt.

Die Längenfunktion zeigt nicht die Länge der leeren Zeichenfolge an, aber die Methode Multiplizieren mit 1, wie oben im Abschnitt Versteckter 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 sich die Ausgabe von Countif not blank um 1 erhöht hat und 20 geworden ist, aber die C14-Zelle ist sichtbar leer. Geben Sie nun folgende Formel in die Zelle E4 ein:

=LEN(C4)

Kopieren Sie dann die Formel in die Zelle E15, aber die Zelle E14 zeigt null 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 Multiplizieren mit 1, um die leere Zeichenfolge zu finden

Geben Sie nun folgende Formel in die Zelle F4 ein:

=C4*1

Kopieren Sie dann die Formel bis zur Zelle F15 und Sie werden sofort feststellen, dass die Zelle F14 einen #Wert-Fehler angezeigt hat.

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

Finden einer leeren String-Zelle durch Multiplizieren mit 1 Formel

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

Sie können auch die Methode Multiplizieren mit 1 verwenden, um die Zellen mit Leerzeichen zu finden.

Problemumgehung für alle Probleme: Verwendung von SUMPRODUCT

Die oben genannten Problemumgehungen zum Beheben der Dateninkonsistenzen sind sehr effizient, können sich jedoch als zu langwierig erweisen, wenn Sie es mit großen Datensätzen zu tun haben. Im folgenden Beispiel haben wir dasselbe Problem wie zuvor verwendet und Zellen enthalten versteckte Werte (leere Zeichenfolgen und versteckte Apostrophe).

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

=SUMMENPRODUKT((TRIM(B4:C15)<>“”)*1)Sumproduct-Funktion, die die richtige Antwort anzeigt, während Countif Not Blank falsches Ergebnis aufgrund unsichtbarer nicht leerer Zellen anzeigt

Jetzt sehen Sie die F4-Zelle, die das Ergebnis von 19 anzeigt, das tatsächliche Ergebnis von 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 kennzeichnen, dass die Zellen nicht leer sind.
• Dann konvertiert (TRIM(B4:C15)<>””)*1 die boolesche Ausgabe (Wahr für nicht leer, Falsch für leer) in ihr algebraisches Gegenstück, dh 1 für Wahr und 0 für Falsch.
• Jetzt wird das SUMPRODUCT Arrays multiplizieren und summieren, was hier 19 ergibt.

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