Zählenwenns Funktion in Excel anwenden (bedingt zählen) – Anleitung

Die Zählenwenns Funktion ist ähnlich zu der Zählenwenn Funktion, der Unterschied liegt darin, dass Sie bei der Zählenwenns Funktion ohne Tricks mehr als eine Bedingung stellen können. Anhand dieser Bedingungen können die Werte aus bestimmten Zellbereichen gezählt werden. Allerdings ist diese Funktion in frühen Versionen von Excel (..., 2000, 2003) noch nicht enthalten. In dieser Anleitung wurde Libre Calc verwendet, sollte aber identisch zur Verwendung in Excel sein.

Formelaufbau der Zählenwenns Funktion (Syntax)

=ZÄHLENWENNS(Bereich1;Bedingung1;Bereich2;Bedingung2;…)

Die Formel zählt die Werte, die in Bereich1 vorkommen und die entsprechenden Bedingungen erfüllen. Wird ein 2. Bereich und eine 2. Bedingung angegeben, müssen die für den 2. Bereich angegebenen Bedingungen ebenfalls zutreffen, dies führt sich für jede weitere Bedingung fort. Sozusagen ist hier jeweils eine logische UND-Verknüpfung implementiert.

Beispiele für die Zählenwenns Funktion

Für die folgenden Beispiele liegen Ihnen Bestellinformationen zu Ihren Kunden vor. Zum einen der Wert der Waren, der jeweiligen Bestellung (Spalte A), zum Anderen die Kundennummer (Spalte B), von dem die jeweilige Bestellung ausging und die Information, ob die jeweilige Ware abgeholt oder geliefert wurde (Spalte C).

Bei wenigen Einträgen, wie hier in den Beispielen, könnten Sie die Ergebnisse noch manuell ermitteln, doch wenn Sie eine Tabelle mit beispielsweise 20.000 Einträgen vorfinden, würde dies ohne die Zählenwenns Funktion in tagelanger Arbeit ausarten.

Beispiel 1: Zählenwenns Funktion mit einer einzigen Bedingung

Hier in dem ersten Beispiel soll nun herausgefunden werden, wieviele Bestellungen der Kunde mit der Kundennummer „1“ insgesamt getätigt hat. Dies können Sie sowohl mit der Zählenwenn, als auch mit der Zählenwenns Formel lösen, weil hier nur eine einzige Bedingung gestellt wird. Da es hier jedoch um die Zählenwenns Funktion geht, wird hier diese bevorzugt.

Dazu benötigen Sie den Bereich (Hier B3 bis B16), also die Kundennummern und eine Bedingung (Hier 1), also die Kundennummer des Kunden, dessen Anzahl Sie wissen möchten.

Die Formel dazu sieht wie folgt aus:

=ZÄHLENWENNS(B3:B16;“1“)

Wichtig: Die Bedingungen müssen dabei in Anführungszeichen stehen, solange es sich nicht um einen Zellbezug oder eine Formel handelt (ein Beispiel mit Zellbezug finden Sie im Beispiel Nr. 6).

Das Ergebnis ist „4“, dies können Sie prüfen, indem Sie händisch die Kundennummer 1 in Spalte B zählen, diese Nummer existiert genau 4 Mal.

Die Formel durchsucht also den Bereich „B3 bis B16“ nach dem Wert „1“ und zählt alle auf die Bedingung zutreffenden Werte. Die Anzahl wird dann als Ergebnis ausgegeben.

Beispiel 2: Zählenwenns Funktion mit zwei Bedingungen

In dem zweiten Beispiel soll nun ermittelt werden, wieviele Bestellungen im Wert von mehr als 100,00 € der Kunde mit der Kundennummer 1 getätigt hat.

Die Formel dazu sieht wie folgt aus:

=ZÄHLENWENNS(A3:A16;“>100“;B3:B16;“1“)

Die Formel bringt zwei mal jeweils einen Bereich und eine Bedingung mit.

Die erste Bedingung ist, dass im Bereich A3 bis A16 die Werte größer als 100 sein müssen.

Die zweite Bedingung ist, dass im Bereich B3 bis B16 der Wert eine 1 sein muss.

Die beiden Bedingungen werden prinzipbedingt mit einem UND verknüpft, es müssen also beide Bedingungen zutreffen. Im Grunde wird von Excel geprüft, ob Wert1 aus Bereich1 und Wert1 aus Bereich2 jeweils auf seine Bedingung zutrifft. Ist dies der Fall, erhöht sich der Zähler um +1, ansonsten nicht. Dies wird durch die Funktion dann jeweils für Wert2, Wert3 usw. durchgeführt, bis die Funktion am Ende der gewählten Bereiche angekommen ist. Hinweis: Wurden unterschiedlich große Bereiche gewählt, wird ein Fehler ausgegeben.

Um dies etwas zu verdeutlichen, gehen wir mal die für den jeweils ersten Wert (hier in Zeile 3 also A3 und B3) der zwei Bereiche die Bedingungen durch:

1. In Zelle A3 steht ein Wert, der größer ist als 100. (Trifft nicht zu)
2. In Zelle B3 steht ein Wert, der eine 1 ist. (Trifft zu)

Da die Bedingungen mit einem mathematischen UND verknüpft sind, ist dies kein Treffer, der Zähler erhöht sich somit nicht.

Die Funktion geht alle Werte (A3 u. B3 || A4 u. B4 || A5 u. B5 || ...) durch und gibt als Ergebnis eine „2“ aus. Wenn Sie in die Liste schauen, sehen Sie, dass das Ergebnis stimmt. Die Zeilen 10 und 16 treffen zu, beide Bestellungen haben einen Wert über 100 Euro und wurden von Kundennummer 1 bestellt.

Beispiel 3: Zählenwenns Funktion mit drei Bedingungen

In diesem Beispiel sollen nun Bestellungen gezählt werden, die mindestens einen Wert von 20 € und maximal einen Wert von 200 € haben und von dem Kunden mit der Kundennummer 1 bestellt wurden.

So sieht die Formel dazu aus:

=ZÄHLENWENNS(A3:A16;“>20“;A3:A16;“<200“;B3:B16;“1“)

In dieser Formel finden Sie drei Bereichsangaben mit jeweils einer Bedingung.

In der ersten Bedingung wird angegeben, dass die Werte im Bereich A3 bis A16 größer sein müssen, als 20.

In der zweiten Bedingung wird angegeben, dass die Werte im Bereich A3 bis A16 kleiner sein müssen, als 200.

In der dritten Bedingung wird angegeben, dass der Wert im Bereich B3 bis B16 eine 1 sein muss.

Wie im Beispiel zuvor erläutert muss die Funktion nun die Bedingungen für den jeweils ersten, zweiten, dritten, … Wert der jeweiligen Bereiche durchlaufen. Dabei ist es auch kein Problem, dass in der 2. Bedingung der gleiche Bereich genutzt wird, wie in der 1. Bedingung.

Gehen wir mal logisch die 3 Bedingungen bezogen auf ihre jeweiligen Bereiche für den jeweils 1. Wert (hier Zeile 3) durch:

1. In Zelle A3 steht ein Wert, der größer ist als 20. (Trifft zu)
2. In Zelle A3 steht ein Wert, der kleiner ist als 200. (Trifft zu)
3. In Zelle B3 steht ein Wert, der eine 1 ist. (Trifft zu)

Somit haben wir hier einen Treffer und der Zähler erhöht sich um +1.

Die Funktion wiederholt dies sozusagen für alle Zeilen, am Ende ergibt neben der Zeile 3 auch die Zeile 16 einen Treffer.

Als Ergebnis wird nun also eine „2“ ausgegeben.

Beispiel 4: Weiteres Beispiel mit 3 Bedingungen

Hier soll herausgefunden werden, wieviele Bestellungen von Kunde 1 vorliegen, die über 100 € betragen und geliefert wurden.

Die Formel sieht wie folgt aus:

=ZÄHLENWENNS(A3:A16;“>100“;B3:B16;“1“;C3:C16;“Lieferung“)

In dieser Formel finden Sie 3 verschiedene Bereiche mit Ihren Bedingungen, die letzte Bedingung enthält Text. Der Text wird hier ebenfalls in Anführungszeichen gesetzt, es ist dabei egal, ob es sich um ein einzelnes Wort oder um einen Satz handelt.

In der ersten Bedingung wird angegeben, dass die Werte im Bereich A3 bis A16 größer sein müssen, als 100.

In der zweiten Bedingung wird angegeben, dass der Wert im Bereich B3 bis B16 eine 1 sein muss.

In der dritten Bedingung wird angegeben, dass der Wert im Bereich C3 bis C16 das Wort „Lieferung“ sein muss.

Das Ergebnis lautet hier „1“, Zeile 10 trifft zu.

Beispiel 5: Zwei Zählenwenns Funktionen addiert

In diesem Beispiel soll herausgefunden werden, wieviele Bestellungen von den Kunden mit den Kundennummern 1 und 2 eingegangen sind und geliefert wurden.

Hierbei handelt es sich um 2 verschiedene Zählenwenns Formeln mit jeweils zwei Bedingungen.

Die gesamte Formel sieht dann so aus:

=ZÄHLENWENNS(B3:B16;“1“;C3:C16;“Lieferung“)+ZÄHLENWENNS(B3:B16;“2“;C3:C16;“Lieferung“)

Die komplette Formel besteht aus den folgenden beiden Formeln:

=ZÄHLENWENNS(B3:B16;“1“;C3:C16;“Lieferung“)

=ZÄHLENWENNS(B3:B16;“2“;C3:C16;“Lieferung“)

Beide Formeln arbeiten komplett unabhängig voneinander ihre Bedingungen ab.

Das Ergebnis beider Formeln wäre jeweils „2“, durch das Pluszeichen werden die Ergebnisse der beiden Formeln ganz normal zusammenaddiert, als stünde dort (=2+2).

Dadurch ergibt sich das Gesamtergebnis „4“.

Dies funktioniert natürlich auch umgekehrt (mit Minus Zeichen).

Beispiel 6: Zählenwenns Funktion mit variabler Bedingung (Zellbezug)

In diesem Beispiel soll wie in Beispiel 2 herausgefunden werden, wieviele Bestellungen der Kunde mit der Kundennummer 1 getätigt hat, die einen Bestellwert von über 100 Euro haben. Dabei soll aber die Kundennummer und der Bestellwert variabel sein, so dass diese Werte verändert werden können, ohne die Formel selbst zu verändern.

Die Formel sieht wie folgt aus:

=ZÄHLENWENNS(A3:A16;“>“&D23;B3:B16;D22)

Vom Prinzip her handelt es sich hier um die gleiche Formel, wie aus Beispiel 2. In der ersten Bedingung wird statt „100“ eine Zelle (D23) angegeben. Durch die Eigenheiten der Zählenwenns Funktion müssen Sie hier diese Schreibweise („>“&D23) verwenden. Das &-Zeichen verkettet dabei sozusagen „>“ und D23 zu >D23. Einen einfachen Bezug (=Zelle), wie in der zweiten Bedingung der Fall, können Sie ganz normal (wie in anderen Funktionen auch) angeben, indem Sie einfach blanko die Zelle eintragen (hier D22). Im 2. Beispiel stand in der Formel direkt die „1“, die Sie hier nun in Zelle D22 einfach ändern können.

Beispiel 7: Zählenwenns mit Zweispaltigen bzw. Zweizeiligen Bereichen:

Die Zählenwenns Funktion kann auch mit mehreren Spalten oder Zeilen als Bereichsangabe umgehen.

Die nachfolgend angewendete Formel sieht wie folgt aus:

=ZÄHLENWENNS(B36:O37;“1“;B34:O35;“>20“)

Als erstes werden nun also wieder die ersten Werte der jeweiligen Bereiche geprüft. Bei mehreren Zeilen verläuft dies nun wie folgt:

Prüfung der Bedingungen für den jew. 1. Wert (B34 = 1 und B36 > 20) – Trifft zu (+1)
Prüfung der Bedingungen für den jew. 2. Wert (B35 = 1 und B37 > 20) – Trifft zu (+1)
3. Wert (C34 = 1und C37 > 20) – Trifft nicht zu
usw.

Da in diesem Beispiel nur die Werte dupliziert wurden, verdoppeln sich also auch die Treffer.

Die Bereiche könnten Sie auch versetzt angeben, da wie oben erläutert immer die ersten Werte, dann die zweiten Werte usw. der jeweiligen Bereiche auf die jeweiligen Bedingungen geprüft werden. Beispielsweise Bereich1 (A1:A10) und Bereich2 (B11:B20).