Anzahl der Zellen zählen, die viele Werte nicht enthalten

Normalerweise ist es ganz einfach, Zellen zu zählen, die einen bestimmten Wert nicht enthalten – dazu eignet sich perfekt die COUNTIF-Funktion. In diesem Beitrag erfahren Sie Schritt für Schritt, wie Sie die Anzahl der Zellen ermitteln, die zahlreiche Werte in einem begrenzten Bereich in Excel nicht enthalten.

Wie zählt man Zellen, die zahlreiche Werte nicht enthalten?

Wie im folgenden Screenshot gezeigt, zählen Sie die Zellen in B3:B11, die keine der in D3:D4 aufgeführten Werte enthalten, wie folgt:

Allgemeine Formel

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

Argumente

Hinweis: Diese Formel muss als Matrixformel eingegeben werden. Nach der Eingabe erscheinen geschweifte Klammern um die Formel, sobald die Matrixformel erfolgreich erstellt wurde.

Wie verwendet man diese Formel?

1. Wählen Sie eine leere Zelle aus, um das Ergebnis anzuzeigen.

2. Geben Sie die folgende Formel ein und drücken Sie anschließend gleichzeitig Strg+Umschalt+Eingabetaste, um das Ergebnis zu erhalten.

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

Wie funktionieren diese Formeln?

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):

• TRANSPOSE(D3:D4): Die TRANSPOSE-Funktion dreht die Ausrichtung von D3:D4 und gibt {„count“,„blank“};
• SEARCH({„count“;„blank“},B3:B11): Die SEARCH-Funktion sucht hier die Position der Teilzeichenfolgen „count“ und „blank“ im Bereich B3:B11 und gibt ein Array zurück als {#WERT!;#WERT!;#WERT!;#WERT!;1;#WERT!;#WERT!;8;1;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;1;
  #WERT!;1;7}.
• In diesem Fall wird jede Zelle im Bereich B3:B11 zweimal durchsucht, da zwei Werte beim Zählen ausgeschlossen werden sollen – daher enthält das Array insgesamt 18 Werte. Jede Zahl darin gibt die Position des ersten Zeichens von „count“ bzw. „blank“ in der jeweiligen Zelle an.
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}: Die ISNUMBER-Funktion gibt WAHR zurück, wenn Zahlen im Array gefunden werden, und FALSCH, wenn Fehler auftreten. Hier wird das Ergebnis wie folgt zurückgegeben:{FALSCH,FALSCH;FALSCH,FALSCH;WAHR,FALSCH;FALSCH,WAHR;WAHR,FALSCH;FALSCH,FALSCH;FALSCH,FALSCH;WAHR,FALSCH;
  WAHR,WAHR}.
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,)
  FALSE;TRUE,TRUE}): Diese beiden Minuszeichen wandeln „WAHR“ in 1 und „FALSCH“ in 0 um. Hier erhalten Sie ein neues Array als {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2)ROW(D3:D4)^0: Die ROW-Funktion gibt die Zeilennummern des angegebenen Zellbereichs zurück: {3;4}. Anschließend liefert der Potenzoperator (^) das Ergebnis, wenn die Zahlen 3 und 4 mit dem Exponenten 0 potenziert werden – und ergibt schließlich {1;1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}): Die MMULT-Funktion liefert das Matrixprodukt dieser beiden Arrays: {0;0;1;1;1;0;0;1;2}, um die Originaldaten abzugleichen. Jede Zahl ungleich null im Array zeigt an, dass mindestens eine der ausgeschlossenen Zeichenfolgen gefunden wurde; eine Null bedeutet, dass keine der ausgeschlossenen Zeichenfolgen enthalten ist.

4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):

• {0;0;1;1;1;0;0;1;2}>0: Hier wird geprüft, ob jede Zahl im Array größer als 0 ist. Ist eine Zahl größer als 0, wird WAHR zurückgegeben – andernfalls FALSCH. Das Ergebnis ist ein neues Array: {FALSCH;FALSCH;WAHR;WAHR;WAHR;FALSCH;FALSCH;WAHR;WAHR}.
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}: Da nur die Zellen gezählt werden sollen, die die angegebenen Werte nicht enthalten, müssen diese Werte im Array durch Subtraktion von 1 invertiert werden. Der mathematische Operator wandelt dabei automatisch WAHR- und FALSCH-Werte in 1 und 0 um und liefert schließlich das Ergebnis als {1;1;0;0;0;1;1;0;0}.
• SUM{1;1;0;0;0;1;1;0;0}Die SUM-Funktion addiert alle Zahlen im Array und liefert als Endergebnis 4 zurück.

Verwandte Funktionen

Verwandte Formeln