Geschrieben von: Sven
Veröffentlicht am: 03.05.2018 13:44 Uhr
Bewertung:
Die Zählenwenns Funktion kommt dann zum Einsatz, wenn Sie anhand einer oder mehrerer Bedingungen die Anzahl bestimmter Werte in einem Bereich zählen wollen. In dieser Anleitung erfahren Sie, wie Sie die Funktion verwenden können.

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.

Die Zählenwenns-Funktion mit einer einzigen Bedingung

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.

Die Zählenwenns-Funktion mit zwei Bedingungen

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.

Die Zählenwenns-Funktion mit drei Bedingungen

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.

Die Zählenwenns Funktion mit drei verschiedenen Bereichen

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).

Zwei Zählenwenns Funktionen kombiniert

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.

Zählenwenns Funktion mit Zellbezügen

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.

Zählenwenns Funktion mit Zweizeiligen Bereichen

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).

Hat Ihnen meine Anleitung weitergeholfen?

😀
Dann würde ich mich sehr über ein freiwilliges Dankeschön via PayPal freuen!



Vielen Dank, für Ihre Unterstützung.

Kommentare: (4)

Ralf Hesse
11.11.2019 17:21 Uhr
Ein sehr schönes und wesentlich umfangreicheres Beispiel als bei MS!
Wie aber muss ich vorgehen, wenn ich statt der Ver-UND-ung auch Kriterien ver-ODERn will? - Z.B. habe ich eine Spalte die ca 5 Statuus annehmen kann. Ich möchte aber nur 3 der 5 auswerten, zusätzlich zu 2 anderen. Wie geht das?

Vielen Dank im voraus
12.11.2019 20:38 Uhr
Hallo Ralf,

danke für die interessante Frage.

Im Moment würde ich das Problem wie folgt angehen, indem ich einfach wie in Beispiel 5 mehrere Zählenwenns Funktionen mit einem „+“ miteinander verbinde. So kannst du ja grundlegend auch das Ergebnis eines logischen Oders erreichen. Vielleicht gibt es auch noch eine bessere Lösung, auf die komme ich allerdings im Moment auch nicht.

Allerdings muss man dabei vorsichtig sein und lieber einmal zu oft gegenprüfen als zu wenig.

Das folgende Beispiel gibt Bestellungen von Kunde 1 aus, die entweder über 100 oder unter 20 Euro sind:

=ZÄHLENWENNS(A3:A16;">100";B3:B16;"1")+ZÄHLENWENNS(A3:A16;"20";B3:B16;"1")

Als Ergebnis erhalte ich eine 3, da zwei Treffer mit über 100 und 1 Treffer mit unter 20 Euro Bestellwert von Kunde 1 gefunden wurde.

Ich hoffe ich konnte dir weiterhelfen.

Gruß

Sven
https://www.anleitung24.com
Patrick
08.06.2021 10:51 Uhr
Hallo
Vielen Dank für all die Beispiele, leider funktioniert das bei mir nicht so recht.
Ich habe folgendes Szenario.
Ich habe 3 Kriterien nach denen gesucht und die Summe ermittelt werden soll.
Meine Formel dazu sieht wie folgt aus:
=ZÄHLENWENNS(E1:AJ1;D715;D5:D704;E714;E5:E704;"1")

"E1:AJ1" in diesem Bereich wird nach einem spezifischen Namen gesucht, der in Zelle D715 vorhanden ist.
"D5:D704" in diesem Bereich wird nach einem spezifischen Wort gesucht, welches in Zelle E714 vorhanden ist.
Sind die oberen beiden Kriterien vollständig so soll er im Bereich "E5:E704" nach dem Wert "1" suchen und dann zählen wie oft die 1 vorkommt.

Was er aber bei mir ausspuckt ist dann #WERT!


Selbst wenn ich #WERT! durch "=WENNFEHLER(Ihre Formel;den Wert den sie sehen möchten, wenn ein Fehler ausgegeben wird)" ersetze durch 0, wird nichts zusammengezählt.

Habe ich irgendwo einen Denkfehler?

Hast du da eine Idee?

Viele Grüße
Patrick
08.06.2021 14:35 Uhr
Hallo Patrick,

hatte ich so noch nie probiert. Es scheint aber generell nicht zu funktionieren, in einer Funktion Zeilen und Spalten-Bereiche zu kreuzen. Vielleicht irre ich mich aber auch. Hier würde ggf. helfen die obere Zeile zuvor zu transponieren. Bedenke auch, dass alle Bereiche gleich groß sein müssen (bis Zeile 704, in deinem Fall), dies würde ja dann auch auf den Zeilen-Bereich zutreffen (hat in meinem Test aber nicht geklappt, trotz identischer Länge).

Zum Thema Fehler ersetzen:

=WENN(ISTFEHLER(deine-formel-ohne-führendes-gleichzeichen);"Beispieltext";deine-formel-ohne-führendes-gleichzeichen)

oder so:

=WENN(ISTFEHLER(C7);"Beispieltext";C7)

Gruß

Sven
https://www.anleitung24.com

Kommentar schreiben:

Noch 5000 Zeichen übrig.
Hinweise zur Verarbeitung Ihrer Angaben und Widerspruchsrechte finden Sie in unserer Datenschutzerklärung.