=ANZAHL2(EINDEUTIG(A2:A9)) zählt, wie viele verschiedene Werte in A2:A9 stehen. EINDEUTIG (englisch UNIQUE) liefert jeden Wert einmal, und ANZAHL2 (englisch COUNTA) zählt diese Liste. Das braucht Excel 2021 oder Microsoft 365; ältere Versionen kommen weiter unten. Die Tabelle zeigt die Formel in englischer Schreibweise, =COUNTA(UNIQUE(A2:A9)); du kannst die Formeln in der Tabelle auch deutsch eingeben, mit Semikolons.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Unique list | Count | |
| 2 | Ana | Ana | 5 | |
| 3 | Ben | Ben | ||
| 4 | Ana | Cara | ||
| 5 | Cara | Dan | ||
| 6 | Ben | Eva | ||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
=ANZAHL2(EINDEUTIG(A2:A9))Acht Bestellungen kamen von fünf Kunden. C2 gibt die Liste der Namen aus EINDEUTIG als Überlauf aus, damit du siehst, was gezählt wird, und D2 zählt sie, ohne die Liste auf dem Blatt zu brauchen. Ändere A9 in Ana, und die Zahl sinkt auf 4; tippe einen neuen Namen, und sie steigt.
EINDEUTIG ignoriert die Schreibweise, also zählen Ana und ana als ein Kunde.
Eindeutige Werte in älterem Excel zählen
Excel 2019 und älter haben kein EINDEUTIG. Die klassische Formel lautet:
=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
In deutschem Excel: =SUMMENPRODUKT(1/ZÄHLENWENN(A2:A9;A2:A9)).
ZÄHLENWENN mit dem ganzen Bereich als Kriterium liefert für jede Zeile, wie oft der Wert dieser Zeile vorkommt. Ein Name, der dreimal vorkommt, bekommt in jeder seiner Zeilen 3, also wird 1/3 dreimal addiert, und der Name ergibt genau 1. Spalte B zeigt die Anzahl für jede Zeile und Spalte C den Bruch.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Times | 1/Times | Count | |
| 2 | Ana | 3 | 0.33 | 5 | |
| 3 | Ben | 2 | 0.50 | 5.00 | |
| 4 | Ana | 3 | 0.33 | ||
| 5 | Cara | 1 | 1.00 | ||
| 6 | Ben | 2 | 0.50 | ||
| 7 | Dan | 1 | 1.00 | ||
| 8 | Ana | 3 | 0.33 | ||
| 9 | Eva | 1 | 1.00 |
=SUMMENPRODUKT(1/ZÄHLENWENN(A2:A9;A2:A9))Anas drei Zeilen addieren je 0.33, Bens zwei Zeilen je 0.50, und die drei einzelnen Namen je 1: insgesamt 5, genau wie die SUMME der Hilfsspalte. Bei Zehntausenden von Zeilen ist diese Formel langsam, weil ZÄHLENWENN den ganzen Bereich einmal pro Zeile durchsucht; EINDEUTIG hat diesen Aufwand nicht.
Verschieden oder einmalig: Werte, die nur einmal vorkommen
"Eindeutig" steht für zwei verschiedene Zählungen. Die obige zählt verschiedene Werte: jeden Namen einmal. Die andere zählt die Werte, die genau einmal vorkommen, etwa Kunden, die nur ein einziges Mal bestellt haben. EINDEUTIG erledigt das, wenn sein drittes Argument, exactly_once, auf WAHR steht.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Count | Result | |
| 2 | Ana | Distinct | 5 | |
| 3 | Ben | Exactly once | 3 | |
| 4 | Ana | Exactly once, older Excel | 3 | |
| 5 | Cara | |||
| 6 | Ben | |||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
=ANZAHL2(EINDEUTIG(A2:A9;;WAHR))Fünf verschiedene Kunden, aber nur drei davon, Cara, Dan und Eva, haben einmal bestellt. Die Variante für älteres Excel zählt die Zeilen, deren ZÄHLENWENN genau 1 ist. Wiederholt sich jeder Wert, liefert EINDEUTIG mit exactly_once #KALK! (englisch #CALC!; die Tabellen hier zeigen die englischen Fehlernamen), und ANZAHL2 zählt diesen Fehler als 1; die Variante mit SUMMENPRODUKT liefert 0.
Eindeutige Werte mit einer Bedingung zählen
Um die verschiedenen Kunden in einer Region zu zählen, filterst du erst die Zeilen und zählst dann, was übrig bleibt. FILTER behält die North-Zeilen, und EINDEUTIG entfernt die Wiederholungen.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Region | Region | Customers | |
| 2 | Ana | North | North | 3 | |
| 3 | Ben | South | South | 3 | |
| 4 | Ana | North | North, older Excel | 3 | |
| 5 | Cara | North | |||
| 6 | Ben | North | |||
| 7 | Dan | South | |||
| 8 | Ana | North | |||
| 9 | Eva | South |
=ANZAHL2(EINDEUTIG(FILTER(A2:A9;B2:B9=D2)))North hat fünf Bestellungen von drei Kunden: Ana, Cara und Ben. E3 zählt South auf dieselbe Weise. E4 ist die Variante für Excel 2019 und älter: ZÄHLENWENNS zählt jedes Paar aus Kunde und Region, und die Bedingung behält nur die Brüche für North.
Passt keine Zeile, liefert FILTER #CALC!, und ANZAHL2 zählt diesen Fehler als einen Wert: Tippe West in D2, und E2 zeigt 1, nicht 0. Die Formel in WENNFEHLER zu packen, hilft nicht, weil ANZAHL2 keinen Fehler liefert. Zähle stattdessen die Zeilen des Ergebnisses, das den Fehler weitergibt: =WENNFEHLER(ZEILEN(EINDEUTIG(FILTER(A2:A9;B2:B9="West")));0) liefert 0.
Eindeutige Werte zählen und leere Zellen auslassen
Eine leere Zelle im Bereich wird zu einem weiteren "Wert". EINDEUTIG liefert sie als 0, und ANZAHL2 zählt diese 0, also liefert Excel für Ana, eine leere Zelle, Ben, Ana, eine leere Zelle, Cara und Ben:
=COUNTA(UNIQUE(A2:A8)) 4 three names plus the 0 for the empty cells
In der älteren Formel lässt eine leere Zeile ZÄHLENWENN 0 liefern, also ergibt 1/0 #DIV/0!. Entferne die leeren Zellen zuerst:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Formula | Count | |
| 2 | Ana | Skip blanks | 3 | |
| 3 | Older Excel | 3 | ||
| 4 | Ben | |||
| 5 | Ana | |||
| 6 | ||||
| 7 | Cara | |||
| 8 | Ben |
=ANZAHL2(EINDEUTIG(FILTER(A2:A8;A2:A8<>"")))Beide Formeln zählen die drei Kunden. FILTER mit A2:A8<>"" entfernt die leeren Zellen, bevor EINDEUTIG sie sieht. In der älteren Formel macht A2:A8&"" aus jeder leeren Zelle eine leere Zeichenfolge, sodass ZÄHLENWENN nie 0 liefert, und (A2:A8<>"") gibt diesen Zeilen das Gewicht 0.
Übung: die Produkte zählen
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Count | Result | |
| 2 | 1001 | Apple | Products | ||
| 3 | 1002 | Pear | |||
| 4 | 1003 | Apple | |||
| 5 | 1004 | Plum | |||
| 6 | 1005 | Pear | |||
| 7 | 1006 | Apple | |||
| 8 | 1007 | Plum | |||
| 9 | 1008 | Fig |
Du bist dran: Zähle, wie viele verschiedene Produkte in B2:B9 vorkommen. Schreib die Formel in E2.
Welche Formel für dein Excel
| Zählen | Excel 365 / 2021 | Excel 2019 und älter |
|---|---|---|
| Verschiedene Werte | =ANZAHL2(EINDEUTIG(A2:A9)) | =SUMMENPRODUKT(1/ZÄHLENWENN(A2:A9;A2:A9)) |
| Werte, die einmal vorkommen | =ANZAHL2(EINDEUTIG(A2:A9;;WAHR)) | =SUMMENPRODUKT(--(ZÄHLENWENN(A2:A9;A2:A9)=1)) |
| Verschiedene, mit einer Bedingung | =ANZAHL2(EINDEUTIG(FILTER(A2:A9;B2:B9="North"))) | =SUMMENPRODUKT((B2:B9="North")/ZÄHLENWENNS(A2:A9;A2:A9;B2:B9;B2:B9)) |
| Verschiedene, ohne leere Zellen | =ANZAHL2(EINDEUTIG(FILTER(A2:A9;A2:A9<>""))) | =SUMMENPRODUKT((A2:A9<>"")/ZÄHLENWENN(A2:A9;A2:A9&"")) |
In einer PivotTable erledigt die Zusammenfassung "Diskrete Anzahl" (englisch Distinct Count) dasselbe ohne Formel, aber nur, wenn die PivotTable mit aktiviertem Häkchen "Dem Datenmodell diese Daten hinzufügen" erstellt wurde. Um die Wiederholungen zu löschen, statt sie zu zählen, siehe Duplikate entfernen.
Häufig gestellte Fragen
Wie zähle ich eindeutige Werte in Excel?
In Excel 365 oder 2021 nimmst du =ANZAHL2(EINDEUTIG(A2:A9)): EINDEUTIG listet jeden Wert einmal auf, und ANZAHL2 zählt die Liste. In älteren Versionen nimmst du =SUMMENPRODUKT(1/ZÄHLENWENN(A2:A9;A2:A9)).
Wie zähle ich Werte, die nur einmal vorkommen?
Setz das dritte Argument von EINDEUTIG, exactly_once, auf WAHR: =ANZAHL2(EINDEUTIG(A2:A9;;WAHR)). Für Ana, Ana, Ben ergibt das 1, weil nur Ben einmal vorkommt. In Excel 2019 und älter nimmst du =SUMMENPRODUKT(--(ZÄHLENWENN(A2:A9;A2:A9)=1)).
Wie zähle ich eindeutige Werte mit einer Bedingung?
Erst filtern, dann zählen: =ANZAHL2(EINDEUTIG(FILTER(A2:A9;B2:B9="North"))) zählt die verschiedenen Kunden in den North-Zeilen. Passt keine Zeile, zählt ANZAHL2 den Fehler #KALK! von FILTER als 1; kann das passieren, nimmst du =WENNFEHLER(ZEILEN(EINDEUTIG(FILTER(A2:A9;B2:B9="North")));0).
Wie zähle ich eindeutige Werte und lasse leere Zellen aus?
Entferne die leeren Zellen vor EINDEUTIG: =ANZAHL2(EINDEUTIG(FILTER(A2:A9;A2:A9<>""))). In älterem Excel überspringt =SUMMENPRODUKT((A2:A9<>"")/ZÄHLENWENN(A2:A9;A2:A9&"")) sie.
