ZÄHLENWENN in Excel
ZÄHLENWENN zählt, wie viele Zellen in einem Bereich ein Kriterium erfüllen, zum Beispiel alle offenen Rechnungen. Die Formel lautet =ZÄHLENWENN(Bereich;Suchkriterien), etwa =ZÄHLENWENN(C2:C8;"offen"). Für mehrere Bedingungen gleichzeitig nimmst du ZÄHLENWENNS.
Wofür du ZÄHLENWENN brauchst
Wie viele Rechnungen sind noch offen? Wie viele Schadenfälle liegen über CHF 10'000? Wie viele Pendenzen hat jede Person auf dem Tisch? Kommt eine Kundennummer doppelt vor? Alle diese Fragen beantwortest du mit ZÄHLENWENN. Die Funktion zählt Zellen, die eine Bedingung erfüllen, und aktualisiert sich automatisch, sobald sich die Daten ändern.
ZÄHLENWENN ist die Schwester von SUMMEWENN: Die Kriterien funktionieren genau gleich, nur dass gezählt statt summiert wird. Wer eine der beiden Funktionen sicher beherrscht, hat die andere praktisch schon gelernt.
Die Syntax von ZÄHLENWENN
=ZÄHLENWENN(Bereich;Suchkriterien)
- Bereich
- Die Zellen, die gezählt werden sollen, wenn sie das Kriterium erfüllen, zum Beispiel die Statusspalte
C2:C8. Leere Zellen und Zellen mit anderem Inhalt werden nicht mitgezählt. - Suchkriterien
- Die Bedingung. Ein Text wie
"offen"und ein Vergleich wie">=10000"stehen in Anführungszeichen. Eine reine Zahl (5) oder ein Zellbezug (E2) brauchen keine.
Anders als SUMMEWENN hat ZÄHLENWENN kein drittes Argument: Gezählt wird immer im selben Bereich, der auch geprüft wird.
Die wichtigsten Kriterien
| Kriterium | zählt Zellen, die … |
|---|---|
"offen" | genau «offen» enthalten (Gross-/Kleinschreibung egal) |
"<>erledigt" | nicht «erledigt» enthalten, auch leere Zellen |
">=10000" | eine Zahl grösser oder gleich 10'000 enthalten |
">="&E1 | grösser oder gleich dem Wert in E1 sind |
E2 | gleich dem Inhalt von E2 sind |
"*AG" | auf «AG» enden |
"<>" | nicht leer sind |
"" | leer sind |
Beispiel 1: Pendenzen nach Status und Person (Büro)
Im Team führt ihr eine Pendenzenliste. Du willst wissen, wie viele Pendenzen offen sind und wie viele jede Person insgesamt hat.
| Zeile | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Pendenz | Verantwortlich | Status | Person | Anzahl | |
| 2 | P-01 | Gerber | offen | Gerber | 3 | |
| 3 | P-02 | Steiner | erledigt | Steiner | 2 | |
| 4 | P-03 | Gerber | in Arbeit | Weber | 2 | |
| 5 | P-04 | Weber | offen | |||
| 6 | P-05 | Steiner | offen | |||
| 7 | P-06 | Gerber | erledigt | |||
| 8 | P-07 | Weber | erledigt |
Anzahl offener Pendenzen:
=ZÄHLENWENN(C2:C8;"offen")
Ergebnis: 3. «in Arbeit» zählt nicht, weil der Text nicht genau «offen» lautet. Willst du alles, was noch nicht erledigt ist, nimmst du das Ungleich-Kriterium: =ZÄHLENWENN(C2:C8;"<>erledigt") ergibt 4. Achtung: Dieses Kriterium zählt auch leere Zellen, falls der Bereich über die Liste hinausgeht.
Für die Auswertung pro Person steht der Name in E2 bis E4, und die Formel in F2 wird nach unten kopiert:
=ZÄHLENWENN($B$2:$B$8;E2)
Der Bereich ist mit $ fixiert, das Kriterium E2 wandert beim Kopieren mit. Gerber hat 3 Pendenzen, Steiner und Weber je 2.
Willst du die offenen Pendenzen pro Person, kombinierst du zwei Bedingungen mit ZÄHLENWENNS:
=ZÄHLENWENNS($B$2:$B$8;E2;$C$2:$C$8;"offen")
Für Gerber ergibt das 1. ZÄHLENWENNS nimmt beliebig viele Paare aus Bereich und Kriterium und zählt nur die Zeilen, in denen alle Bedingungen gleichzeitig zutreffen.
Beispiel 2: Grossschäden zählen und Anteil berechnen (Versicherung)
In der Schadenstatistik willst du wissen, wie viele Fälle die Grenze von CHF 10'000 erreichen und welchen Anteil sie an allen Fällen haben.
| Zeile | A | B |
|---|---|---|
| 1 | Schaden-Nr. | Betrag CHF |
| 2 | S-401 | 3'400 |
| 3 | S-402 | 18'750 |
| 4 | S-403 | 9'990 |
| 5 | S-404 | 42'300 |
| 6 | S-405 | 1'150 |
| 7 | S-406 | 12'000 |
| 8 | S-407 | 10'000 |
=ZÄHLENWENN(B2:B8;">=10000")
Ergebnis: 4. Mit ">10000" wären es nur 3, weil S-407 mit genau 10'000 dann nicht mehr dazugehört. Ob die Grenze dazugehört, ist eine fachliche Frage, die du klären solltest, bevor du die Formel baust. In Formeln schreibst du Zahlen übrigens ohne Hochkomma: 10000, nicht 10'000.
Den Anteil der Grossschäden an allen Fällen berechnest du, indem du durch die Anzahl aller Beträge teilst:
=ZÄHLENWENN(B2:B8;">=10000")/ANZAHL(B2:B8)
Ergebnis: 4 von 7, also rund 57.1 %, wenn du die Zelle als Prozent formatierst. ANZAHL zählt alle Zellen mit Zahlen und ist hier der richtige Nenner.
Für Bandbreiten, etwa alle Fälle zwischen CHF 5'000 und CHF 10'000, setzt du zwei Bedingungen auf denselben Bereich: =ZÄHLENWENNS(B2:B8;">=5000";B2:B8;"<10000"). Das ergibt 1 (S-403).
Beispiel 3: Doppelte Kundennummern finden (Finance)
Vor einem Import in die Buchhaltung oder einem Serienbrief willst du sicher sein, dass jede Kundennummer nur einmal vorkommt. ZÄHLENWENN zählt für jede Zeile, wie oft die Nummer in der ganzen Liste steht.
| Zeile | A | B | C |
|---|---|---|---|
| 1 | Kunden-Nr. | Vorkommen | Prüfung |
| 2 | K-1001 | 1 | |
| 3 | K-1002 | 2 | Duplikat |
| 4 | K-1003 | 2 | Duplikat |
| 5 | K-1002 | 2 | Duplikat |
| 6 | K-1004 | 1 | |
| 7 | K-1005 | 1 | |
| 8 | K-1003 | 2 | Duplikat |
| 9 | K-1006 | 1 |
Die Formel in B2, nach unten kopiert:
=ZÄHLENWENN($A$2:$A$9;A2)
Jeder Wert grösser als 1 ist ein Duplikat. Mit WENN machst du daraus einen lesbaren Hinweis in Spalte C:
=WENN(ZÄHLENWENN($A$2:$A$9;A2)>1;"Duplikat";"")
Hier sind die $-Zeichen entscheidend. Ohne sie würde der Bereich beim Kopieren mit nach unten wandern, und spätere Zeilen würden ihre Duplikate weiter oben nicht mehr sehen.
Typische Fehler und wie du sie erkennst
#NAME?
=ZÄHLENWENN(C2:C8;offen) liefert #NAME?, weil Excel «offen» ohne Anführungszeichen für einen Bereichsnamen hält. Richtig ist "offen". Auch COUNTIF in der deutschen Excel-Version oder ein Tippfehler wie ZAEHLENWENN führen zu #NAME?.
Excel nimmt die Formel nicht an
Ein Operator ohne Anführungszeichen (=ZÄHLENWENN(B2:B8;>=10000)) oder Kommas statt Semikolons (=ZÄHLENWENN(C2:C8,"offen")) führen dazu, dass Excel beim Bestätigen ein Problem mit der Formel meldet.
Ergebnis 0 oder zu tief
- Zellbezug in Anführungszeichen:
">=E1"vergleicht mit dem Text «E1». Richtig:">="&E1. - Leerzeichen: «offen » mit Leerzeichen am Ende wird von
"offen"nicht erfasst. Bereinige die Daten mit GLÄTTEN oder verwende vorübergehend"offen*", um das Problem zu bestätigen. - Zahlen als Text: Ein Vergleich wie
">=10000"berücksichtigt nur echte Zahlen. Beträge, die als Text gespeichert sind, fallen heraus. - Platzhalter im Text: Suchst du nach einem echten Sternchen oder Fragezeichen, setzt du eine Tilde davor:
"~*".
Fehlende $
Wie im Duplikat-Beispiel gezeigt: Ohne absoluten Bezug verschiebt sich der Bereich beim Kopieren. Die Formel liefert dann keine Fehlermeldung, sondern stillschweigend falsche Zahlen. Mehr dazu im Artikel über absolute Bezüge.
Englisch vs. Deutsch
Aus =COUNTIF(C2:C8,"open") wird in der Schweizer Excel-Version =ZÄHLENWENN(C2:C8;"offen"). Beachte den Umlaut im deutschen Namen: ZÄHLENWENN, nicht ZAHLENWENN.
| Deutsch | Englisch | zählt … |
|---|---|---|
| ZÄHLENWENN | COUNTIF | Zellen, die ein Kriterium erfüllen |
| ZÄHLENWENNS | COUNTIFS | Zellen, die mehrere Kriterien gleichzeitig erfüllen |
| ANZAHL | COUNT | Zellen mit Zahlen |
| ANZAHL2 | COUNTA | nicht leere Zellen |
| ANZAHLLEEREZELLEN | COUNTBLANK | leere Zellen |
| SUMMEWENN | SUMIF | summiert statt zählt |
| IDENTISCH | EXACT | vergleicht Text mit Gross-/Kleinschreibung |
Häufige Fragen
Was ist der Unterschied zwischen ZÄHLENWENN und ANZAHL?
ANZAHL zählt alle Zellen, die eine Zahl enthalten, ohne Bedingung. ZÄHLENWENN zählt nur die Zellen, die ein Kriterium erfüllen, und funktioniert auch mit Text. Für alle nicht leeren Zellen, egal ob Text oder Zahl, gibt es ANZAHL2.
Wie zähle ich mit ZÄHLENWENN alle nicht leeren Zellen?
Mit dem Kriterium "<>", also =ZÄHLENWENN(C2:C8;"<>"). Für leere Zellen nimmst du "" oder die Funktion ANZAHLLEEREZELLEN.
Unterscheidet ZÄHLENWENN zwischen Gross- und Kleinschreibung?
Nein, "offen" und "Offen" zählen gleich. Brauchst du eine exakte Unterscheidung, kombinierst du SUMMENPRODUKT mit IDENTISCH: =SUMMENPRODUKT(--IDENTISCH(C2:C8;"Offen")).
Wie zähle ich Werte zwischen zwei Grenzen?
Mit ZÄHLENWENNS und zwei Bedingungen auf denselben Bereich, zum Beispiel =ZÄHLENWENNS(B2:B8;">=5000";B2:B8;"<10000") für alle Beträge ab 5'000 und unter 10'000.