Mit Hilfe einer Matrixformel möchte ich diesmal herausfinden, wieviele unterschiedliche Hersteller von Druckern wir im Einsatz haben. Als Vorlage habe ich eine, für dieses Beispiel stark verkürzte, Excel-Liste aller Geräte.

Gleich ein erster Versuch mit der Matrixformel in Zelle A18 die Formel „=Zählenwenn(A1:A17;A1:A17)“ eingeben und mit <Strg>+<Shift>+<Return> abschließen.

Und das Ergebnis ist 3. 3? Na, da stimmt doch was nicht.

Jetzt muss man wissen, wie die Matrixformel funktioniert.
Die Formel „Zählenwenn“ hat schon eine Bereichsvorgabe (=Zählenwenn(Bereich;Bezug)) und erhält jetzt, anstatt eines Zellbezuges, eine weitere Bereichsvorgabe für die Matrixformelfunktion (=Zählenwenn(Bereich;Bezug)). Dadurch wird das „Zählenwenn“ so oft ausgeführt, wie Zeilen im „Bezugs“-Bereich vorhanden sind und wir erhalten das Ergebnis der letzten Abfrage. Im Beispiel ist dies, analog zur Formel „=Zählenwenn(A1:A17;A17)“, die Zelle A17 (=“HP“) und korrekter Weise gibt die Formel den Wert 3 aus, da „HP“ 3x in der Liste vertreten ist.
Irgendwie muss man jetzt die einzelnen Schritte der Matrixformel „erhalten“.
Einfachster Weg: aufsummieren mit (man glaubt‘s kaum) „=Summe“.

Das Ergebnis lautet 51. Wieder nicht das, was wir wollten.

Bauen wir die ganze Sache doch mal nach. Ich kopiere die Daten nochmal in die Spalte D und setze in die Spalte E eine normale „Zählenwenn“-Formel („=Zählenwenn($A$1:$A$17;D1)“ usw) und summiere am Ende die Einzelwerte.

Siehe da, auch 51.

Das kommt davon, dass das „Zählenwenn“ bei jeder Zeile ausgeführt wird. Wenn also „HP“ 3x in der Liste vorkommt, so wird 3x „HP“ mit dem Wert 3 ausgegeben, was eine Gesamtanzahl von 9 ergibt.

Gegenprobe: Ich reduziere die Zeilen in der Spalte D mit der Funktion „Duplikate entfernen“ im Reiter „Daten“ und erzeuge eine neue „Zählenwenn“-Formel in Spalte E mit der gleichen Formel wie eben („=Zählenwenn($A$1:$A$17;D1)“ usw).

Um auf das Ergebnis 51 zu kommen, muss ich jetzt jeden Wert in der Spalte E mit sich selbst multiplizieren (die Werte werden ja auch in der Matrixformel mehrfach zusammengezählt) und erzeuge am Ende dann wieder die Summe zur Kontrolle.

Soweit, so gut. Jetzt haben wir den Beweis, wie die „Zählenwenn“-Matrixformel rechnet. Ich möchte aber die Anzahl der unterschiedlichen Hersteller haben und nicht eine multiplizierte Irgendwas-Zahl.
Eine mathematische Lösung ist hier unumgänglich, denn nur diese lässt sich in einer Formel berechnen. Am einfachsten man dividiert „1“ durch die errechnete Anzahl in Spalte F und multipliziert dieses Ergebnis ebenfalls wieder mit der errechneten Anzahl aus Spalte F.
Als Beispiel nehme ich mal Zeile HP: 1/9*9 = 1

Summiert man nun die Spalte G, erhält man den Wert 6.

Und das stimmt nun auch endlich mit der wirklichen Anzahl unterschiedlicher Hersteller überein.

Jetzt verbaue ich diese Erkenntnis in die anfängliche Matrixformel.
Die Formel lautet demnach „=SUMME(1/Zählenwenn(A1:A17;A1:A17))“ (und <Strg>+<Shift>+<Return> nicht vergessen).

Das Ergebnis lautet 6 – korrekt.

Es ist nicht nur wichtig, Formeln und Funktionen zu kennen, sondern ebenso wichtig ist es zu verstehen, wie der Ablauf dieser Formeln/Funktionen ist.
Ich hoffe, dass ich mit diesem Beispiel die Funktionsweise einer Matrixformel etwas aufzeigen konnte.