Die Funktion COUNTIFS in Excel wird verwendet, um die Anzahl der Zellen in einem oder mehreren Bereichen zu ermitteln, die eine Reihe von angegebenen Bedingungen oder Kriterien erfüllen.
Es kann vorkommen, dass die Funktion COUNTIFS nicht wie erwartet funktioniert und einen Fehler #VALUE! , 0, amessage anzeigt, dass es einen Fehler in der Formel oder einen anderen unerwarteten Wert gibt.
Hier sind einige der Gründe, warum die Funktion in Microsoft Excel nicht wie erwartet funktioniert :
- Wenn eine Formel auf einen Bereich in einer Arbeitsmappe verweist, die geschlossen ist.
- Einen Wert aus einer anderen Zelle verwenden, ohne ihn mit dem verwendeten logischen Operator zu verknüpfen.
- Textkriterien nicht in Anführungszeichen ( “ „) einfügen.
- Nachfolgende Bereiche haben nicht die gleiche Anzahl an Spalten und Zeilen wie der erste Kriterienbereich.
- Numerische Kriterien und der logische Operator werden nicht in Anführungszeichen ( “ „) eingefügt.
- Versuchen Sie, eine Zahl mithilfe der OR-Logik zu ermitteln.
- Nachträgliche Änderungen im Datensatz.
- Fehler in der Formel.
In diesem Tutorial erklären wir, wie diese Gründe behoben werden können, damit die Funktion die richtige Zahl zurückgibt, nach der wir suchen.
1. Die Formel bezieht sich auf einen Bereich, der sich in einer anderen Arbeitsmappe befindet, die geschlossen ist.
Wenn die Funktion COUNTIFS auf einen Bereich verweist, der sich in einer anderen geschlossenen Arbeitsmappe befindet, gibt sie statt der erwarteten Anzahl von Zellen den Fehler #WERT! zurück.
Im folgenden Beispiel wurde, anstatt dass die Formel die Anzahl der roten Produkte zurückgibt, ein #WERT! -Fehler erzeugt, weil die Formel auf einen Bereich in einer anderen geschlossenen Arbeitsmappe verweist.her workbook called Product Colours that is closed.
Lösung
Die Lösung dafür, dass die Konten in diesem Szenario nicht funktionieren, besteht darin, die geschlossene Arbeitsmappe zu öffnen und die Taste F9 auf der Tastatur zu drücken, damit die Formel die korrekten Parameter neu berechnet. Wir erhalten dann das korrekte Konto, wie das folgende Beispiel zeigt.
2. Verwendung des Werts einer anderen Zelle ohne Verkettung des Zellbezugs mit dem logischen Operator.
Wenn Sie einen logischen Operator und einen Zellbezug als Kriterien für die Funktion COUNTIFS in einer Formel verwenden und den Zellbezug nicht mit dem logischen Operator verknüpfen, indem Sie & vor den Zellbezug einfügen, gibt die Funktion eine 0 statt der erwarteten Zahl zurück.
Dies wird im folgenden Beispiel veranschaulicht, in dem die Formel =COUNTIFS(G2:G6,“>M2″) verwendet wurde.
Lösung
Im folgenden Beispiel sollen wir die korrekte Formel =COUNTIFS(G2:G6,“ > “ &M2) in die Zelle I3 eingeben. Wenn wir die ENTER -Taste drücken, erhalten wir die korrekte Zahl 4.
In der korrekten Formel steht nur der logische Operator, der größer als (>) ist, in Anführungszeichen ( “ „) und der Verkettungsoperator & steht vor dem Zellverweis auf M2.
3. Setzen Sie Textkriterien nicht in Anführungszeichen ( “ „).
Wenn die Textkriterien nicht in Anführungszeichen eingefügt werden, gibt die Funktion COUNTIFS statt der erwarteten korrekten Zahl den Wert 0 zurück, wie das folgende Beispiel zeigt.
Die Formel für Zelle J2 lautet =COUNTIFS(C2:C6,Rot). Die Formel gibt 0 zurück , weil das Kriterium Rot nicht in Anführungszeichen ( “ „) gesetzt wurde.
Lösung für das Problem der nicht funktionierenden Konten.
Wir müssen die Textkriterien in der Formel in Anführungszeichen setzen, wie im folgenden Beispiel beschrieben, dann erhalten wir die korrekte Zahl 2.
4. Der nächste Bereich von Kriterien hat nicht dieselbe Form wie der erste Bereich von Kriterien.
Wenn wir mehrere Kriterien in der Funktion KÜRZE verwenden und die zusätzlichen optionalen Kriterienbereiche nicht mit dem ersten Kriterienbereich übereinstimmen, wird der Fehler #WERT generiert.
Im folgenden Beispiel wurde die Formel =COUNTIFS(B2:B13, „Rot“,C2:C10, „Microsoft“) in die Zelle G3 eingegeben, um zu versuchen, die Anzahl der von Microsoft gelieferten roten Produkte zu ermitteln.
Wenn wir die Eingabetaste drücken, erhalten wir den Fehler #VALUE! wie unten gezeigt.
Das liegt daran, dass der zweite Kriterienbereich C2:C10 nicht die gleiche Anzahl an Zellen hat wie der erste Kriterienbereich B2:B13.
Lösung
Um dieses Problem zu beheben, müssen wir sicherstellen, dass der zweite Kriterienbereich die gleiche Größe hat wie der erste Kriterienbereich, wie unten gezeigt :
Wenn wir die ENTER-Taste drücken, erhalten wir die korrekte Anzahl von 2, wie unten gezeigt:
5. Fügen Sie numerische Kriterien und den logischen Operator nicht in Anführungszeichen ( “ „) ein.
Wenn wir logische Operatoren wie gleich (=), größer als (>), kleiner als (<) und ungleich (<>) in einer Formel verwenden und es uns nicht gelingt, die numerischen Kriterien und den logischen Operator zwischen denselben Anführungszeichen einzufügen, gibt Excel eine Meldung aus, dass die Formel einen Fehler enthält.
Im folgenden Beispiel haben wir den logischen Operator und das numerische Kriterium nicht in Anführungszeichen gesetzt. Nur der logische Operator befindet sich in Anführungszeichen.
Wenn wir die ENTER-Taste drücken, erhalten wir die folgende Meldung:
Lösung
Wir müssen sicherstellen, dass wir sowohl den logischen Operator als auch das numerische Kriterium innerhalb derselben Anführungszeichen einschließen, wie unten gezeigt:
Wenn wir die ENTER-Taste drücken, erhalten wir die korrekte Anzahl von 5, wie unten gezeigt:
6. Versuchen Sie, eine Zählung mithilfe der ODER-Logik zu erreichen.
Obwohl die Funktion COUNTIFS mithilfe der AND-Logik korrekte Zählungen erzeugen kann, erzeugt sie nur den Wert 0, wenn wir versuchen, mit ihr mithilfe der OR-Logik zu rechnen.
Im folgenden Beispiel versuchen wir, die Anzahl der roten und blauen Produktkategorien mithilfe der ODER-Logik zu ermitteln.
Wenn wir die ENTER-Taste drücken, erhalten wir den unerwarteten Wert 0, wie unten gezeigt. Dies liegt daran, dass die Funktion COUNTIFS nicht mithilfe der OR-Logik rechnen kann.
Lösung
Wir können dieses Problem mit der ODER-Logik umgehen, indem wir die Funktionen COUNTIFS und SUM zusammen verwenden.
Im folgenden Beispiel haben wir die Formel =SUM(COUNTIFS(B2:B13,{„Rot“, „Blau“})) in die Zelle G3 eingegeben.
Wenn wir die ENTER-Taste drücken, erhalten wir den korrekten Wert von 6, wie unten gezeigt. Das liegt daran, dass die Funktion COUNTIFS 3 Zahlen für Rot und 3 Zahlen für Blau aus dem Kriterienbereich B2:B13 zurückgegeben hat und die Funktion SUM die Werte addiert hat, um 6 zu erhalten.
7. Spätere Änderungen am Datensatz
Änderungen an einem Datensatz können dazu führen, dass die Funktion COUNTIFS nicht die erwarteten Werte erzeugt. Im folgenden Datensatz funktioniert die Funktion COUNTIF beispielsweise korrekt und gibt die genaue Anzahl der blauen Produkte zurück.
Wenn es aber Änderungen in den Daten gibt, z. B. fügen wir in diesem Fall das Wort Farbe zu Blau hinzu, gibt die Funktion COUNTIFS nun den falschen Wert 0 aus.
Lösung
Dies lässt sich beheben, indem Sie mit der Funktion COUNTIF Platzhalterzeichen verwenden.
Im folgenden Beispiel haben wir das Sternchenzeichen (*) in den Kriterien verwendet, wodurch wir die korrekte Anzahl von 4 Produkten erhalten. Tatsächlich kann die Funktion COUNTIF nun innerhalb des Kriterienbereichs nach teilweisen Übereinstimmungen suchen.
8. Fehler in der Formel
Es kommt vor, dass die Funktion COUNTIF aufgrund eines Fehlers in der Formel nicht funktioniert, z. B. weil ein Trennkomma zwischen dem Kriterienbereich und dem logischen Operator vergessen wurde.
Im folgenden Beispiel gibt es kein Komma zwischen dem Kriterienbereich D2:D13 und dem logischen Operator, der größer als (>) ist.
Wenn wir die ENTER-Taste drücken, erhalten wir ein Meldungsfeld, das uns mitteilt, dass es einen Fehler in der Formel gibt.
Lösung
Beim Schreiben von Formeln sollte man besonders aufmerksam sein, um sicherzustellen, dass die Syntax korrekt ist. Im folgenden Beispiel löst das Einfügen eines Kommas zwischen dem Kriterienbereich und dem Operator größer als (>) das Problem und wir erhalten die korrekte Zahl 5.
Schlussfolgerung
COUNTIFS ist eine sehr mächtige Funktion, mit der Sie die Anzahl der Zellen in Datenbereichen erzeugen können, die bestimmte Bedingungen erfüllen.
In diesem Tutorial haben wir uns die verschiedenen Gründe angesehen, warum diese Funktion nicht immer wie erwartet funktioniert, und wie man sie beheben kann.