Anzahl der Zellen zählen, die nicht vielen Werten in Excel entsprechen

In Excel können Sie die Anzahl der Zellen, die einem bestimmten Wert nicht entsprechen, ganz einfach mithilfe der ZÄHLENWENN-Funktion ermitteln. Doch haben Sie schon einmal versucht, Zellen zu zählen, die mehreren Werten nicht entsprechen? Angenommen, Sie möchten die Gesamtanzahl der Produkte in Spalte A ermitteln, dabei aber bestimmte Artikel aus dem Bereich C4:C6 ausschließen – wie im folgenden Screenshot gezeigt. In diesem Artikel stellen wir Ihnen einige Formeln vor, mit denen Sie diese Aufgabe in Excel mühelos bewältigen können.

• Anzahl der Zellen, die nicht vielen Werten entsprechen, mithilfe Formel 1 zählen
• Anzahl der Zellen, die nicht vielen Werten entsprechen, mithilfe Formel 2 zählen

Anzahl der Zellen, die nicht vielen Werten entsprechen, mithilfe Formel 1 zählen

Um Zellen zu zählen, die nicht mit mehreren angegebenen Werten übereinstimmen, verwenden Sie die Kombination der Funktionen MATCH, ISNA und SUMMENPRODUKT. Die allgemeine Syntax lautet:

• range: Die Liste der Zellen, die Sie zählen möchten;
• exclude_values: Die Werte, die aus der Bereichsliste ausgeschlossen werden sollen.

1. Geben Sie die folgende Formel in eine leere Zelle ein oder fügen Sie sie dort ein, wo das Ergebnis angezeigt werden soll:

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

Erklärung der Formel:

• MATCH(A2:A13;C2:C4;0): Diese MATCH-Funktion gibt die Position jedes übereinstimmenden Werts als Zahl zurück und liefert den Fehlerwert #N/A, wenn kein Treffer gefunden wird. Das heißt: Sobald ein Wert aus A2:A13 einem Wert in C2:C4 entspricht, wird dessen Position als Zahl ausgegeben – andernfalls erscheint der Fehler #N/A. Das Ergebnis lautet hier: {1;#N/A;#N/A;1;3;2;#N/A;#N/A;3;#N/A;#N/A;#N/A}.
• ISNA(MATCH(A2:A13;C2:C4;0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}): Die ISNA-Funktion wandelt dabei Fehlerwerte in WAHR und Zahlen in FALSCH um – so erhalten Sie folgendes Ergebnis: {FALSCH;WAHR;WAHR;FALSCH;FALSCH;FALSCH;WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR}.
• --(ISNA(MATCH(A2:A13;C2:C4;0))))=--({FALSCH;WAHR;WAHR;FALSCH;FALSCH;FALSCH;WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR}): Der doppelte Minus-Operator (--) wandelt alle WAHR-Werte in 1 und alle FALSCH-Werte in 0 um, wie folgt: SUMMENPRODUKT(--(ISNA(MATCH(A2:A13;C2:C4;0))))=SUMMENPRODUKT({0;1;1;0;0;0;1;1;0;1;1;1}).
• Anschließend wird die SUMMENPRODUKT-Funktion verwendet, um die Zahlen im Array zu summieren, und liefert das Ergebnis: 7.

Anzahl der Zellen, die nicht vielen Werten entsprechen, mithilfe Formel 2 zählen

Um diese Aufgabe in Excel zu lösen, können Sie alternativ auch die Kombination der Funktionen ZÄHLENW, SUMMENPRODUKT und ZÄHLENWENN verwenden. Die allgemeine Syntax lautet:

• range: Die Liste der Zellen, die Sie zählen möchten;
• exclude_values: Die Werte, die aus der Bereichsliste ignoriert werden sollen.

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

2. Drücken Sie anschließend Enter, um das Ergebnis wie im folgenden Screenshot gezeigt zu erhalten:

Erklärung der Formel:

• ZÄHLENW(A2:A13): Diese ZÄHLENW-Funktion gibt die Anzahl aller nicht leeren Zellen im Bereich A2:A13 zurück. Das Ergebnis lautet: 12.
• ZÄHLENWENN(A2:A13;C2:C4): Diese ZÄHLENWENN-Funktion zählt die Zellen im Bereich A2:A13, die den Kriterien in C2:C4 entsprechen. Das Ergebnis lautet daher {2;1;2} – das bedeutet: Apfel kommt zweimal vor, Traube einmal und Zitrone zweimal.
• SUMMENPRODUKT(ZÄHLENWENN(A2:A13;C2:C4))=SUMMENPRODUKT({2;1;2}): Diese SUMMENPRODUKT-Funktion summiert alle Elemente des Arrays und liefert das Endergebnis: 5.
• ZÄHLENW(A2:A13)-SUMMENPRODUKT(ZÄHLENWENN(A2:A13;C2:C4))=12-5: Das Ergebnis der SUMMENPRODUKT-Funktion (5) wird von der Gesamtanzahl der nicht leeren Zellen (12) abgezogen, um das gewünschte Endergebnis zu erhalten.

Hinweis: Beide oben genannten Formeln berücksichtigen keine Groß- und Kleinschreibung.

Verwendete verwandte Funktion:

Weitere Artikel: