ZÄHLENWENN in Excel: Schichten, Urlaubstage & Kürzel im Dienstplan zählen

Was macht ZÄHLENWENN?

Microsoft ordnet ZÄHLENWENN den statistischen Funktionen zu: Sie prüft jede Zelle eines Bereichs gegen eine Bedingung und gibt zurück, wie viele Zellen sie erfüllen. Das Ergebnis ist immer eine ganze Zahl – nie eine Summe der Zellinhalte. Genau das passt zum Dienstplan, denn die Planfläche besteht aus Kürzeln wie F, S, N, U und K, nicht aus Zahlen. Wer wissen will, wie oft ein Kürzel vorkommt, zählt.

Im englischsprachigen Excel heißt die Funktion COUNTIF, die Variante mit mehreren Bedingungen COUNTIFS. Excel übersetzt die Namen beim Öffnen einer Datei automatisch in die Sprache der Installation; eine Formel aus einer englischen Vorlage funktioniert also auch in einer deutschen Excel-Version. Beim Abtippen aus englischen Anleitungen sind dagegen zwei Dinge anzupassen: der Funktionsname und das Trennzeichen – deutsches Excel trennt Argumente mit Semikolon statt mit Komma.

Syntax von ZÄHLENWENN und ZÄHLENWENNS

ZÄHLENWENN hat genau zwei Pflichtargumente, ZÄHLENWENNS beliebig viele Paare aus Bereich und Kriterium.

Zwei Regeln aus der Microsoft-Dokumentation zu ZÄHLENWENNS sind für Dienstpläne besonders wichtig. Erstens muss jeder weitere Bereich dieselbe Anzahl von Zeilen und Spalten haben wie der erste; die Bereiche müssen aber nicht nebeneinander liegen. Eine Monatszeile C5:AG5 lässt sich also mit einer Datumszeile C3:AG3 kombinieren, nicht aber mit C3:AF3. Zweitens zählt eine Position nur dann, wenn alle Kriterien an derselben Stelle erfüllt sind – die Bedingungen wirken wie ein logisches UND. Ein ODER („Frühdienst oder Spätdienst“) lässt sich mit ZÄHLENWENNS nicht in einer Bedingung ausdrücken; dafür werden zwei Zählungen addiert.

Ein drittes Detail fällt seltener auf: Verweist ein Kriterium auf eine leere Zelle, behandelt ZÄHLENWENNS sie laut Microsoft als Nullwert. Eine Formel, die das Kürzel aus einer noch leeren Auswahlzelle holt, zählt dann nicht „nichts“, sondern Nullen.

Kriterien richtig schreiben

Das Suchkriterium entscheidet darüber, ob die Zählung stimmt. Microsoft nennt als häufigen Grund für ein ausbleibendes Ergebnis, dass Kriterien nicht in Anführungszeichen stehen. Text und alle Ausdrücke mit Vergleichsoperatoren gehören in doppelte Anführungszeichen; reine Zahlen und Zellbezüge nicht.

Text, Zahlen und Vergleichsoperatoren

Die Variante "<>U" ist eine kleine Falle: Sie zählt auch leere Tage mit. Wer „eingeplant, aber nicht im Urlaub“ meint, braucht zwei Bedingungen – =ZÄHLENWENNS(C5:AG5;"<>";C5:AG5;"<>U"). Derselbe Bereich darf in ZÄHLENWENNS mehrfach vorkommen.

Zellbezüge im Kriterium

Ein Kriterium kann direkt auf eine Zelle zeigen: =ZÄHLENWENN($C5:$AG5;AI$3) zählt das Kürzel, das in der Kopfzelle AI3 steht. Das ist die übliche Bauweise für eine Auswertungstabelle rechts neben dem Plan – in AI3 bis AM3 stehen F, S, N, U und K, und eine einzige Formel wird über alle Spalten und Zeilen kopiert. Die Dollarzeichen sorgen dafür, dass der Planbereich beim Kopieren nach rechts stehen bleibt und die Kopfzeile beim Kopieren nach unten.

Soll ein Vergleichsoperator mit einem Zellinhalt kombiniert werden, verbindet der &-Operator beides zu einem Text. Microsoft zeigt das am Beispiel "<>"&B4, das zu "<>75" wird. Im Dienstplan sieht das so aus: Steht in $B$1 eine Grenze von 10 Stunden, zählt =ZÄHLENWENN(C20:AG20;">"&$B$1) die Tage darüber. Der häufigste Fehler ist die Schreibweise ">$B$1" – dann sucht Excel nach dem Text „$B$1“ und findet nichts.

Platzhalter * und ?

Das Sternchen steht für eine beliebige Zeichenfolge, das Fragezeichen für genau ein Zeichen. Im Plan sind Platzhalter nützlich, wenn Kürzel Varianten haben:

• =ZÄHLENWENN(C5:AG5;"N*") zählt N, N1 und N-Ber. gemeinsam, also alle Nachtdienstvarianten.
• =ZÄHLENWENN(C5:AG5;"F?") zählt F1 und F2, aber weder F allein noch F10.
• =ZÄHLENWENN(C5:AG5;"*Ber*") zählt alle Zellen, in denen „Ber“ für Bereitschaft vorkommt, an beliebiger Stelle.

Platzhalter zählen großzügig. "U*" trifft U und U/2, aber auch „UB“ für eine unbezahlte Freistellung, falls der Betrieb so ein Kürzel führt. Bevor ein Platzhalter in die Auswertung geht, lohnt ein Blick auf die vollständige Kürzelliste. Soll tatsächlich nach einem Sternchen oder Fragezeichen gesucht werden, gehört laut Microsoft eine Tilde davor: "~*".

Groß- und Kleinschreibung

ZÄHLENWENN unterscheidet nicht zwischen Groß- und Kleinschreibung. Das ist meistens gewollt: Ein versehentlich kleingeschriebenes „f“ zählt als Frühdienst. Nutzt ein Plan aber bewusst „F“ für einen vollen und „f“ für einen verkürzten Frühdienst, reicht ZÄHLENWENN nicht. Dann hilft die Funktion IDENTISCH, die Groß- und Kleinschreibung beachtet, in Kombination mit SUMMENPRODUKT: =SUMMENPRODUKT(--IDENTISCH(C5:AG5;"f")). Einfacher und robuster ist es allerdings, solche Paare gar nicht erst anzulegen und für Varianten eindeutige Kürzel wie „FK“ zu vergeben.

Vier Beispiele aus dem Dienstplan

Die folgenden Formeln beziehen sich auf einen Monatsplan mit diesem Aufbau: In Zeile 3 stehen die Datumswerte des Monats in C3:AG3, darunter in Zeile 4 eine Hilfszeile mit dem Wochentag als Zahl, in den Zeilen 5 bis 14 die Mitarbeitenden mit ihren Kürzeln. Die Kürzel kommen idealerweise aus einer Dropdown-Liste, damit keine Schreibvarianten entstehen.

1. Frühdienste je Mitarbeiter

Die Grundform: =ZÄHLENWENN($C5:$AG5;"F") in AI5, nach unten kopiert, liefert die Frühdienste jeder Person im Monat. Mit einer Kopfzeile aus Kürzeln wird daraus eine vollständige Übersicht:

Die Zahlen sind Beispielwerte. „Dienste gesamt“ summiert bewusst nur F, S und N – Urlaub und Arbeitsunfähigkeit sind keine Dienste. Wer Stunden statt Dienste braucht, zählt nicht, sondern schlägt die Nettostunden je Kürzel nach; das beschreibt SVERWEIS in Excel. Eine schnelle Näherung ist auch mit ZÄHLENWENN möglich, wenn alle Schichten gleich lang sind: =ZÄHLENWENN($C5:$AG5;"F")*7,5.

2. Wochenenddienste mit ZÄHLENWENNS und Datum

ZÄHLENWENN kann nur den Inhalt des geprüften Bereichs selbst bewerten. Ob ein Frühdienst auf einen Samstag fällt, steht aber nicht in der Planzelle, sondern im Datum darüber. Hier kommt ZÄHLENWENNS ins Spiel – zusammen mit einer Hilfszeile, denn ZÄHLENWENN und ZÄHLENWENNS erwarten Zellbereiche und können keine Funktion wie WOCHENTAG auf einen ganzen Bereich anwenden.

1. Hilfszeile anlegen. In C4 steht =WOCHENTAG(C$3;2), nach rechts kopiert bis AG4. Mit dem Typ 2 liefert WOCHENTAG für Montag die 1 und für Sonntag die 7; Samstag und Sonntag sind also alle Werte größer als 5. Die Zeile kann ausgeblendet werden. Wie WOCHENTAG und seine Rückgabetypen funktionieren, steht in Wochentag aus Datum anzeigen.
2. Wochenend-Frühdienste zählen. =ZÄHLENWENNS($C5:$AG5;"F";$C$4:$AG$4;">5")
3. Alle Wochenenddienste zählen. Weil ZÄHLENWENNS kein ODER kennt, werden die Schichtarten addiert: =ZÄHLENWENNS($C5:$AG5;"F";$C$4:$AG$4;">5")+ZÄHLENWENNS($C5:$AG5;"S";$C$4:$AG$4;">5")+ZÄHLENWENNS($C5:$AG5;"N";$C$4:$AG$4;">5")

Mit der Datumszeile selbst lassen sich Zeiträume eingrenzen. Stehen in $B$1 und $B$2 Beginn und Ende eines Abrechnungszeitraums, zählt =ZÄHLENWENNS($C5:$AG5;"N";$C$3:$AG$3;">="&$B$1;$C$3:$AG$3;"<="&$B$2) die Nachtdienste darin. Microsoft zeigt dieselbe Technik mit Datumskriterien wie "<"&B4. Datumswerte im Kriterium besser immer über eine Zelle oder DATUM(2026;11;1) angeben statt als Text – ein Datum wie „1.11.2026“ im Kriterium hängt von den Regionaleinstellungen ab.

Wer die Hilfszeile vermeiden will, kann auf SUMMENPRODUKT ausweichen: =SUMMENPRODUKT(($C5:$AG5="F")*(WOCHENTAG($C$3:$AG$3;2)>5)). Die Formel prüft beide Bedingungen direkt, ist aber schwerer zu lesen. Ein Kürzel mit angehängtem Leerzeichen wird auch hier nicht erkannt.

3. Besetzung je Tag prüfen

Dreht man die Blickrichtung, zählt ZÄHLENWENN die Besetzung einer Spalte. In Zeile 16 unter dem Plan steht =ZÄHLENWENN(C$5:C$14;"F"), nach rechts kopiert – das ergibt die Zahl der Frühdienste an jedem Tag. Zeile 17 und 18 zählen auf dieselbe Weise S und N.

Die Mindestbesetzung je Schicht muss jeder Betrieb selbst festlegen; der Wert 3 ist ein Beispiel. Sichtbar wird eine Unterbesetzung am schnellsten mit einer Regel der bedingten Formatierung auf Zeile 16, etwa =C16<C19. Wie solche Regeln mit gemischten Bezügen aufgebaut werden, zeigt Bedingte Formatierung in Excel. Dort wird ZÄHLENWENN auch für eine zweite Aufgabe verwendet: =ZÄHLENWENN(Feiertage;C$3)>0 prüft, ob das Datum einer Spalte in einer benannten Feiertagsliste steht.

4. Urlaubstage „U“ zählen

Die einfachste Form =ZÄHLENWENN($C5:$AG5;"U") zählt alle Urlaubskürzel einer Zeile. Für eine Übersicht „wer hatte wann frei“ reicht das. Für ein Urlaubskonto ist es zu grob: Urlaub wird nur für Tage verbraucht, an denen sonst gearbeitet worden wäre, und wer den Urlaub über ganze Wochen einträgt, markiert auch Wochenenden und Feiertage. Mit zwei Hilfszeilen kann ZÄHLENWENNS das abbilden:

• Zeile 4: Wochentag als Zahl wie oben, =WOCHENTAG(C$3;2).
• Zeile 2: Feiertagskennzeichen, =ZÄHLENWENN(Feiertage;C$3) – ergibt 1 an Feiertagen und 0 sonst.
• Urlaubstage an Arbeitstagen: =ZÄHLENWENNS($C5:$AG5;"U";$C$4:$AG$4;"<6";$C$2:$AG$2;0).

Die Formel setzt eine Fünftagewoche von Montag bis Freitag voraus. Für Teilzeitkräfte mit anderen Arbeitstagen oder für halbe Urlaubstage wird die Rechnung individueller; halbe Tage mit eigenem Kürzel lassen sich etwa als =ZÄHLENWENN($C5:$AG5;"U")+0,5*ZÄHLENWENN($C5:$AG5;"U/2") erfassen. Wie ein ganzes Urlaubsjahr mit Resturlaub und Kollisionsprüfung aufgebaut wird und warum SUMMENPRODUKT dort flexibler ist, zeigt Urlaubsplaner in Excel bauen.

Typische Fehler und ihre Ursachen

ZÄHLENWENN meldet selten einen Fehler. Das Problem ist das Gegenteil: Die Formel liefert eine plausible, aber zu kleine Zahl. Die folgenden Ursachen treten in Dienstplänen am häufigsten auf.

Leerzeichen: der häufigste Grund für falsche Summen

Ein Kürzel mit angehängtem Leerzeichen sieht im Plan genauso aus wie ohne – es entsteht beim Abtippen, beim Kopieren aus E-Mails oder aus einem anderen Programm. ZÄHLENWENN mit dem Kriterium "F" übergeht diese Zelle. Microsoft nennt führende und nachgestellte Leerzeichen ausdrücklich als Ursache unerwarteter Ergebnisse bei Textwerten. Aufspüren lässt sich das mit einer Kontrollformel: =ZÄHLENWENN(C5:AG14;"* ") zählt alle Zellen, die auf ein Leerzeichen enden. Ergibt sie mehr als 0, wird bereinigt. Dauerhaft verhindert nur eine Datenüberprüfung mit fester Kürzelliste, dass solche Varianten entstehen.

Zahlen, die als Text gespeichert sind

In einer Stundenzeile können Werte stehen, die wie Zahlen aussehen, für Excel aber Text sind – erkennbar oft an der linksbündigen Ausrichtung oder einem grünen Dreieck in der Zellecke. Kriterien mit Vergleichsoperatoren wie ">10" erfassen solche Zellen in der Praxis nicht zuverlässig. Bevor eine Auswertung über Stunden läuft, gehört deshalb der Datentyp geprüft; die Ursachen und die Rechnung mit Zeitwerten erklärt Arbeitsstunden in Excel korrekt summieren.

Die Grenze von 255 Zeichen

Für Kürzel spielt sie keine Rolle, für Bemerkungsfelder schon: Laut Microsoft liefert ZÄHLENWENN bei Zeichenfolgen mit mehr als 255 Zeichen falsche Ergebnisse. Die dokumentierte Abhilfe ist, das Kriterium aus Teilen zusammenzusetzen, etwa "erster Teil"&"zweiter Teil". In Dienstplänen ist der bessere Weg meist, lange Freitexte gar nicht zu zählen, sondern ihnen ein kurzes Kategoriekürzel in einer eigenen Spalte zu geben.

Abgrenzung: ZÄHLENWENN, ANZAHL2, ANZAHL und SUMMEWENN

Excel hat mehrere Funktionen, die auf den ersten Blick dasselbe tun. Im Dienstplan unterscheiden sie sich deutlich:

Die Unterscheidung zwischen ZÄHLENWENN und ANZAHL2 wird oft übersehen. Wenn der Plan an leeren Tagen eine Formel mit dem Ergebnis "" enthält, zählt ANZAHL2 diese Zellen mit – ein vermeintlich leerer Tag gilt dann als belegt. =ZÄHLENWENN(C5:AG5;"?*") zählt dagegen nur Zellen mit mindestens einem Zeichen Text und ist in solchen Plänen die verlässlichere Wahl für „eingetragene Tage“.

SUMMEWENN ist das Gegenstück, sobald nicht mehr Vorkommen, sondern Werte gefragt sind. Microsoft beschreibt die Syntax als SUMMEWENN(Bereich;Suchkriterien;[Summe_Bereich]). Liegt unter jeder Kürzelzeile eine Stundenzeile, summiert =SUMMEWENN(C5:AG5;"N";C6:AG6) die Nachtdienststunden einer Person. Für Zuschläge mit Zeitfenstern reicht das allerdings nicht, weil eine Schicht teils in, teils außerhalb des Fensters liegen kann – das zeigt Zuschläge in Excel berechnen.

ZÄHLENWENN in Google Sheets und Excel für das Web

In Excel für das Web und in Microsoft 365 funktionieren ZÄHLENWENN und ZÄHLENWENNS unverändert, ebenso in älteren Desktop-Versionen; die Funktionen gehören seit Langem zum Standardumfang. Google Sheets kennt ZÄHLENWENN mit derselben Logik und den Argumenten Bereich und Kriterium. Wird eine Excel-Datei mit Bezügen auf andere Arbeitsmappen in eine Cloud-Ablage verschoben, sollten diese Bezüge geprüft werden, denn Microsoft weist darauf hin, dass ZÄHLENWENN auf eine andere Arbeitsmappe nur zugreift, solange diese geöffnet ist. Für einen geteilten Plan ist es deshalb sicherer, alle Daten in einer Mappe zu halten – wie das organisiert wird, beschreibt Excel-Dienstplan im Team teilen.

Wo das Zählen an seine Grenze kommt

ZÄHLENWENN beantwortet „wie oft“, aber nicht „ob es zulässig ist“. Ob zwischen einem Spätdienst und dem folgenden Frühdienst elf Stunden Ruhezeit nach § 5 ArbZG liegen, ob jemand an zu vielen aufeinanderfolgenden Tagen eingeplant ist oder ob eine Vertretung die nötige Qualifikation hat, lässt sich nur mit zusätzlichen Prüfformeln über benachbarte Zellen nachbauen. Jede dieser Formeln hängt an festen Bereichen, Hilfszeilen und Kürzeln, die beim Kopieren eines Monatsblatts oder beim Einfügen einer Person mitgepflegt werden müssen. Für ein kleines Team mit stabilem Schichtmodell bleibt das gut beherrschbar. Wächst der Plan, wird die Pflege der Auswertung selbst zur Aufgabe – welche Signale dafür sprechen, beschreibt Wann sich der Umstieg auf eine App lohnt.

Weiterlesen

Die Eingabeseite, die saubere Kürzel überhaupt erst möglich macht, beschreibt Excel Dropdown erstellen. Wie aus Kürzeln Uhrzeiten und Stunden werden, zeigt SVERWEIS in Excel, und wie Zählergebnisse farbig sichtbar werden, Bedingte Formatierung in Excel. Eine Anwesenheitsliste mit ZÄHLENWENN-Auswertung je Kürzel steht in Anwesenheitsliste Vorlage in Excel. Fertige Arbeitsmappen gibt es in der Vorlagenübersicht.