Generische Formel
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Zusammenfassung
Um Zellen zu zählen, die vielen Dingen nicht entsprechen, können Sie eine Formel verwenden, die auf den Funktionen MATCH, ISNA und SUMPRODUCT basiert. In dem gezeigten Beispiel lautet die Formel in Zelle F5:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Dabei ist "Daten" der benannte Bereich B5: B16 und "Ausschließen" der benannte Bereich D5: D7.
Erläuterung
Zunächst ein kleiner Kontext. Wenn Sie nur ein paar Dinge haben, die Sie nicht zählen möchten, können Sie normalerweise COUNTIFS wie folgt verwenden:
=COUNTIFS(range,"apple",range,"orange")
Dies lässt sich jedoch nicht sehr gut skalieren, wenn Sie eine Liste mit vielen Dingen haben, da Sie für jede Sache, die Sie nicht zählen möchten, ein zusätzliches Bereich / Kriterien-Paar hinzufügen müssen. Es wäre viel einfacher, eine Liste zu erstellen und einen Verweis auf diese Liste als Teil der Kriterien zu übergeben. Genau das macht die Formel auf dieser Seite.
Im Kern verwendet diese Formel die MATCH-Funktion, um Zellen zu finden, die nicht gleich "a", "b" oder "c" mit diesem Ausdruck sind:
MATCH(data,exclude,0)
Beachten Sie, dass der Suchwert und das Sucharray von der normalen Konfiguration "umgekehrt" sind. Wir stellen alle Werte aus dem benannten Bereich "data" als Suchwerte bereit und geben alle Werte, die wir ausschließen möchten, in den benannten Bereich "exclude" ein. Da wir MATCH mehr als einen Suchwert geben, erhalten wir in einem Array wie diesem mehr als ein Ergebnis:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Im Wesentlichen gibt MATCH die Position übereinstimmender Werte als Zahl an und gibt für alle anderen Werte # N / A zurück.
Die # N / A-Ergebnisse sind diejenigen, an denen wir interessiert sind, da sie Werte darstellen, die nicht gleich "a", "b" oder "c" sind. Dementsprechend verwenden wir ISNA, um diese Werte auf TRUE und die Zahlen auf FALSE zu setzen:
ISNA(MATCH(data,exclude,0)
Dann verwenden wir ein doppeltes Negativ, um TRUE auf 1 und FALSE auf Null zu zwingen. Das resultierende Array in SUMPRODUCT sieht folgendermaßen aus:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Mit nur einem zu verarbeitenden Array summiert SUMPRODUCT und gibt ein Endergebnis zurück, 4.
Hinweis: Wenn Sie SUMPRODUCT anstelle von SUM verwenden, müssen Sie nicht mehr Strg + Umschalt + Eingabetaste verwenden.
Anzahl minus Übereinstimmung
Eine andere Möglichkeit, Zellen zu zählen, die keinem von mehreren Dingen entsprechen, besteht darin, alle Werte zu zählen und Übereinstimmungen zu subtrahieren. Sie können dies mit einer Formel wie der folgenden tun:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Hier gibt COUNTA eine Anzahl aller nicht leeren Zellen zurück. Die COUNTIF-Funktion gibt bei dem benannten Bereich "Ausschließen" drei Zählungen zurück, eine für jedes Element in der Liste. SUMPRODUCT addiert die Summe und diese Zahl wird von der Anzahl aller nicht leeren Zellen abgezogen. Das Endergebnis ist die Anzahl der Zellen, die nicht den Werten in "ausschließen" entsprechen.
Literal enthält Typlogik
Die Formel auf dieser Seite zählt mit der Logik "gleich". Wenn Sie Zellen zählen müssen, die nicht viele Zeichenfolgen enthalten, wobei enthält bedeutet, dass eine Zeichenfolge an einer beliebigen Stelle in einer Zelle angezeigt werden kann, benötigen Sie eine komplexere Formel.
