Generische Formel
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Zusammenfassung
Um die Werte in einer Liste zu zählen, die in einer anderen Liste fehlen, können Sie eine Formel verwenden, die auf den Funktionen COUNTIF und SUMPRODUCT basiert.
In dem gezeigten Beispiel lautet die Formel in H6:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Was 1 zurückgibt, da der Wert "Osborne" in B6: B11 nicht erscheint.
Erläuterung
Die COUNTIF-Funktionen prüfen Werte in einem Bereich anhand von Kriterien. Oft wird nur ein Kriterium angegeben, in diesem Fall liefern wir jedoch mehr als ein Kriterium.
Für Bereich geben wir COUNTIF der benannte Bereich list1 (B6: B11), und nach Kriterien, liefern wir den benannten Bereich list2 (F6: F8).
Da wir COUNTIF mehr als ein Kriterium geben, erhalten wir mehr als ein Ergebnis in einem Ergebnisarray, das folgendermaßen aussieht: (2; 1; 0)
Wir wollen nur fehlende Werte zählen, die per Definition eine Zählung von Null haben, also konvertieren wir diese Werte mit der Anweisung "= 0" in TRUE und FALSE, was ergibt: (FALSE; FALSE; TRUE)
Dann erzwingen wir die TRUE FALSE-Werte mit dem doppelt negativen Operator (-) auf 1s und 0s, was Folgendes ergibt: (0; 0; 1)
Schließlich verwenden wir SUMPRODUCT, um die Elemente im Array zu addieren und eine Gesamtzahl der fehlenden Werte zurückzugeben.
Alternative mit MATCH
Wenn Sie mehr wörtliche Formeln bevorzugen, können Sie die folgende Formel verwenden, die auf MATCH basiert und mithilfe der ISNA-Funktion buchstäblich Werte zählt, die "fehlen":
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
