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

In Excel können Sie leicht die Anzahl der Zellen erhalten, die nicht einem bestimmten Wert entsprechen, indem Sie die COUNTIF-Funktion verwenden. Aber haben Sie jemals versucht, die Zellen zu zählen, die nicht gleich vielen Werten entsprechen? Zum Beispiel möchte ich die Gesamtzahl der Produkte in Spalte A erhalten, aber die spezifischen Artikel in C4:C6 wie im folgenden Screenshot ausgeschlossen haben. In diesem Artikel werde ich einige Formeln zur Lösung dieser Aufgabe in Excel vorstellen.

• Anzahl der Zellen, die nicht gleich vielen Werten entsprechen, mit Formel1 zählen
• Anzahl der Zellen, die nicht gleich vielen Werten entsprechen, mit Formel2 zählen

Anzahl der Zellen, die nicht gleich vielen Werten entsprechen, mit Formel1 zählen

Um Zellen zu zählen, die nicht gleich vielen angegebenen Werten entsprechen, können Sie die Kombination der Funktionen MATCH, ISNA und SUMPRODUCT verwenden. Die allgemeine Syntax lautet:

• Bereich: Die Liste der Zellen, die Sie zählen möchten;
• auszuschließende_Werte: Die Werte, die von der Bereichsliste ignoriert werden sollen.

1. Geben Sie die folgende Formel in eine leere Zelle ein oder kopieren Sie sie dorthin, wo Sie das Ergebnis ausgeben möchten:

2. Drücken Sie dann die Eingabetaste, um das richtige 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 gibt den Fehlerwert #NV zurück, wenn nichts gefunden wurde. Das bedeutet, wenn die Werte in A2:A13 gleich den Werten in C2:C4 sind, wird eine Zahl zurückgegeben, andernfalls wird ein #NV-Fehler zurückgegeben. Hier ist das Ergebnis: {1;#NV;#NV;1;3;2;#NV;#NV;3;#NV;#NV;#NV;}.
• ISTNV(MATCH(A2:A13;C2:C4;0))= ISTNV({1;#NV;#NV;1;3;2;#NV;#NV;3;#NV;#NV;#NV;}): Diese ISTNV-Funktion wandelt die Fehlerwerte in WAHR und die Zahlen in FALSCH um, sodass Sie dies erhalten: {FALSCH;WAHR;WAHR;FALSCH;FALSCH;FALSCH;WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR}.
• --(ISTNV(MATCH(A2:A13;C2:C4;0))))=--({FALSCH;WAHR;WAHR;FALSCH;FALSCH;FALSCH;WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR}): Der -- Doppelnegative Operator wandelt alle WAHR in 1 und FALSCH in 0 um, wie hier: SUMMENPRODUKT(--(ISTNV(MATCH(A2:A13;C2:C4;0))))=SUMMENPRODUKT({0;1;1;0;0;0;1;1;0;1;1;1;}).
• Und dann wird die SUMMENPRODUKT-Funktion verwendet, um die Zahlen im Array zu summieren und das Ergebnis:7 zu erhalten.

Anzahl der Zellen, die nicht gleich vielen Werten entsprechen, mit Formel2 zählen

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

• Bereich: Die Liste der Zellen, die Sie zählen möchten;
• auszuschließende_Werte: Die Werte, die von der Bereichsliste ignoriert werden sollen.

1. Bitte geben Sie die folgende Formel in eine leere Zelle ein oder kopieren Sie sie, um das Ergebnis zu platzieren:

2. Drücken Sie dann die Eingabetaste, um das Ergebnis wie im folgenden Screenshot gezeigt zu erhalten:

Erklärung der Formel:

• ANZAHL2(A2:A13): Diese ANZAHL2-Funktion gibt die Anzahl aller nicht leeren Zellen im Bereich A2:A13 zurück. Und es wird das Ergebnis:12 erhalten.
• ZÄHLENWENN(A2:A13;C2:C4): Diese ZÄHLENWENN-Funktion zählt die Zellen im Bereich A2:A13, die dem Kriterium in C2:C4 entsprechen, sodass Sie dieses Ergebnis erhalten: {2;1;2}, was bedeutet, dass Apfel zweimal, Traube einmal und Zitrone zweimal erscheinen.
• SUMMENPRODUKT(ZÄHLENWENN(A2:A13;C2:C4))=SUMMENPRODUKT({2;1;2}): Diese SUMMENPRODUKT-Funktion summiert alle Elemente im Array und gibt ein endgültiges Ergebnis:5 zurück.
• ANZAHL2(A2:A13)-SUMMENPRODUKT(ZÄHLENWENN(A2:A13;C2:C4))=12-5 :Das Ergebnis (5) der SUMMENPRODUKT-Funktion wird von der Anzahl aller nicht leeren Zellen (12) subtrahiert, um das benötigte Endergebnis zu erhalten.

Hinweis: Beide oben genannten Formeln sind nicht zwischen Groß- und Kleinschreibung unterscheidend.

Verwandte verwendete Funktion:

Weitere Artikel: