Fehlende Werte zählen
Angenommen, Sie haben zwei Listen und möchten die Gesamtanzahl der Werte in einer Liste ermitteln, die in der anderen Liste nicht vorkommen. Dazu können Sie eine SUMMENPRODUKT-Formel mit den Funktionen VERGLEICH (MATCH)und ISTNV (ISNA) oder alternativ die ZÄHLENWENN-Funktion verwenden.
Fehlende Werte mit SUMMENPRODUKT, VERGLEICH und ISTNV zählen
Fehlende Werte mit SUMMENPRODUKT und ZÄHLENWENN zählen
Fehlende Werte mit SUMMENPRODUKT, VERGLEICH und ISTNV zählen
Um die Gesamtanzahl der Werte in Liste B zu ermitteln, die in Liste A fehlen, wie oben dargestellt, verwenden Sie zunächst die VERGLEICH-Funktion (MATCH), um ein Array mit den relativen Positionen der Werte aus Liste B innerhalb von Liste A zu erhalten. Fehlt ein Wert in Liste A, gibt die Funktion einen #NV-Fehler (#N/A) zurück. Anschließend identifiziert die ISTNV-Funktion (ISNA) diese #NV-Fehler, und SUMMENPRODUKT (SUMPRODUCT) zählt deren Gesamtanzahl.
Allgemeine Syntax
=SUMPRODUCT(--ISNA(MATCH()))range_to_count,lookup_range,0)))
- range_to_count: Der Bereich, aus dem fehlende Werte gezählt werden sollen – hier Liste B.
- lookup_range: Der Bereich, der mit dem range_to_count verglichen wird. Bezieht sich hier auf Liste A.
- 0: Der match_type 0 zwingt die Funktion VERGLEICH (MATCH), eine exakte Übereinstimmung durchzuführen.
Um die Gesamtanzahl der Werte in Liste B zu zählen, die in Liste A fehlen, kopieren oder geben Sie die folgende Formel in die Zelle H6 ein und drücken Sie Enter, um das Ergebnis zu erhalten:
=SUMMENPRODUKT(--ISTNV(VERGLEICH()))F6:F8,B6:B10,0)))
Erläuterung der Formel
=SUMPRODUCT(--)ISNA()MATCH(F6:F8,B6:B10,0)))
- MATCH(F6:F8,B6:B10,0): Der match_type 0 veranlasst die VERGLEICH-Funktion (MATCH), numerische Werte zurückzugeben, die die relativen Positionen der Werte in den Zellen F6 bis F8 innerhalb des Bereichs B6:B10 angeben. Falls ein Wert aus Liste A nicht vorhanden ist, wird ein #NV-Fehler (#N/A) zurückgegeben. Das Ergebnis liegt daher in einem Array folgender Form vor: {2;3;#NV}.
- ISNA()MATCH(F6:F8,B6:B10,0))=ISNA(){2;3;#NV}): Die Funktion ISNA prüft, ob ein Wert den Fehler „#NV“ (#N/A) enthält. Trifft das zu, gibt sie WAHR (TRUE) zurück – andernfalls FALSCH (FALSE). Das Ergebnis der ISNA-Formel lautet daher:{FALSCH;FALSCH;WAHR}.
- SUMPRODUCT(--)ISNA()MATCH(F6:F8,B6:B10,0))) = SUMPRODUCT(--{FALSCH;FALSCH;WAHR}): Das doppelte Minuszeichen wandelt WAHR-Werte in 1 und FALSCH-Werte in 0 um: {0;0;1}. Anschließend gibt die SUMMENPRODUKT-Funktion (SUMPRODUCT) die Summe zurück: 1.
Fehlende Werte mit SUMMENPRODUKT und ZÄHLENWENN zählen
Um die Gesamtanzahl der Werte in Liste B zu zählen, die in Liste A fehlen, können Sie alternativ die ZÄHLENWENN-Funktion (COUNTIF) nutzen: Prüfen Sie mithilfe der Bedingung „=0“, ob ein Wert in Liste A vorhanden ist – denn wenn ein Wert fehlt, liefert die Funktion genau 0. Anschließend zählt SUMMENPRODUKT (SUMPRODUCT) die Gesamtanzahl dieser fehlenden Werte.
Allgemeine Syntax
=SUMPRODUCT(--(COUNTIF()))lookup_range,range_to_count)=0))
- lookup_range: Der Bereich, der mit dem range_to_count verglichen wird. Bezieht sich hier auf Liste A.
- range_to_count: Der Bereich, aus dem fehlende Werte gezählt werden sollen – hier Liste B.
- 0: Der match_type 0 zwingt die Funktion VERGLEICH (MATCH), eine exakte Übereinstimmung durchzuführen.
Um die Gesamtanzahl der Werte in Liste B zu zählen, die in Liste A fehlen, kopieren oder geben Sie die folgende Formel in die Zelle H6 ein und drücken Sie Enter, um das Ergebnis zu erhalten:
=SUMMENPRODUKT(--(ZÄHLENWENN()))B6:B10,F6:F8)=0))
Erläuterung der Formel
=SUMPRODUCT()--()COUNTIF(B6:B10,F6:F8)=0))
- COUNTIF(B6:B10,F6:F8): Die ZÄHLENWENN-Funktion (COUNTIF) zählt, wie oft die Werte aus dem Bereich F6 bis F8 im Bereich B6:B10 vorkommen. Das Ergebnis wird als Array der folgenden Form ausgegeben: {1;1;0}.
- --()COUNTIF(B6:B10,F6:F8)=0)=--(){1;1;0}=0):Der Ausdruck {1;1;0}=0 liefert ein WAHR/FALSCH-Array {FALSCH;FALSCH;WAHR}. Das doppelte Minuszeichen wandelt anschließend WAHR in 1 und FALSCH in 0 um – das endgültige Array lautet somit:{0;0;1}.
- SUMPRODUCT()--()COUNTIF(B6:B10,F6:F8)=0)) = SUMPRODUCT({0;0;1}): Anschließend gibt SUMMENPRODUKT (SUMPRODUCT) die Summe zurück: 1.
Verwandte Funktionen
Excel-Funktion SUMMENPRODUKT (SUMPRODUCT)
In Excel lässt sich die SUMMENPRODUKT-Funktion (SUMPRODUCT) nutzen, um zwei oder mehr Spalten oder Arrays miteinander zu multiplizieren und anschließend die Summe der Produkte zu berechnen. Tatsächlich ist SUMMENPRODUKT eine äußerst nützliche Funktion, mit der Zellwerte anhand mehrerer Kriterien gezählt oder summiert werden können – ganz ähnlich wie mit den Funktionen ZÄHLENWENNS (COUNTIFS) oder SUMMEWENNS (SUMIFS). Dieser Artikel stellt die Syntax der Funktion sowie einige praktische Beispiele für die Verwendung von SUMMENPRODUKT vor.
Excel-Funktion VERGLEICH (MATCH)
Die Excel-Funktion VERGLEICH sucht in einem Zellbereich nach einem bestimmten Wert und liefert dessen relative Position zurück.
Excel-Funktion ZÄHLENWENN (COUNTIF)
Die ZÄHLENWENN-Funktion (COUNTIF) ist eine statistische Excel-Funktion, mit der Sie die Anzahl der Zellen zählen, die ein bestimmtes Kriterium erfüllen. Sie unterstützt logische Operatoren (>, < usw.) sowie die Platzhalterzeichen „?“ und „*“ für teilweise Übereinstimmungen.
Verwandte Formeln
In bestimmten Fällen müssen Sie zwei Listen vergleichen, um zu prüfen, ob ein Wert aus Liste A in Liste B in Excel enthalten ist. Angenommen, Sie haben eine eigene Produktliste und möchten herausfinden, ob Ihre Produkte auch in der Lieferanten-Produktliste aufgeführt sind. Dafür stellen wir Ihnen im Folgenden drei effiziente Methoden vor – wählen Sie einfach diejenige, die am besten zu Ihnen passt.
Zellen zählen, die gleich sind
Dieser Artikel widmet sich Excel-Formeln zum Zählen von Zellen, die entweder exakt mit einer von Ihnen angegebenen Zeichenfolge übereinstimmen oder diese teilweise enthalten – wie in den folgenden Screenshots veranschaulicht. Zunächst erläutern wir die Formelsyntax und ihre Argumente, gefolgt von praxisnahen Beispielen für ein besseres Verständnis.
Anzahl der Zellen zählen, die nicht zwischen zwei vorgegebenen Zahlen liegen
Das Zählen der Zellen zwischen zwei Zahlen ist eine häufige Aufgabe in Excel. In bestimmten Fällen möchten Sie jedoch die Anzahl der Zellen ermitteln, die **nicht** zwischen zwei vorgegebenen Werten liegen. Angenommen, Sie verfügen über eine Produktliste mit den Umsätzen von Montag bis Sonntag und möchten herausfinden, wie viele Zellen außerhalb bestimmter unterer und oberer Grenzwerte liegen – wie im folgenden Screenshot dargestellt. In diesem Artikel stelle ich Ihnen praktische Formeln vor, mit denen Sie diese Aufgabe in Excel mühelos bewältigen können.
Die besten Office-Produktivitätswerkzeuge
Kutools für Excel – Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen und stellt sicher, dass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab – Aktiviert tabbasiertes Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- Mit einem Klick zwischen Dutzenden geöffneter Dokumente wechseln!
- Reduziert täglich Hunderte Mausklicks für Sie – verabschieden Sie sich von der Maus-Hand.
- Steigert Ihre Produktivität um 50 %, wenn Sie mehrere Dokumente gleichzeitig anzeigen und bearbeiten.
- Bringt effiziente Tabs in Office (inklusive Excel) – genau wie bei Chrome, Edge und Firefox.