Excel verfügt über viele Funktionen, bei denen ein Benutzer ein oder mehrere Kriterien angeben muss, um das Ergebnis zu erhalten. Wenn Sie beispielsweise Zellen anhand mehrerer Kriterien zählen möchten, können Sie die Funktionen COUNTIF oder COUNTIFS in Excel verwenden.
Dieses Tutorial behandelt verschiedene Möglichkeiten zur Verwendung eines einzelnen oder mehrerer Kriterien in COUNTIF und COUNTIFS Funktion in Excel.,
Während ich mich in diesem Tutorial hauptsächlich auf COUNTIF-und COUNTIFS-Funktionen konzentrieren werde, können all diese Beispiele auch in anderen Excel-Funktionen verwendet werden, die mehrere Kriterien als Eingaben verwenden (z. B. SUMIF, SUMIFS, AVERAGEIF und AVERAGEIFS).
Dieses Tutorial behandelt:
Eine Einführung in die Funktionen COUNTIF und COUNTIFS von Excel
Lassen Sie uns zuerst die Funktionen COUNTIF und COUNTIFS in Excel in den Griff bekommen.,
Excel COUNTIF Funktion (nimmt Einzelne Kriterien)
Excel COUNTIF funktion ist am besten geeignet für situationen, wenn sie wollen zu zählen zellen basierend auf einem einzigen kriterium. Wenn Sie anhand mehrerer Kriterien zählen möchten, verwenden Sie die COUNTIFS-Funktion.
Syntax
=COUNTIF (range, criteria)
Eingabeargumente
- range – der Bereich der Zellen, die Sie zählen möchten.
- Kriterien-die Kriterien, die anhand des Zellbereichs für eine zu zählende Zelle ausgewertet werden müssen.,
Excel COUNTIFS-Funktion (nimmt mehrere Kriterien an)
Die Excel COUNTIFS-Funktion eignet sich am besten für Situationen, in denen Sie Zellen basierend auf mehreren Kriterien zählen möchten.
Syntax
=COUNTIFS (criteria_range1, criteria1, …)
Eingabeargumente
- criteria_range1 – Der Zellbereich, für den Sie anhand von criteria1 bewerten möchten.
- criteria1-die Kriterien, die Sie für criteria_range1 auswerten möchten, um zu bestimmen, welche Zellen gezählt werden sollen.
- – Der Bereich der Zellen, für die Sie anhand von Kriterien2 bewerten möchten.,
- – die Kriterien, die Sie für criteria_range2 auswerten möchten, um zu bestimmen, welche Zellen gezählt werden sollen.
Schauen wir uns nun einige Beispiele für die Verwendung mehrerer Kriterien in COUNTIF-Funktionen in Excel an.
Verwenden von Zahlenkriterien in Excel COUNTIF-Funktionen
#1 Zähle Zellen wenn Kriterien GLEICH einem Wert sind
Um die Anzahl der Zellen abzurufen, bei denen das Kriterienargument gleich ist Ein bestimmter Wert, können Sie entweder direkt die Kriterien eingeben oder die Zellreferenz verwenden, die die Kriterien enthält.,
Unten ist ein Beispiel, in dem wir die Zellen zählen, die die Zahl 9 enthalten (was bedeutet, dass das Kriterienargument gleich 9 ist). Hier ist die Formel:
=COUNTIF($B$2:$B$11,D3)
Im obigen Beispiel, den (im Bild), wird das Kriterium in Zelle D3. Sie können die Kriterien auch direkt in die Formel eingeben., Sie können beispielsweise auch Folgendes verwenden:
=COUNTIF ($B$2:$B$11,9)
#2 Zählen Sie Zellen wenn Kriterien GRÖßER ALS ein Wert sind
Um die Anzahl der Zellen mit einem Wert größer als ein bestimmter Wert zu erhalten, verwenden wir den Operator größer als („>“). Wir könnten es entweder direkt in der Formel verwenden oder eine Zellreferenz mit den Kriterien verwenden.
Wenn wir einen Operator in Kriterien in Excel verwenden, müssen wir ihn in doppelte Anführungszeichen setzen., Wenn die Kriterien beispielsweise größer als 10 sind, müssen wir als Kriterien „>10“ eingeben (siehe Abbildung unten):
Hier ist die Formel:
=COUNTIF($B$2:$B$11,“>10″)
Sie können auch die Kriterien in einer Zelle haben und die Zellreferenz als Kriterien verwenden. In diesem Fall müssen Sie die Kriterien NICHT in doppelte Anführungszeichen setzen:
=COUNTIF ($B$2:$B$11,D3)
Es kann auch vorkommen, dass sich die Kriterien in einer Zelle befinden, diese jedoch nicht mit dem Operator möchten., Beispielsweise soll die Zelle D3 die Nummer 10 haben und nicht >10.
In diesem Fall müssen Sie ein Kriterienargument erstellen, das eine Kombination aus Operator und Zellreferenz ist (siehe Abbildung unten):
=COUNTIF($B$2:$B$11,“>“&D3)
HINWEIS: Wenn Sie einen Operator und eine Zelle kombinieren, referenz, der Operator ist immer in doppelten Anführungszeichen. Der Operator und die Zellreferenz werden durch ein kaufmännisches und verbunden (&).,
#3 Zellen zählen wenn Kriterien KLEINER ALS ein Wert sind
Um die Anzahl der Zellen mit einem Wert kleiner als ein bestimmter Wert zu erhalten, verwenden wir den Operator kleiner als („<„). Wir könnten es entweder direkt in der Formel verwenden oder eine Zellreferenz mit den Kriterien verwenden.
Wenn wir einen Operator in Kriterien in Excel verwenden, müssen wir ihn in doppelte Anführungszeichen setzen., Wenn das Kriterium beispielsweise lautet, dass die Zahl kleiner als 5 sein sollte, müssen wir als Kriterien „<5“ eingeben (siehe Bild unten):
=COUNTIF($B$2:$B$11,“<5″)
Sie können auch haben Sie die Kriterien in einer Zelle und verwenden Sie die Zellreferenz als Kriterien., In diesem Fall müssen Sie die Kriterien NICHT in doppelte Anführungszeichen setzen (siehe Bild unten):
=COUNTIF ($B$2:$B$11,D3)
Außerdem könnte es einen Fall geben, in dem die Kriterien in einer Zelle sein sollen, aber nicht mit dem Operator. Beispielsweise möchten Sie möglicherweise, dass die Zelle D3 die Nummer 5 hat und nicht <5.,
In diesem Fall müssen Sie ein Kriterienargument erstellen, das eine Kombination aus Operator und Zellenreferenz ist:
=COUNTIF($B$2:$B$11,“<„&D3)
HINWEIS: Wenn Sie einen Operator und eine eine Zellreferenz, der Operator ist immer in doppelten Anführungszeichen. Der Operator und die Zellreferenz werden durch ein kaufmännisches und verbunden (&).,
#4 Zähle Zellen mit mehreren Kriterien-Zwischen zwei Werten
Um eine Anzahl von Werten zwischen zwei Werten zu erhalten, müssen wir mehrere Kriterien in der COUNTIF-Funktion verwenden.
Hier sind zwei Methoden, dies zu tun:
METHODE 1: Mit COUNTIFS Funktion
COUNTIFS Funktion kann mehrere Kriterien als Argumente behandeln und zählt die Zellen nur, wenn alle Kriterien WAHR sind., Um Zellen mit Werten zwischen zwei angegebenen Werten (z. B. 5 und 10) zu zählen, 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 gleich (>=) und kleiner als gleich (<=)., Hier ist die Formel:
=COUNTIFS ($B$2:$B$11,“>=5″,$B$2:$B$11,“<=10″)
Sie können diese Kriterien auch in Zellen haben und die Zellreferenz als Kriterium verwenden. In diesem Fall müssen Sie die Kriterien NICHT in doppelte Anführungszeichen setzen (siehe Bild unten):
Sie können auch eine Kombination von Zellreferenzen und Operatoren verwenden (wobei der Operator direkt in die Formel eingegeben wird). Wenn Sie einen Operator und eine Zellreferenz kombinieren, ist der Operator immer in doppelten Anführungszeichen., Der Operator und die Zellreferenz werden durch ein kaufmännisches und verbunden (&).
METHODE 2: Verwenden von zwei COUNTIF-Funktionen
Wenn Sie mehrere Kriterien haben, können Sie entweder COUNTIFS verwenden oder eine Kombination von COUNTIF-Funktionen erstellen., Die folgende Formel würde auch 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, die einen Wert größer als 5 haben, und subtrahieren die Anzahl der Zellen mit einem Wert größer als 10. Dies würde uns das Ergebnis als 5 geben (dh die Anzahl der Zellen mit Werten von mehr als 5 und weniger als 10).,3c219a21b4″>
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 die Formel um sowohl ‚5‘ als auch ’10‘ von der Zählung auszuschließen, 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 Kriterien haben in Zellen und verwenden Sie die Zellen Referenzen, oder Sie können eine Kombination von Operatoren und Zellen Referenzen verwenden.,
Verwenden von Textkriterien in Excel-Funktionen
#1 Zellen zählen wenn Kriterien einem bestimmten Text entsprechen
Um Zellen zu zählen, die eine genaue Übereinstimmung mit dem angegebenen Text enthalten, können wir diesen Text einfach als Kriterien verwenden. Wenn ich beispielsweise im Datensatz (unten im Bild gezeigt) alle Zellen mit dem Namen Joe zählen möchte, kann ich die folgende Formel verwenden:
=COUNTIF($B$2:$B$11,“Joe“)
Da dies eine Textzeichenfolge ist, muss ich die Textkriterien in doppelte Anführungszeichen setzen.,
Sie können auch die Kriterien in einer Zelle haben und dann diese Zellreferenz verwenden (wie unten gezeigt):
=COUNTIF($B$2:$B$11,E3)
HINWEIS: Sie können falsche Ergebnisse erzielen, wenn die Kriterien oder Kriterien führende/nachfolgende Leerzeichen enthalten.bereich. Stellen Sie sicher, dass Sie die Daten bereinigen, bevor Sie diese Formeln verwenden.
#2 Zellen zählen wenn Kriterien EINEM bestimmten Text NICHT entsprechen
Ähnlich wie im obigen Beispiel können Sie auch Zellen zählen, die keinen bestimmten Text enthalten., Dazu müssen wir den Operator not equal to (<>).
Angenommen, Sie möchten alle Zellen zählen, die nicht den Namen JOE enthalten, hier ist die Formel, die dies tun wird:
=COUNTIF($B$2:$B$11,“<>Joe“)
Sie können auch haben Sie die Kriterien in einer Zelle und verwenden Sie die Zellreferenz als Kriterien., In diesem Fall müssen Sie die Kriterien NICHT in doppelte Anführungszeichen setzen (siehe Bild unten):
=COUNTIF ($B$2:$B$11,E3)
Es könnte auch einen Fall geben, in dem die Kriterien in einer Zelle sein sollen, aber nicht mit dem Operator. Beispielsweise soll die Zelle D3 den Namen Joe haben und nicht <>Joe.,
In diesem Fall müssen Sie ein Kriterienargument erstellen, das eine Kombination aus Operator und Zellreferenz ist (siehe Abbildung unten):
=COUNTIF($B$2:$B$11,“<>“&E3)
Wenn Sie einen Operator und eine Zellreferenz kombinieren, ist der Operator immer in doppelten Anführungszeichen. Der Operator und die Zellreferenz werden durch ein kaufmännisches und verbunden (&).
Mit Datumskriterien in Excel COUNTIF und COUNTIFS Funktionen
Excel speichern Datum und Uhrzeit als Zahlen., So können wir es genauso verwenden, wie wir Zahlen verwenden.
#1 Zellen zählen wenn Criteria GLEICH einem bestimmten Datum ist
Um die Anzahl der Zellen zu erhalten, die das angegebene Datum enthalten, würden wir den Operator equal to (=) zusammen mit dem Datum verwenden.
Um das Datum zu verwenden, empfehle ich die Datumsfunktion, da dadurch Fehler im Datumswert vermieden werden können. Wenn ich beispielsweise das Datum 1. September 2015 verwenden möchte, kann ich die Datumsfunktion wie unten gezeigt verwenden:
=DATE(2015,9,1)
Diese Formel würde trotz regionaler Unterschiede dasselbe Datum zurückgeben., Zum Beispiel wäre 01-09-2015 der 1. September 2015 gemäß der US-Datumssyntax und der 09.Januar 2015 gemäß der britischen Datumssyntax. Diese Formel würde jedoch immer den 1. September 2105 zurückgeben.
Hier ist die Formel,um die Anzahl der Zellen zu zählen, die das Datum 02-09-2015 enthalten:
=COUNTIF($A$2:$A$11, DATE(2015,9,2))
#2 Zählen Sie Zellen, wenn das Datum VOR oder NACH einem bestimmten Datum liegt
Um Zellen zu zählen, die das Datum vor oder nach einem bestimmten Datum enthalten, können weniger als/größer als Operatoren.,
Wenn ich zum Beispiel alle Zellen zählen möchte, die ein Datum nach dem 02.September 2015 enthalten, kann ich die Formel verwenden:
=COUNTIF($A$2:$A$11,“>“&DATE(2015,9,2))
In ähnlicher Weise können Sie auch die Anzahl der Zellen vor einem bestimmten Datum zählen. Wenn Sie ein Datum in die Zählung aufnehmen möchten, verwenden Sie den Operator and ‚equal to‘ zusammen mit dem Operator‘ greater than/less than‘.
Sie können auch eine Zellenreferenz verwenden, die ein Datum enthält., In diesem Fall müssen Sie den Operator (in doppelten Anführungszeichen) mit dem Datum mit einem kaufmännischen und kombinieren (&).
Siehe Beispiel unten:
=COUNTIF($A$2:$A$11,“>“&F3)
#3 Zählen Sie Zellen mit mehreren Kriterien – zwischen zwei Daten
Um eine Anzahl von Werten zwischen zwei Werte, wir müssen mehrere Kriterien in der COUNTIF Funktion verwenden.
Wir können dies mit zwei Methoden tun – einer einzelnen COUNTIFS-Funktion oder zwei COUNTIF-Funktionen.,
METHODE 1: Mit COUNTIFS Funktion
COUNTIFS Funktion kann mehrere Kriterien als Argumente und zählt die Zellen nur, wenn alle Kriterien wahr sind. Um Zellen mit Werten zwischen zwei angegebenen Daten zu zählen (z. B. 2.September und 7. September), können wir die folgende COUNTIFS-Funktion verwenden:
=COUNTIFS($A$2:$A$11,“>“&DATE(2015,9,2),$A$2:$A$11,“<„&DATUM(2015,9, 7))
Die obige Formel zählt keine Zellen, die die angegebenen Daten enthalten., Wenn Sie auch diese Daten einschließen möchten, verwenden Sie die Operatoren größer als gleich (>=) und kleiner als gleich (<=). Hier ist die Formel:
=ZÄHLENWENNS($A$2:$A$11,“>=“&DATE(2015,9,2),$A$2:$A$11,“<=“&DATE(2015,9,7))
Sie können auch die Daten in einer Zelle bei und verwenden Sie den Zellbezug als die Kriterien. In diesem Fall können Sie den Operator nicht mit dem Datum in den Zellen haben., Sie müssen Operatoren manuell in die Formel einfügen (in doppelten Anführungszeichen) und Zellreferenz mit einem kaufmännischen und hinzufügen (&). Siehe das Bild unten:
=COUNTIFS($A$2:$A$11,“>“&F3,$A$2:$A$11,“<„&G3)
METHODE 2: Verwenden von COUNTIF-Funktionen
Wenn Sie mehrere Kriterien haben, können Sie entweder eine COUNTIFS-Funktion verwenden oder eine Kombination aus zwei COUNTIF-Funktionen erstellen., Die folgende Formel würde auch den Trick machen:
=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, die ein Datum nach dem 2.September haben, und subtrahieren die Anzahl der Zellen mit Daten nach dem 7. September. Dies würde uns das Ergebnis als 7 geben (das ist die Anzahl der Zellen, die Daten nach dem 2.September und am oder vor dem 7. September haben).,>DATE(2015,9,2))-COUNTIF($A$2:$A$11,“>“&DATE(2015,9,7))
Wenn Sie beide Daten vom Zählen 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,DATE 2015,9,7)))
Außerdem können Sie die Kriteriendaten in Zellen haben und die Zellreferenzen verwenden(zusammen mit Operatoren in doppelten Anführungszeichen, die mit kaufmännischem und verbunden sind).,
Verwenden von PLATZHALTERZEICHEN in Kriterien in COUNTIF & COUNTIFS Funktionen
In Excel gibt es drei Platzhalterzeichen:
Sie können die COUNTIF-Funktion mit Platzhalterzeichen verwenden, um Zellen zu zählen, wenn eine andere integrierte Zählfunktion fehlschlägt. Angenommen, Sie haben einen Datensatz wie unten gezeigt:
Nehmen wir nun verschiedene Beispiele:
#1 Zählen Sie Zellen, die Text enthalten
Um Zellen mit Text darin zu zählen, können wir das Platzhalterzeichen * (Sternchen) verwenden., Da asterisk eine beliebige Anzahl von Zeichen darstellt, würde es alle Zellen zählen, die Text enthalten. Hier ist die Formel:
=COUNTIFS ($C$2:$C$11,“*“)
Hinweis: Die obige Formel ignoriert Zellen, die Zahlen, leere Zellen und logische Werte enthalten, zählt jedoch die Zellen, die einen Apostroph enthalten (und daher leer erscheinen) oder Zellen, die leere Zeichenfolgen ( = „“ ) enthalten, die möglicherweise als Teil einer Formel zurückgegeben wurden.
Hier ist ein ausführliches Tutorial zum Umgang mit Fällen, in denen eine leere Zeichenfolge oder ein Apostroph vorhanden ist.,
Hier ist ein ausführliches Tutorial zum Umgang mit Fällen, in denen leere Zeichenfolgen oder Apostrophe vorhanden sind.
Unten ist ein Video, das verschiedene Szenarien des Zählens von Zellen mit Text darin erklärt.
#2 Zählen Sie nicht leere Zellen
Wenn Sie an die Verwendung der COUNTA-Funktion denken, denken Sie noch einmal darüber nach.
Versuchen Sie es und es könnte Sie scheitern. COUNTA zählt auch eine Zelle, die eine leere Zeichenfolge enthält (häufig von Formeln als =““ oder wenn Personen nur einen Apostroph in eine Zelle eingeben). Zellen, die leere Zeichenfolgen enthalten, sehen leer aus, sind es aber nicht und werden daher von der COUNTA-Funktion gezählt.,
COUNTA zählt auch eine Zelle, die eine leere Zeichenfolge enthält (häufig von Formeln als =““ oder wenn Personen nur einen Apostroph in eine Zelle eingeben). Zellen, die leere Zeichenfolgen enthalten, sehen leer aus, sind es aber nicht und werden daher von der COUNTA-Funktion gezählt.
Wenn Sie also die Formel =COUNTA(A1:A11) verwenden, wird 11 zurückgegeben, während 10 zurückgegeben werden sollte.
Hier ist das Update:
=COUNTIF($A$1:$A$11,“?*“)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))
wir verstehen Sie diese Formel, indem Sie es brechen:
- ZÄHLENWENN($N$8:$N$18,“?,*“) – Dieser Teil der Formel gibt 5. Dies schließt jede Zelle ein, die ein Textzeichen enthält. Ein ? repräsentiert ein Zeichen und * repräsentiert eine beliebige Anzahl von Zeichen. Daher die Kombination ?* in den Kriterien zwingt Excel, Zellen zu zählen, die mindestens ein Textzeichen enthalten.
- COUNT($A$1:$A$11) – Diese zählt alle Zellen, die zahlen enthalten. Im obigen Beispiel wird 3 zurückgegeben.
- SUMPRODUCT(–ISLOGICAL($A$1:$A$11) – Diese zählt alle Zellen, logische Werte enthalten. Im obigen Beispiel wird 2 zurückgegeben.,
#3 Zählen Sie Zellen, die bestimmten Text enthalten
Angenommen, wir möchten alle Zellen zählen, in denen der Name des Vertriebsmitarbeiter mit J beginnt. Hier ist die Formel:
=COUNTIFS ($C$2:$C$11,“J*“)
Das Kriterium J* gibt an, dass der Text in einer Zelle mit J beginnen soll und eine beliebige Anzahl von Zeichen enthalten kann.
Wenn Sie Zellen zählen möchten, die das Alphabet irgendwo im Text enthalten, flankieren Sie es mit einem Sternchen auf beiden Seiten., Wenn Sie beispielsweise Zellen zählen möchten, die das Alphabet „a“ enthalten, verwenden Sie *a* als Kriterium.