Tipp getestet unter Excel 2003, 2007, 2010.

Wenn Sie z.B. eine Sportveranstaltung planen und wissen wollen, aus wie vielen verschiedenen Vereinen die Teilnehmer kommen, müssen Sie herausfinden, wie viele unterschiedliche Einträge ein Tabellenbereich enthält (in diesem Fall die Spalte, die die Vereine neben den Teilnehmernamen auflistet).

Um das Problem zu lösen, analysieren Sie die Liste mit einer Array-Formel:

• Angenommen, die Vereine der Teilnehmer stehen in den Zellen B2 bis B50, dann geben Sie in die Zelle, die die Anzahl der verschiedenen Vereine ausgeben soll, folgende Formel ein:

  =SUMME(1/WENN(ZÄHLENWENN(B2:B50;B2:B50)=0;1;ZÄHLENWENN(B2:B50;B2:B50)))-ANZAHLLEEREZELLEN(B2:B50)

• Schließen Sie die Eingabe aber nicht mit der -Taste ab, sondern drücken Sie + + . Diese Tastenkombination erzeugt eine Arrayformel. Excel trägt den Ausdruck daraufhin mit geschwungenen Klammern ein:

  {=SUMME(1/WENN(ZÄHLENWENN(B2:B50;B2:B50)=0;1;ZÄHLENWENN(B2:B50;B2:B50)))-ANZAHLLEEREZELLEN(B2:B50)}

• Erklärungen zur Formel:
  • Die ZÄHLENWENN-Funktion vergleicht den zu analysierenden Bereich mit sich selbst, sodass Sie ein Array erhalten, in dem die Häufigkeiten jedes einzelnen Eintrags stehen. Wenn ein Eintrag zum Beispiel fünf Mal auftaucht, steht an jeder Position dieses Eintrags die Zahl 5. Ein Wert, der nur einmal vorkommt, erhält das ZÄHLENWENN-Ergebnis 1.
  • Indem Sie 1 durch die einzelnen Häufigkeiten teilen, weisen Sie den Arraywerten gewissermaßen eine Wertigkeit zu. Bei einem ZÄHLENWENN-Ergebnis von 5 ist beispielsweise jeder einzelne nur ein Fünftel wert und alle zusammen ergeben 1. Die Summe aller Wertigkeiten entspricht somit der Anzahl der unterschiedlichen Listeneinträge.
  • Mit der WENN-Funktion zwingen Sie die Formel, für jede Leerzelle im Bereich (ein Teilnehmer gehört zu keinem Verein) die Zahl 1 auszugeben. Da diese Zellen aber gar nicht gezählt werden sollen, ziehen Sie am Ende die Anzahl der leeren Zellen des Bereichs wieder ab (Funktion ANZAHLLEEREZELLEN).