Anzahl der Zeilen zählen, die bestimmte Werte in Excel enthalten

Es ist oft einfach, die Anzahl der Zellen mit einem bestimmten Wert in einem Excel-Arbeitsblatt zu zählen. Doch die Ermittlung der Anzahl der Zeilen, die bestimmte Werte enthalten, kann recht kompliziert sein. In solchen Fällen hilft eine leistungsstarke Formel, die auf den Funktionen SUMME, MMULT, TRANSPOSE und COLUMN basiert. Dieses Tutorial zeigt Schritt für Schritt, wie Sie diese Formel erstellen, um die Aufgabe in Excel effizient zu meistern.

• Anzahl der Zeilen zählen, die bestimmte Werte enthalten

Anzahl der Zeilen zählen, die bestimmte Werte enthalten

Beispielsweise haben Sie einen Wertebereich in einem Arbeitsblatt und müssen nun die Anzahl der Zeilen mit dem vorgegebenen Wert „300“ zählen, wie im folgenden Screenshot dargestellt:

Um die Anzahl der Zeilen zu ermitteln, die bestimmte Werte enthalten, lautet die allgemeine Syntax:

• data: Der Zellbereich, der auf das Vorhandensein des spezifischen Werts überprüft werden soll;
• XDer spezifische Wert, anhand dessen die Zeilen gezählt werden sollen.

1. Geben Sie die folgende Formel in eine leere Zelle ein oder kopieren Sie sie dorthin, wo das Ergebnis erscheinen soll:

2. Drücken Sie anschließend gleichzeitig Strg + Umschalt + Enter, um das korrekte Ergebnis zu erhalten (siehe Screenshot):

Erläuterung der Formel:

=SUMME(--(MMULT(--($A$2:$C$12=300);TRANSPOSE(COLUMN($A$2:$C$12)))>0))

• --$A$2:$C$12=300: Dieser Ausdruck prüft, ob der Wert „300“ im Bereich A2:C12 vorkommt, und liefert ein Array aus WAHR- und FALSCH-Werten. Das doppelte Minuszeichen wandelt WAHR in 1 und FALSCH in 0 um – das Ergebnis lautet somit: {0,0,0;1,0,0;0,0,0;0,1,1;0,0,0;0,1,0;0,0,0;1,0,0;0,0,1;0,0,0;1,1,1}. Dieses Array mit 11 Zeilen und 3 Spalten wird als array1 in der MMULT-Funktion verwendet.
• TRANSPOSE(COLUMN($A$2:$C$12)): Die COLUMN-Funktion ruft hier die Spaltennummern des Bereichs A2:C12 ab und liefert ein dreispaltiges Array der Form {1,2,3}. Anschließend wandelt die TRANSPOSE-Funktion dieses Array in ein dreizeiliges Array {1;2;3} um, das innerhalb der MMULT-Funktion als array2 verwendet wird.
• MMULT(--($A$2:$C$12=„Joanna"),TRANSPOSE(COLUMN($A[[PH_39]]:$C[[PH_38]]))): Diese MMULT-Funktion berechnet das Matrixprodukt der beiden oben genannten Arrays und liefert folgendes Ergebnis: {0;1;0;5;0;2;0;1;3;0;6}.
• SUM(--(MMULT(--($A$2:$C$12=„Joanna"),TRANSPOSE(COLUMN($A[[PH_67]]:$C[[PH_66]])))>0))= SUM(--{0;1;0;5;0;2;0;1;3;0;6}>0): Zunächst prüft die Formel, ob die Werte im Array größer als 0 sind: Ist ein Wert größer als 0, ergibt dies WAHR; ist er kleiner oder gleich 0, ergibt dies FALSCH. Anschließend wandelt das doppelte Minuszeichen (--) diese WAHR-/FALSCH-Werte in 1 und 0 um, sodass Sie Folgendes erhalten: SUM({0;1;0;1;0;1;0;1;1;0;1}). Schließlich summiert die SUM-Funktion alle Werte im Array und liefert das Endergebnis: 6.

Tipps:

Wenn Sie die Anzahl der Zeilen zählen müssen, die einen bestimmten Text in einem Arbeitsblatt enthalten, wenden Sie bitte die folgende Formel an und drücken Sie zum Erhalt der Gesamtanzahl unbedingt gleichzeitig Strg + Umschalt + Enter:

Verwendete verwandte Funktion:

Weitere Artikel: