News & Tipps zu Microsoft Excel

1. SmartTools Blogs
2. Excel
3. 9.12.2025

Beitrag aus SmartTools Excel Weekly

Komplexe ZÄHLENWENN-Formel vereinfachen

Excel 365 2024 2021 2019 2016 2013

FRAGE In einem Tabellenbereich mit Zeilen für die Tage eines Monats und einzelnen Spalten für unsere Mitarbeiterinnen und Mitarbeiter trage ich Kürzel für die geplanten Schichten ein. Die Schichtkürzel entsprechen bestimmten Arbeitszeiten: zum Beispiel "S1" für eine Schicht von 8:00 Uhr bis 13:15 Uhr mit 15 Minuten Pause, was einer Arbeitszeit von 5 Stunden entspricht. Die verschiedenen Schichtdetails habe ich im Bereich J10:M18 erfasst – mit den Kürzeln in Spalte J und den Stunden in Spalte M.

Nun möchte ich anhand des Schichtplans berechnen, wieviele Stunden für jeden Mitarbeiter verplant sind. Bislang ermittle ich dazu, wie oft jedes einzelne Schichtkürzel in der Mitarbeiterspalte vorkommt, und multipliziere das Ergebnis mit der Stundenzahl der jeweiligen Schicht. Das führt zu komplexen Formelkonstruktionen wie dieser:

Geht das nicht auch einfacher?

W. E. Bingel

ANTWORT Ihre Formelkonstruktionen lassen sich durchaus vereinfachen, indem Sie die in Excel eingebaute Array-Funktionalität nutzen. Damit sind Sie in der Lage, in einer einzigen Formel mehrere Zellinhalte auf einmal auszuwerten.

Das bedeutet in Ihrem Fall, dass Sie mit einer einzigen ZÄHLENWENN-Funktion alle Schichtarten im Spaltenbereich eines Mitarbeiters zählen und mit der jeweiligen Stundenanzahl multiplizieren können. Das Ergebnis ist ein Array aus den Mitarbeiterstunden jeder einzelnen Schicht. Um die Gesamtstunden zu erhalten, müssen Sie die Werte aus dem Array dann nur noch mit Hilfe einer SUMME-Funktion addieren.

In Excel 365 sowie Excel 2021 und neuer gelingt das mit einer normalen Formel. In älteren Excel-Versionen müssen Sie die Formel als Arrayformel (auch Matrixformel genannt) eingeben, indem Sie die Formeleingabe nicht einfach nur mit Return, sondern mit Strg + Umschalt + Return abschließen. In der Bearbeitungszeile umgibt Excel die Formel danach mit geschweiften Klammern.

Betrachten wir dazu Ihre Schichtplanung. Angenommen, Sie geben die geplanten Schichtkürzel eines Mitarbeiters im Bereich D10:D40 ein. Die Schichtdetails stehen im Bereich J10:M18, - mit den möglichen Schichtkürzeln im Bereich J10:J18 und den jeweils zugeordneten Stunden im Bereich M10:M18.

Die Gesamtstunden, die sich aus den geplanten Schichten des Mitarbeiters ergeben, berechnen Sie dann mit folgender Formel, die Sie beispielsweise in Zelle D6 eingeben können:

Beachten Sie, dass Sie die Formeleingabe in Excel 2019 und älter mit Strg + Umschalt + Return abschließen müssen.

Sie brauchen also nicht für jedes Schichtkürzel eine eigene ZÄHLENWENN-Funktion, sondern können mit einer einzigen Funktion alle Kürzel abdecken:

Gleiches gilt für die Multiplikation mit den jeweiligen Schichtstunden. Auch hier können Sie alle Werte in einer einzigen Rechenoperation zusammenfassen:

In der Formel verwenden wir eine Mischung aus relativen und absoluten Zellbezügen (mit den $-Zeichen). So lässt sich die Formel ohne weiteres in andere Mitarbeiterspalten kopieren, da der relative Bezug auf die Mitarbeiterschichten (D10:D40) automatisch angepasst wird und sich die absoluten Bezüge auf die Schichtdetails immer auf dieselben Bereiche beziehen.