Zählen Sie eindeutige Werte in Excel mit der COUNTIF-Funktion

In diesem Tutorial erfahren Sie, wie Sie eindeutige Werte in Excel mithilfe von Formeln zählen (COUNTIF-und SUMPRODUCT-Funktionen).

So zählen Sie eindeutige Werte in Excel

Angenommen, wir haben einen Datensatz wie unten gezeigt:

Zum Zweck dieses Tutorials werde ich den Bereich A2:A10 als NAMEN benennen. In Zukunft werden wir diesen benannten Bereich in den Formeln verwenden.

See Also: How to create Named Ranges in Excel.

In diesem Datensatz gibt es eine Wiederholung im Namensbereich., Um die Anzahl der eindeutigen Namen aus diesem Datensatz (A2:A10) zu erhalten, können wir eine Kombination aus COUNTIF-und SUMPRODUCT-Funktionen verwenden,wie unten gezeigt:

=SUMPRODUCT(1/COUNTIF(NAMEN, NAMEN))

Wie funktioniert diese Formel?

Lassen Sie uns diese Formel aufschlüsseln,um ein besseres Verständnis zu erhalten:

• COUNTIF(NAMES, NAMES)
  • Dieser Teil der Formel gibt ein Array zurück. Im obigen Beispiel wäre es {2;2;3;1;3;1;2;3;2}. Die Zahlen hier geben an, wie oft ein Wert im angegebenen Zellbereich auftritt.,
    Zum Beispiel ist der Name Bob, der zweimal in der Liste vorkommt, daher würde er die Nummer 2 für Bob zurückgeben. In ähnlicher Weise tritt Steve dreimal auf und daher wird 3 für Steve zurückgegeben.
• 1/ZÄHLENWENN(Namen,NAMEN)
  • Dieser Teil der Formel würde ein array zurückgeben– {0.5;0.5;0.333333333333333;1;0.333333333333333;1;0.5;0.333333333333333;0.5}
    Da haben wir geteilt 1 durch die Reihe, es gibt dieses array.
    Zum Beispiel war das erste Element des oben zurückgegebenen Arrays 2. Wenn 1 durch 2 geteilt wird, kehrt es zurück .5.,
• SUMPRODUCT(1/ZÄHLENWENN(Namen,NAMEN))
  • SUMPRODUCT-Funktion fügt einfach alle diese zahlen. Beachten Sie, dass, wenn Bob zweimal in der Liste auftritt, das obige Array zurückkehrt .5. Bob Name erschien in der Liste. Ebenso, da Steve dreimal in der Liste erscheint, kehrt das Array zurück .333333333 wann immer Steve Name erscheint. Wenn wir die Zahlen für jeden Namen hinzufügen, wird immer 1 zurückgegeben. Wenn wir alle Zahlen hinzufügen, wird die Gesamtzahl der eindeutigen Namen in der Liste zurückgegeben.

Diese Formel funktioniert einwandfrei, bis keine leeren Zellen im Bereich vorhanden sind., Aber wenn Sie leere Zellen haben, würde es ein #DIV/0 zurückgeben! Fehlermeldung.

Wie gehe ich mit LEEREN Zellen um?

Lassen Sie uns zunächst verstehen, warum ein Fehler zurückgegeben wird, wenn eine leere Zelle im Bereich vorhanden ist. Angenommen, wir haben den Datensatz wie unten gezeigt (wobei Zelle A3 leer ist):

Und da wir 1 durch dieses gesamte Array teilen, wird ein #DIV/0 zurückgegeben! Fehlermeldung.,

Um diesen Teilungsfehler bei leeren Zellen zu behandeln, verwenden Sie die folgende Formel:

=SUMPRODUCT ((1/COUNTIF (NAMES,NAMES&“)))

Eine Änderung, die wir an dieser Formel vorgenommen haben, ist der Kriterienteil der COUNTIF-Funktion. Wir haben NAMEN&““ anstelle von NAMEN. Auf diese Weise gibt die Formel die Anzahl der leeren Zellen zurück (früher wurde 0 zurückgegeben, wenn eine leere Zelle vorhanden war).

HINWEIS: Diese Formel zählt leere Zellen als eindeutigen Wert und gibt sie im Ergebnis zurück.,

Im obigen Beispiel sollte das Ergebnis 5 sein, aber es wird 6 zurückgegeben, da die leere Zelle als einer der eindeutigen Werte gezählt wird.

Hier ist die Formel,die sich um die leeren Zellen kümmert und sie im Endergebnis nicht zählt:

=SUMPRODUCT((NAMES<>““)/COUNTIF(NAMES, NAMES&““))

In dieser Formel haben wir anstelle von 1 als Zähler die NAMEN<>““., Dies gibt ein Array von Wahrheiten und Fälschungen zurück. Es gibt FALSE zurück, wenn eine leere Zelle vorhanden ist. Da TRUE in Berechnungen gleich 1 und FALSE gleich 0 ist, werden leere Zellen nicht gezählt, da der Zähler 0 (FALSE) ist.

Nachdem wir nun das Grundgerüst der Formel bereit haben, können wir noch einen Schritt weiter gehen und verschiedene Datentypen zählen.

So zählen Sie eindeutige Werte in Excel, die Text sind

Wir werden dasselbe oben diskutierte Konzept verwenden, um die Formel zu erstellen, die nur eindeutige Textwerte zählt.,

Hier ist die Formel, die eindeutige Textwerte in Excel zählt:

=SUMPRODUCT ((ISTEXT(NAMES)/COUNTIF (NAMES,NAMES&“)))

Wir haben nur die Formel ISTEXT (NAMES) als Zähler verwendet. Es gibt TRUE zurück, wenn die Zelle text enthält, und FALSCH, wenn nicht. Es zählen nicht leere Zellen, aber die zählen Zellen, die eine leere Zeichenfolge („“).,

So zählen Sie eindeutige Werte in Excel, die numerisch sind

Hier ist die Formel,die eindeutige numerische Werte in Excel zählt

=SUMPRODUCT((ISNUMBER(NAMES))/COUNTIF(NAMES, NAMES&“))

Hier verwenden wir ISNUMBER(NAMES) als Zähler. Es gibt TRUE zurück, wenn die Zelle enthält numerische Daten Typ ist, und FALSE, wenn nicht. Es zählt nicht leere Zellen.

Sie können auch die folgenden Excel-Tutorials mögen:

• So zählen Sie Zellen, die Textzeichenfolgen enthalten.
• So zählen Sie die Anzahl der Wörter in Excel.,
• Zählen Sie Zellen basierend auf der Hintergrundfarbe in Excel.
• Mit mehreren Kriterien in Excel COUNTIF und COUNTIFS Funktion.