✅ So verwenden Sie mehrere Kriterien in Excel ZÄHLENWENN und ZÄHLENWENN

Inhaltsverzeichnis

Eine Einführung in die Funktionen ZÄHLENWENN und ZÄHLENWENN in Excel
Verwenden von NUMBER-Kriterien in Excel-ZÄHLENWENN-Funktionen
Verwenden von TEXT-Kriterien in Excel-Funktionen
Verwenden von DATUM-Kriterien in den Excel-Funktionen ZÄHLENWENN und ZÄHLENWENN
Verwenden von WILDCARD CHARACTERS in Kriterien in den ZÄHLENWENN- und ZÄHLENWENN-Funktionen

Excel hat viele Funktionen, bei denen ein Benutzer ein einzelnes oder mehrere Kriterien 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ÄHLENWENN in Excel verwenden.

In diesem Tutorial werden verschiedene Möglichkeiten zur Verwendung eines einzelnen oder mehrerer Kriterien in der ZÄHLENWENN- und ZÄHLENWENN-Funktion in Excel behandelt.

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

Eine Einführung in die Funktionen ZÄHLENWENN und ZÄHLENWENN in Excel

Lassen Sie uns zunächst die Verwendung der Funktionen ZÄHLENWENN und ZÄHLENWENN in Excel in den Griff bekommen.

Excel-ZÄHLENWENN-Funktion (erfordert einzelne Kriterien)

Die Excel-ZÄHLENWENN-Funktion eignet sich am besten für Situationen, in denen Sie Zellen basierend auf einem einzigen Kriterium zählen möchten. Wenn Sie nach mehreren Kriterien zählen möchten, verwenden Sie die Funktion ZÄHLENWENN.

Syntax

=ZÄHLENWENN(Bereich, Kriterien)

Eingabeargumente

Angebot - den Zellbereich, den Sie zählen möchten.
Kriterien - die Kriterien, die anhand des Zellbereichs bewertet werden müssen, damit eine Zelle gezählt wird.

Excel COUNTIFS-Funktion (erfordert mehrere Kriterien)

Die Excel-Funktion ZÄHLENWENN eignet sich am besten für Situationen, in denen Sie Zellen basierend auf mehreren Kriterien zählen möchten.

Syntax

=ZÄHLENWENN(Kriterien_Bereich1, Kriterien1, [Kriterien_Bereich2, Kriterien2]… )

Eingabeargumente

Kriterien_Bereich1 - Der Zellbereich, für den Sie anhand von Kriterien auswerten möchten1.
Kriterien1 - die Kriterien, die Sie für Criteria_range1 auswerten möchten, um zu bestimmen, welche Zellen gezählt werden sollen.
[criteria_range2] - Der Zellbereich, für den Sie anhand von Kriterien auswerten möchten2.
[Kriterien2] - die Kriterien, die Sie für Criteria_range2 auswerten möchten, um zu bestimmen, welche Zellen gezählt werden sollen.

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

Verwenden von NUMBER-Kriterien in Excel-ZÄHLENWENN-Funktionen

#1 Zellen zählen, wenn Kriterien einem Wert entsprechen

Um die Anzahl der Zellen abzurufen, bei denen das Kriterienargument einem angegebenen Wert entspricht, können Sie die Kriterien entweder direkt eingeben oder den Zellbezug verwenden, der 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:

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

Im obigen Beispiel (im Bild) befindet sich das Kriterium in Zelle D3. Sie können die Kriterien auch direkt in die Formel eingeben. Sie können beispielsweise auch Folgendes verwenden:

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

#2 Zellen zählen, wenn das Kriterium GRÖSSER ALS ein Wert ist

Um die Anzahl der Zellen zu ermitteln, deren Wert größer als ein bestimmter Wert ist, verwenden wir den Größer-als-Operator („>“). Wir könnten es entweder direkt in der Formel verwenden oder einen Zellbezug verwenden, der die Kriterien enthält.

Immer wenn wir einen Operator in Kriterien in Excel verwenden, müssen wir ihn in doppelte Anführungszeichen setzen. Wenn das Kriterium beispielsweise größer als 10 ist, müssen wir als Kriterium „>10“ eingeben (siehe Bild unten):

Hier ist die Formel:

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

Sie können die Kriterien auch in einer Zelle haben und den Zellbezug als Kriterium verwenden. In diesem Fall müssen Sie die Kriterien NICHT in doppelte Anführungszeichen setzen:

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

Es kann auch vorkommen, dass die Kriterien in einer Zelle enthalten sein sollen, dies jedoch nicht mit dem Operator. Sie möchten beispielsweise, dass die Zelle D3 die Zahl 10 und nicht >10 hat.

In diesem Fall müssen Sie ein Kriterienargument erstellen, das eine Kombination aus Operator und Zellbezug ist (siehe Bild unten):

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

HINWEIS: Wenn Sie einen Operator und einen Zellbezug kombinieren, steht der Operator immer in doppelten Anführungszeichen. Operator und Zellbezug werden durch ein kaufmännisches Und (&) verbunden.

#3 Zellen zählen, wenn das Kriterium WENIGER ALS ein Wert ist

Um die Anzahl der Zellen mit einem Wert zu ermitteln, der kleiner als ein bestimmter Wert ist, verwenden wir den Kleiner-als-Operator („<“). Wir könnten es entweder direkt in der Formel verwenden oder einen Zellbezug verwenden, der die Kriterien enthält.

Immer 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 soll, müssen wir als Kriterium „<5“ eingeben (siehe Bild unten):

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

Sie können die Kriterien auch in einer Zelle haben und den Zellbezug als Kriterium verwenden. In diesem Fall müssen Sie die Kriterien NICHT in doppelte Anführungszeichen setzen (siehe Bild unten):

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

Es kann auch vorkommen, dass Sie die Kriterien in einer Zelle haben möchten, dies jedoch nicht mit dem Operator. Sie möchten beispielsweise, dass die Zelle D3 die Zahl 5 und nicht <5 hat.

In diesem Fall müssen Sie ein Kriterienargument erstellen, das eine Kombination aus Operator und Zellbezug ist:

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

HINWEIS: Wenn Sie einen Operator und einen Zellbezug kombinieren, steht der Operator immer in doppelten Anführungszeichen. Operator und Zellbezug werden durch ein kaufmännisches Und (&) verbunden.

#4 Zellen mit mehreren Kriterien zählen – zwischen zwei Werten

Um eine Anzahl von Werten zwischen zwei Werten zu erhalten, müssen wir mehrere Kriterien in der Funktion ZÄHLENWENN verwenden.

Hier sind zwei Methoden, dies zu tun:

METHODE 1: Verwenden der ZÄHLENWENN-Funktion

Die ZÄHLENWENN-Funktion kann mehrere Kriterien als Argumente verarbeiten 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:

=ZÄHLENWENN($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:

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

Sie können diese Kriterien auch in Zellen haben und den Zellbezug 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 aus Zellreferenzen und Operatoren verwenden (wobei der Operator direkt in die Formel eingegeben wird). Wenn Sie einen Operator und einen Zellbezug kombinieren, steht der Operator immer in doppelten Anführungszeichen. Operator und Zellbezug werden durch ein kaufmännisches Und (&) verbunden.

METHODE 2: Verwenden von zwei ZÄHLENWENN-Funktionen

Wenn Sie über mehrere Kriterien verfügen, können Sie entweder ZÄHLENWENN verwenden oder eine Kombination von ZÄHLENWENN-Funktionen erstellen. Die folgende Formel würde auch das gleiche tun:

=ZÄHLENWENN($B$2:$B$11”,>5″)-ZÄHLENWENN($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 die Anzahl der Zellen mit einem Wert größer als 10. Dies würde uns als Ergebnis als 5 ergeben (das ist die Anzahl der Zellen, die Werte größer 5 und kleiner gleich 10).

Wenn die Formel sowohl 5 als auch 10 enthalten soll, verwenden Sie stattdessen die folgende Formel:

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

Wenn Sie möchten, dass die Formel sowohl „5“ als auch „10“ von der Zählung ausschließt, verwenden Sie die folgende Formel:

=ZÄHLENWENN($B$2:$B$11",>=5″)-ZÄHLENWENN($B$2:$B$11",>10″)-ZÄHLENWENN($B$2:$B$11,10)

Sie können diese Kriterien in Zellen haben und die Zellreferenzen verwenden, oder Sie können eine Kombination aus Operatoren und Zellreferenzen verwenden.

Verwenden von TEXT-Kriterien in Excel-Funktionen

#1 Zellen zählen, wenn Kriterien einem angegebenen Text entsprechen

Um Zellen zu zählen, die eine genaue Übereinstimmung mit dem angegebenen Text enthalten, können wir diesen Text einfach als Kriterium verwenden. Wenn ich beispielsweise im Datensatz (im Bild unten gezeigt) alle Zellen mit dem Namen Joe zählen möchte, kann ich die folgende Formel verwenden:

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

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

Sie können die Kriterien auch in einer Zelle haben und dann diesen Zellbezug verwenden (wie unten gezeigt):

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

HINWEIS: Sie können falsche Ergebnisse erhalten, wenn die Kriterien oder der Kriterienbereich führende/nachfolgende Leerzeichen enthalten. Stellen Sie sicher, dass Sie die Daten bereinigen, bevor Sie diese Formeln verwenden.

#2 Zellen zählen, wenn Kriterien NICHT GLEICH mit einem angegebenen Text sind

Ä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 den Namen JOE nicht enthalten, hier ist die Formel, die dies tut:

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

Sie können die Kriterien auch in einer Zelle haben und den Zellbezug als Kriterium verwenden. In diesem Fall müssen Sie die Kriterien NICHT in doppelte Anführungszeichen setzen (siehe Bild unten):

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

Es kann auch vorkommen, dass die Kriterien in einer Zelle enthalten sein sollen, aber nicht beim Operator. Sie möchten beispielsweise, dass die Zelle D3 den Namen Joe und nicht Joe hat.

In diesem Fall müssen Sie ein Kriterienargument erstellen, das eine Kombination aus Operator und Zellbezug ist (siehe Bild unten):

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

Wenn Sie einen Operator und einen Zellbezug kombinieren, steht der Operator immer in doppelten Anführungszeichen. Operator und Zellbezug werden durch ein kaufmännisches Und (&) verbunden.

Verwenden von DATUM-Kriterien in den Excel-Funktionen ZÄHLENWENN und ZÄHLENWENN

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

#1 Zellen zählen, wenn die Kriterien einem angegebenen Datum entsprechen

Um die Anzahl der Zellen zu ermitteln, die das angegebene Datum enthalten, würden wir den Gleichheitsoperator (=) zusammen mit dem Datum verwenden.

Um das Datum zu verwenden, empfehle ich die Verwendung der DATE-Funktion, da sie jede Fehlermöglichkeit im Datumswert beseitigt. Wenn ich zum Beispiel das Datum 1. September 2015 verwenden möchte, kann ich die DATE-Funktion wie unten gezeigt verwenden:

=DATUM(2015,9,1)

Diese Formel würde trotz regionaler Unterschiede dasselbe Datum zurückgeben. Der 01-09-2015 wäre beispielsweise 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 09.02.2015 enthalten:

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

#2 Zellen zählen, wenn die Kriterien VOR oder NACH einem bestimmten Datum sind

Um Zellen zu zählen, die ein Datum vor oder nach einem bestimmten Datum enthalten, können wir die Kleiner als/Größer als-Operatoren verwenden.

Wenn ich beispielsweise alle Zellen zählen möchte, die ein Datum enthalten, das nach dem 2. September 2015 liegt, kann ich die Formel verwenden:

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

Ebenso können Sie die Anzahl der Zellen vor einem bestimmten Datum zählen. Wenn Sie ein Datum in die Zählung einbeziehen möchten, verwenden Sie und den Operator „gleich“ zusammen mit dem Operator „größer als/kleiner als“.

Sie können auch einen Zellbezug verwenden, der 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:

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

#3 Zellen mit mehreren Kriterien zählen – zwischen zwei Daten

Um eine Anzahl von Werten zwischen zwei Werten zu erhalten, müssen wir mehrere Kriterien in der Funktion ZÄHLENWENN verwenden.

Wir können dies mit zwei Methoden tun - Eine einzelne ZÄHLENWENN-Funktion oder zwei ZÄHLENWENN-Funktionen.

METHODE 1: Verwenden der ZÄHLENWENN-Funktion

Die ZÄHLENWENN-Funktion kann mehrere Kriterien als Argumente annehmen und zählt die Zellen nur, wenn alle Kriterien WAHR sind. Um Zellen mit Werten zwischen zwei angegebenen Daten (z. B. 2. September und 7. September) zu zählen, können wir die folgende ZÄHLENWENN-Funktion verwenden:

=ZÄHLENWENN($A$2:$A$11",>"&DATE(2015,9,2),$A$2:$A$11",<"&DATE(2015,9,7))

Die obige Formel zählt keine Zellen, die die angegebenen Daten enthalten. Wenn Sie diese Datumsangaben ebenfalls einschließen möchten, verwenden Sie die Operatoren größer als gleich (>=) und kleiner als gleich (<=). Hier ist die Formel:

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

Sie können auch die Datumsangaben in einer Zelle haben und den Zellbezug als Kriterium verwenden. In diesem Fall können Sie den Operator mit dem Datum nicht in den Zellen haben. Sie müssen der Formel manuell Operatoren (in doppelten Anführungszeichen) und den Zellbezug mit einem kaufmännischen Und (&) hinzufügen. Siehe das Bild unten:

=ZÄHLENWENN($A$2:$A$11“,>“&F3,$A$2:$A$11“,<“&G3)

METHODE 2: ZÄHLENWENN-Funktionen verwenden

Wenn Sie über mehrere Kriterien verfügen, können Sie entweder eine ZÄHLENWENN-Funktion verwenden oder eine Kombination aus zwei ZÄHLENWENN-Funktionen erstellen. Die folgende Formel würde auch den Zweck erfüllen:

=ZÄHLENWENN($A$2:$A$11”,>”&DATE(2015,9,2))-ZÄHLENWENN($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 wir subtrahieren die Anzahl der Zellen mit Datumsangaben nach dem 7. September nach dem 2. September und am oder vor dem 7. September).

Wenn die Formel nicht sowohl den 2. September als auch den 7. September zählen soll, verwenden Sie stattdessen die folgende Formel:

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

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

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

Außerdem können Sie die Kriteriendatumsangaben in Zellen haben und die Zellenreferenzen verwenden (zusammen mit Operatoren in doppelten Anführungszeichen, die mit kaufmännischem Und-Zeichen verbunden sind).

Verwenden von WILDCARD CHARACTERS in Kriterien in den ZÄHLENWENN- und ZÄHLENWENN-Funktionen

In Excel gibt es drei Platzhalterzeichen:

* (Sternchen) - Es steht für eine beliebige Anzahl von Zeichen. Ex* könnte beispielsweise Excel, Excel, Beispiel, Experte usw. bedeuten.
? (Fragezeichen) - Es repräsentiert ein einzelnes Zeichen. Tr?mp könnte zum Beispiel Trump oder Tramp bedeuten.
~ (Tilde) - Es wird verwendet, um ein Platzhalterzeichen (~, *, ?) im Text zu identifizieren.

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 Zellen zählen, die Text enthalten

Um Zellen mit Text zu zählen, können wir das Platzhalterzeichen * (Sternchen) verwenden. Da ein Sternchen eine beliebige Anzahl von Zeichen darstellt, würde es alle Zellen zählen, die Text enthalten. Hier ist die Formel:

=ZÄHLENWENN($C$2:$C$11”,*”)

Hinweis: Die obige Formel ignoriert Zellen, die Zahlen, leere Zellen und logische Werte enthalten, würde jedoch die Zellen zählen, die ein Apostroph enthalten (und daher leer erscheinen) oder Zellen, die eine leere Zeichenfolge (=““) enthalten, die möglicherweise als a . zurückgegeben wurde Teil einer Formel.

Hier ist ein detailliertes Tutorial zum Umgang mit Fällen, in denen eine leere Zeichenfolge oder ein Apostroph vorhanden ist.

Hier ist ein detailliertes Tutorial zum Umgang mit Fällen, in denen leere Zeichenfolgen oder Apostrophe vorhanden sind.

Unten ist ein Video, das verschiedene Szenarien zum Zählen von Zellen mit Text erklärt.

#2 Nicht leere Zellen zählen

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

Versuchen Sie es und es könnte Sie scheitern lassen. ANZAHLA zählt auch eine Zelle, die eine leere Zeichenfolge enthält (oft von Formeln als =““ zurückgegeben 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.

ANZAHLA zählt auch eine Zelle, die eine leere Zeichenfolge enthält (oft von Formeln als =““ zurückgegeben 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 =ANZAHLA(A1:A11) verwenden, gibt sie 11 zurück, während sie 10 zurückgeben sollte.

Hier ist die Lösung:

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

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

ZÄHLENWENN($N$8:$N$18”,?*”) – Dieser Teil der Formel gibt 5 zurück. Dies schließt jede Zelle ein, die ein Textzeichen enthält. EIN ? steht für ein Zeichen und * steht für eine beliebige Anzahl von Zeichen. Daher zwingt die Kombination ?* in den Kriterien Excel dazu, Zellen zu zählen, die mindestens ein Textzeichen enthalten.
COUNT($A$1:$A$11) - Dies zählt alle Zellen, die Zahlen enthalten. Im obigen Beispiel gibt es 3 zurück.
SUMPRODUCT(-ISLOGICAL($A$1:$A$11) - Dies zählt alle Zellen, die logische Werte enthalten. Im obigen Beispiel gibt es 2 zurück.

#3 Zellen zählen, die bestimmten Text enthalten

Nehmen wir an, wir möchten alle Zellen zählen, bei denen der Name des Vertriebsmitarbeiters mit J beginnt. Dies kann leicht durch die Verwendung eines Platzhalterzeichens in der Funktion ZÄHLENWENN erreicht werden. Hier ist die Formel:

=ZÄHLENWENN($C$2:$C$11,”J*”)

Das Kriterium J* gibt an, dass der Text in einer Zelle mit J beginnen soll und beliebig viele 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.

Dieser Artikel ist im Vergleich zu meinen anderen Artikeln ungewöhnlich lang. Ich hoffe, es hat Ihnen gefallen. Teilen Sie mir Ihre Meinung mit, indem Sie einen Kommentar hinterlassen.

Sie können auch die folgenden Excel-Tutorials nützlich finden: