So verwenden Sie mehrere Bedingungen in Excel COUNTIF und COUNTIFS

Excel hat viele Funktionen, bei denen der Benutzer eine oder mehrere Bedingungen angeben muss, um das Ergebnis zu erhalten.Wenn Sie beispielsweise Zellen basierend auf mehreren Kriterien zählen möchten, können Sie die Funktionen ZÄHLENWENN oder ZÄHLENWENNS in Excel verwenden.

In diesem Lernprogramm werden verschiedene Möglichkeiten zur Verwendung einzelner oder mehrerer Kriterien in den Funktionen ZÄHLENWENN und ZÄHLENWENNS von Excel behandelt.

Während ich mich in diesem Lernprogramm hauptsächlich auf die Funktionen ZÄHLENWENN und ZÄHLENWENNS konzentriere, können alle diese Beispiele auch mit anderen Excel-Funktionen verwendet werden, die mehrere Kriterien als Eingabe verwenden (z. B. SUMMEWENN, SUMIFS, AVERAGEIF und AVERAGEIFS).

Einführung in die Excel-Funktionen ZÄHLENWENN und ZÄHLENWENNS

Lassen Sie uns zunächst verstehen, wie die Funktionen ZÄHLENWENN und ZÄHLENWENNS in Excel verwendet werden.

Excel COUNTIF-Funktion (mit einem einzigen Kriterium)

Die Excel-Funktion ZÄHLENWENN funktioniert am besten, wenn Sie Zellen basierend auf einem einzigen Kriterium zählen möchten.Wenn Sie anhand mehrerer Kriterien zählen möchten, verwenden Sie die Funktion ZÄHLENWENNS.

Syntax

=ZÄHLENWENN(Bereich, Bedingung)

Eingabeparameter

• Reichweite -  Der Zellbereich, den Sie berechnen möchten.
• Zustand -Kriterien, die anhand des Bereichs der zu zählenden Zellen bewertet werden müssen.

Excel COUNTIFS-Funktion (mit mehreren Kriterien)

Die Excel-Funktion ZÄHLENWENNS funktioniert am besten, wenn Sie Zellen basierend auf mehreren Kriterien zählen möchten.

Syntax

=COUNTIFS(Kriterien_Bereich1,Kriterien1,[Kriterien_Bereich2,Kriterien2]…)

Eingabeparameter

• Kriterien_Bereich1 – Der Zellbereich, den Sie anhand von Kriterium 1 auswerten möchten.
• Kriterien1 – Das auszuwertende Kriterium ist kriterium_bereich1, um zu bestimmen, welche Zellen auszuwerten sind.
• [Kriterienbereich2] – Der Zellbereich, den Sie anhand von Kriterium 2 auswerten möchten.
• [Kriterien2] – Das Kriterium, das Sie auswerten möchten, ist „criteria_range2“, um zu bestimmen, welche Zellen gezählt werden sollen.

Sehen wir uns nun einige Beispiele für die Verwendung mehrerer Kriterien in der ZÄHLENWENN-Funktion von Excel an.

Verwenden Sie die NUMBER-Bedingung in der Excel-Funktion ZÄHLENWENN

#1 Zellen zählen, wenn das Kriterium einem Wert entspricht

Um die Anzahl der Zellen zu erhalten, in denen der Bedingungsparameter gleich dem angegebenen Wert ist, können Sie die Bedingung direkt eingeben oder einen Zellbezug verwenden, der die Bedingung enthält.

Unten sehen Sie ein Beispiel, in dem wir Zellen zählen, die die Zahl 9 enthalten (was bedeutet, dass der Bedingungsparameter gleich 9 ist).Hier ist die Formel:

=ZÄHLENWENN($B$2:$B$11,D3)

Im obigen Beispiel (im Bild) befindet sich das Kriterium in Zelle D3.Sie können Bedingungen auch direkt in Formeln eingeben.Sie können zum Beispiel auch Folgendes verwenden:

=ZÄHLENWENN($B$2:$B$11,9)

#2 Zellen zählen, wenn die Bedingung größer als der Wert ist

Um die Anzahl der Zellen mit einem Wert zu erhalten, der größer als ein bestimmter Wert ist, verwenden wir den Größer-als-Operator (">").Wir können es direkt in Formeln verwenden oder wir können Zellbezüge mit Bedingungen verwenden.

Wann immer wir einen Operator in einer Bedingung in Excel verwenden, müssen wir ihn in doppelte Anführungszeichen setzen.Wenn die Bedingung beispielsweise größer als 10 ist, müssen wir als Bedingung „>10“ eingeben (siehe Abbildung unten):

Hier ist die Formel:

=ZÄHLENWENN($B$2:$B$11”,>10″)

Sie können auch Bedingungen in Zellen einfügen und Zellbezüge als Bedingungen verwenden.In diesem Fall müssen Sie die Bedingung nicht in doppelte Anführungszeichen setzen:

=ZÄHLENWENN($B$2:$B$11,D3)

Dies kann auch der Fall sein, wenn Sie eine Bedingung in einer Zelle haben möchten, diese aber nicht mit einem Operator verwenden möchten.Sie möchten beispielsweise, dass die Zahl in Zelle D3 10 statt >10 ist.

In diesem Fall müssen Sie einen bedingten Parameter erstellen, der eine Kombination aus Operator und Zellbezug ist (siehe Abbildung unten):

=ZÄHLENWENN($B$2:$B$11,”>”&D3)

Hinweis: Wenn Sie Operatoren und Zellbezüge kombinieren, werden die Operatoren immer in doppelte Anführungszeichen eingeschlossen.Operatoren und Zellreferenzen werden durch ein kaufmännisches Und (&) verbunden.

#3 Zellen zählen, wenn das Kriterium kleiner als ein Wert ist

Um die Anzahl der Zellen mit einem Wert zu erhalten, der kleiner als ein angegebener Wert ist, verwenden wir den Kleiner-als-Operator ("<").Wir können es direkt in Formeln verwenden oder wir können Zellbezüge mit Bedingungen verwenden.

Wann immer wir einen Operator in einer Bedingung in Excel verwenden, müssen wir ihn in doppelte Anführungszeichen setzen.Wenn das Kriterium beispielsweise ist, dass die Menge kleiner als 5 sein soll, müssen wir als Kriterium "<5" eingeben (siehe Bild unten):

=ZÄHLENWENN($B$2:$B$11,”<5”)

Sie können auch Bedingungen in Zellen einfügen und Zellbezüge als Bedingungen verwenden.In diesem Fall müssen Sie den Standard nicht in doppelte Anführungszeichen setzen (siehe Abbildung unten):

=ZÄHLENWENN($B$2:$B$11,D3)

Außerdem möchten Sie möglicherweise, dass sich die Bedingung in der Zelle befindet, aber nicht beim Operator.Beispielsweise möchten Sie möglicherweise, dass Zelle D3 die Nummer 5 anstelle von <5 enthält.

In diesem Fall müssen Sie einen bedingten Parameter erstellen, der eine Kombination aus Operator und Zellbezug ist:

=ZÄHLENWENN($B$2:$B$11,"<"&D3)

Hinweis: Wenn Sie Operatoren und Zellbezüge kombinieren, werden die Operatoren immer in doppelte Anführungszeichen eingeschlossen.Operatoren und Zellreferenzen werden durch ein kaufmännisches Und (&) verbunden.

#4 Zähle Zellen mit mehreren Kriterien – zwischen zwei Werten

Um die Anzahl der Werte zwischen zwei Werten zu erhalten, müssen wir mehrere Bedingungen in der COUNTIF-Funktion verwenden.

Hier sind zwei Möglichkeiten, dies zu tun:

Methode XNUMX: Verwenden Sie die COUNTIFS-Funktion

Die COUNTIFS-Funktion kann mehrere Bedingungen als Argumente behandeln und Zellen nur zählen, wenn alle Bedingungen TRUE sind.Um Zellen mit Werten zwischen zwei angegebenen Werten zu zählen, z. B. 5 und 10, können wir die folgende COUNTIFS-Funktion verwenden:

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)

Hinweis: Die obige Formel zählt keine Zellen, die 5 oder 10 enthalten.Wenn Sie diese Zellen einschließen möchten, verwenden Sie die Operatoren „Größer als oder gleich“ (>=) und „Kleiner als oder gleich“ (<=).Hier ist die Formel:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

Sie können diese Bedingungen auch in Zellen einschließen und Zellbezüge als Bedingungen verwenden.In diesem Fall müssen Sie den Standard nicht in doppelte Anführungszeichen setzen (siehe Abbildung unten):

Sie können auch eine Kombination aus Zellbezügen und Operatoren verwenden (Operatoren direkt in Formeln eingeben).Beim Kombinieren von Operatoren und Zellbezügen werden die Operatoren immer in doppelte Anführungszeichen eingeschlossen.Operatoren und Zellreferenzen werden durch ein kaufmännisches Und (&) verbunden.

Methode 2: Verwenden Sie zwei COUNTIF-Funktionen

Wenn Sie mehrere Bedingungen haben, können Sie COUNTIFS verwenden oder eine Kombination von COUNTIF-Funktionen erstellen.Die folgende Formel wird dasselbe tun:

=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)

In der obigen Formel finden wir zuerst die Anzahl der Zellen mit einem Wert größer als 5 und subtrahieren dann die Anzahl der Zellen mit einem Wert größer als 10.Dadurch erhalten wir ein Ergebnis von 5 (dh mit Werten größer als 5 und kleiner oder gleich 10).

Wenn Sie möchten, dass die Formel sowohl 5 als auch 10 enthält, verwenden Sie stattdessen die folgende Formel:

=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)

Wenn Sie möchten, dass die Formel "5" und "10" aus der Zählung ausschließt, verwenden Sie die folgende Formel:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

Sie können diese Bedingungen in Zellen einfügen und Zellbezüge verwenden, oder Sie können eine Kombination aus Operatoren und Zellbezügen verwenden.

Verwenden Sie TEXT-Bedingungen in Excel-Funktionen

#1 Zellen zählen, wenn die Bedingung gleich dem angegebenen Text ist

Um Zellen zu zählen, die eine genaue Übereinstimmung mit einem bestimmten Text enthalten, können wir diesen Text einfach als Bedingung verwenden.Wenn ich beispielsweise in einem Datensatz (siehe unten) alle Zellen mit dem Namen Joe zählen möchte, könnte ich die folgende Formel verwenden:

=ZÄHLENWENN($B$2:$B$11,"Joe")

Da dies eine Textzeichenfolge ist, muss ich die Textbedingung in doppelte Anführungszeichen setzen.

Sie können auch eine Bedingung in eine Zelle einfügen und dann diese Zellreferenz verwenden (wie unten gezeigt):

=ZÄHLENWENN($B$2:$B$11,E3)

Hinweis: Sie erhalten möglicherweise falsche Ergebnisse, wenn führende/nachgestellte Leerzeichen in der Bedingung oder im Bedingungsbereich vorhanden sind.Stellen Sie sicher, dass Sie die Daten bereinigen, bevor Sie diese Formeln verwenden.

#2 Zellen zählen, wenn die Bedingung nicht dem angegebenen Text entspricht

Ähnlich wie im obigen Beispiel können Sie auch Zellen zählen, die keinen bestimmten Text enthalten.Dazu müssen wir den Ungleich-Operator (<>) verwenden.

Angenommen, Sie möchten alle Zellen zählen, die nicht den Namen JOE enthalten, hier ist eine Formel, die dies tun kann:

=ZÄHLENWENN($B$2:$B$11,“<>Joe“)

Sie können auch Bedingungen in Zellen einfügen und Zellbezüge als Bedingungen verwenden.In diesem Fall müssen Sie den Standard nicht in doppelte Anführungszeichen setzen (siehe Abbildung unten):

=ZÄHLENWENN($B$2:$B$11,E3)

Dies kann auch der Fall sein, wenn Sie eine Bedingung in einer Zelle haben möchten, diese aber nicht mit einem Operator verwenden möchten.Sie möchten beispielsweise, dass Zelle D3 den Namen Joe anstelle von <>Joe hat.

In diesem Fall müssen Sie einen bedingten Parameter erstellen, der eine Kombination aus Operator und Zellbezug ist (siehe Abbildung unten):

=ZÄHLENWENN($B$2:$B$11,”<>”&E3)

Beim Kombinieren von Operatoren und Zellbezügen werden die Operatoren immer in doppelte Anführungszeichen eingeschlossen.Operatoren und Zellreferenzen werden durch ein kaufmännisches Und (&) verbunden.

Verwenden von DATE-Bedingungen in Excel COUNTIF- und COUNTIFS-Funktionen

Excel speichert Datum und Uhrzeit als Zahlen.Wir können es also wie eine Zahl verwenden.

#1 Zellen zählen, wenn die Bedingung gleich dem angegebenen Datum ist

Um die Anzahl der Zellen zu erhalten, die ein bestimmtes Datum enthalten, verwenden wir den Gleichheitsoperator (=) zusammen mit dem Datum.

Um mit Datumsangaben zu arbeiten, empfehle ich die Verwendung der DATE-Funktion, da sie jede Möglichkeit ausschließt, dass der Datumswert falsch ist.Wenn ich beispielsweise das Datum 2015. September 9 verwenden möchte, könnte ich die DATE-Funktion wie folgt verwenden:

= DATUM (2015,9,1)

Trotz der regionalen Unterschiede gibt diese Formel dasselbe Datum zurück.Beispielsweise wäre 01-09-2015 der 2015. September 9 in der US-Datumssyntax und der 1. Januar 2015 in der britischen Datumssyntax.Diese Formel gibt jedoch immer den 2105. September 9 zurück.

Hier ist die Formel, um die Anzahl der Zellen zu zählen, die das Datum 02 enthalten:

=ZÄHLENWENN($A$2:$A$11,DATUM(2015,9,2))

#2 Zellen zählen, wenn die Bedingung vor oder nach dem angegebenen Datum liegt

Um Zellen zu zählen, die Daten vor oder nach einem bestimmten Datum enthalten, können wir die Operatoren kleiner als/größer als verwenden.

Wenn ich beispielsweise alle Zellen zählen möchte, die Daten nach dem 2015. September 9 enthalten, könnte ich die folgende Formel verwenden:

=ZÄHLENWENN($A$2:$A$11,”>”&DATUM(2015,9,2))

Ebenso können Sie auch die Anzahl der Zellen vor einem bestimmten Datum zählen.Wenn Sie Datumsangaben in die Zählung einbeziehen möchten, verwenden Sie den „Gleich“-Operator zusammen mit den „Größer als/Kleiner als“-Operatoren.

Sie können auch Zellbezüge verwenden, die Datumsangaben enthalten.In diesem Fall müssen Sie ein kaufmännisches Und (&) verwenden, um den Operator (in doppelten Anführungszeichen) mit dem Datum zu kombinieren.

Siehe das Beispiel unten:

=ZÄHLENWENN($A$2:$A$11,”>”&F3)

#3 Zähle Zellen mit mehreren Kriterien – zwischen zwei Daten

Um die Anzahl der Werte zwischen zwei Werten zu erhalten, müssen wir mehrere Bedingungen in der COUNTIF-Funktion verwenden.

Wir können dies auf zwei Arten tun - eine einzelne COUNTIFS-Funktion oder zwei COUNTIF-Funktionen.

Methode XNUMX: Verwenden Sie die COUNTIFS-Funktion

Die COUNTIFS-Funktion kann mehrere Bedingungen als Argumente annehmen und Zellen nur zählen, wenn alle Bedingungen TRUE sind.Um Zellen mit Werten zwischen zwei angegebenen Daten zu zählen, z. B. dem 9. September und dem 2. September, können wir die folgende COUNTIFS-Funktion verwenden:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

Die obige Formel zählt nicht die Zellen, die das angegebene Datum enthalten.Wenn Sie diese Datumsangaben ebenfalls einbeziehen möchten, verwenden Sie die Operatoren „Größer als oder gleich“ (>=) und „Kleiner als oder gleich“ (<=).Hier ist die Formel:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

Sie können auch Datumsangaben in Zellen einfügen und Zellbezüge als Kriterien verwenden.In diesem Fall kann der Operator das Datum nicht in die Zelle einfügen.Sie müssen der Formel manuell Operatoren hinzufügen (in doppelte Anführungszeichen eingeschlossen) und ein kaufmännisches Und (&) verwenden, um Zellreferenzen hinzuzufügen.Siehe unten:

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)

Methode 2: Verwenden Sie die ZÄHLENWENN-Funktion

Wenn Sie mehrere Bedingungen haben, können Sie eine COUNTIFS-Funktion verwenden oder eine Kombination aus zwei COUNTIF-Funktionen erstellen.Die folgende Formel wird auch den Zweck erfüllen:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

In der obigen Formel finden wir zuerst die Anzahl der Zellen, deren Datum nach dem 9. September liegt, und subtrahieren dann die Anzahl der Zellen, deren Datum nach dem 2. September liegt.Dies ergibt ein Ergebnis von 9 (d. h. die Anzahl der Zellen mit Daten nach dem 7. September und am oder vor dem 7. September).

Wenn Sie nicht möchten, dass die Formel sowohl den 9. als auch den 2. September berechnet, verwenden Sie stattdessen die folgende Formel:

=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Wenn Sie zwei Daten von der Zählung ausschließen möchten, verwenden Sie die folgende Formel:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))

Darüber hinaus können Sie Standarddaten in Zellen festlegen und Zellbezüge (und Operatoren in doppelten Anführungszeichen mit kaufmännischen Und-Zeichen) verwenden.

Verwenden von Platzhaltern in den Bedingungen der Funktionen COUNTIF und COUNTIFS

In Excel gibt es drei Platzhalter:

1. *(Sternchen) - es steht für eine beliebige Anzahl von Zeichen.Beispielsweise kann ex* Excel, Excels, Beispiel, Experte usw. bedeuten.
2. ? (Fragezeichen) - Es repräsentiert ein einzelnes Zeichen.Zum Beispiel könnte Trömp Trump oder Tramp bedeuten.
3. ~ (Tilde) - Wird verwendet, um Platzhalterzeichen (~, *, ?) im Text zu identifizieren.

Sie können die COUNTIF-Funktion mit Platzhaltern verwenden, um Zellen zu zählen, wenn andere integrierte Zählfunktionen fehlschlagen.Angenommen, Sie haben ein Dataset, das so aussieht:

Nehmen wir nun ein paar Beispiele:

#1 Zähle Zellen, die Text enthalten

Um Zellen mit Text zu zählen, können wir den Platzhalter * (Sternchen) verwenden.Da Sternchen eine beliebige Anzahl von Zeichen darstellen, werden alle Zellen gezählt, die Text enthalten.Hier ist die Formel:

=ZÄHLENWENNS($C$2:$C$11,"*")

Hinweis: Die obige Formel ignoriert Zellen, die Zahlen, leere Zellen und logische Werte enthalten, zählt jedoch Zellen, die Apostrophe enthalten (und daher leer erscheinen) oder die möglicherweise als Teil der Formel enthalten sind.

Hier ist ein detailliertes Tutorial zum Umgang mit Fällen mit leeren Zeichenfolgen oder Apostrophen.

#2 Zähle nicht leere Zellen

Wenn Sie erwägen, die COUNTA-Funktion zu verwenden, denken Sie noch einmal darüber nach.

Probieren Sie es aus, es könnte Sie enttäuschen.COUNTA zählt auch Zellen, die leere Zeichenfolgen enthalten (normalerweise von Formeln als ="" zurückgegeben oder wenn Leute einfach Apostrophe in Zellen eingeben).Zellen, die leere Zeichenfolgen enthalten, scheinen leer zu sein, sind es aber nicht und werden daher von der COUNTA-Funktion gezählt.

COUNTA zählt auch Zellen, die leere Zeichenfolgen enthalten (normalerweise von Formeln als ="" zurückgegeben oder wenn Leute einfach Apostrophe in Zellen eingeben).Zellen, die leere Zeichenfolgen enthalten, scheinen leer zu sein, sind es aber nicht und werden daher von der COUNTA-Funktion gezählt.

Wenn Sie also die Formel = verwenden Leutnant (A1:A11), es gibt 11 zurück, obwohl es 10 zurückgeben sollte.

Hier ist die Lösung:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

Lassen Sie uns diese Formel verstehen, indem wir sie aufschlüsseln:

#3 Zähle Zellen, die bestimmten Text enthalten

Angenommen, wir möchten alle Zellen zählen, in denen der Name des Vertriebsmitarbeiters mit J beginnt.Dies kann einfach durch die Verwendung von Platzhaltern in der COUNTIF-Funktion erreicht werden.Hier ist die Formel:

=ZÄHLENWENNS($C$2:$C$11,"J*") 

Die Bedingung J* gibt an, dass der Text in der Zelle mit J beginnen soll und beliebig viele Zeichen enthalten kann.

Wenn Sie Zellen zählen möchten, die irgendwo im Text Buchstaben enthalten, umgeben Sie sie mit einem Sternchen.Wenn Sie beispielsweise Zellen zählen möchten, die den Buchstaben „a“ enthalten, verwenden Sie *a* als Bedingung.

Im Vergleich zu meinen anderen Artikeln ist dieser ungewöhnlich lang.Hoffe du magst es.Lassen Sie mich wissen, was Sie denken, indem Sie einen Kommentar hinterlassen.