Datumsangaben auswerten ist nicht immer leicht. In einem Datum stecken Jahr, Monat, Tag und auch noch ein Wochentag oder eine Kalenderwoche. Und alles kann interessant sein!
Um jetzt einmal zu zählen wie viele Einträge in ein Jahr oder einen Wochentag fallen gibt es mehrere Möglichkeiten:
- Daten von Hand zählen 😓😩
- Neue Spalten in der Tabelle anlegen, in denen die Werte vereinzeln und dann über
SUMMEWENN(🇺🇸🇬🇧SUMIF) zählen 🤪🤨😱 - Direkt und einfach zählen lassen über intelligente Verwendung von
SUMMENPRODUKT(🇺🇸🇬🇧SUMPRODUCT) 👍😎
Möglichkeit 1 ist nur akzeptabel für kleinste Datenmengen. Und spätestens beim Raussuchen von Kalenderwochen macht das keinen Spaß mehr. Möglichkeit 2 macht die Tabelle mit Daten unübersichtlich und ist nicht wirklich elegant. Also auf geht’s mit Möglichkeit 3 und Excel schwitzen lassen 👍
Noch kein aktuelles Excel oder Office-Lizenz? Einfach hier bei Amazon kaufen (Affiliate Link).
Hinweis: Numbers stellt die Funktion SUMMEWENN/SUMPRODUCT nicht zur Verfügung.
Hier direkt das Template herunterladen:
Im-Datum-zaehlen-Download.xlsx (89 Downloads)
Ausgangslage
In unserem Beispiel nutzen wir eine Geburtstagsliste
(Namen trennen in Spalten? 👉 Excel durfte zaubern):
Jetzt möchten wir wissen wie viele Personen…
- in einem bestimmten Jahr,
- in einem bestimmten Monat,
- an einem bestimmten Wochentag
geboren wurden. Und dazu noch bei wie vielen Personen alle drei Punkte zutreffen.
Als Ergänzung dann noch wie viele Personen an einem Tag im Monat und in einer Kalenderwoche ihren Geburtstag feiern.
Für jede dieser Fragen müssen aus einem Datum einzelne Werte herausgenommen (Tag, Monat, Jahr) oder ermittelt (Wochentag, Kalenderwoche) werden.
Zählen lassen mit Formeln 😃
Um Excel mit Formel-Ergebnissen zählen zu lassen wird die Funktion SUMMENPRODUKT (🇺🇸🇬🇧SUMPRODUCT) genutzt 🥳. Damit können wir etwas bauen wie:
=SUMMENPRODUKT((JAHR($E$11:$E$70)=C3)+0) (🇺🇸🇬🇧
=SUMPRODUCT((YEAR($E$11:$E$70)=C3)+0))Was passiert denn da? 🤔😱
Aus jeder Zelle im Bereich $E$11:$E$70 (da stehen die Geburtstage) wird das Jahr ermittelt. Und dann jeweils verglichen mit dem Wert in der Zelle C3. Wenn das gleich ist kommt jeweils 1 heraus und wenn nicht 0. Dann zählt Excel die ganzen 1 zusammen und hat die Anzahl. Das +0 am Ende ist technisch nötig (einfach mit kopieren), weil sonst immer 0 als Ergebnis kommt.
Alles einzeln gezählt ✔️
Die übrigen Formeln sehen dann alle recht ähnlich aus:
Zählen wie viele Personen in einem Monat geboren sind:
=SUMMENPRODUKT((MONAT($E$11:$E$70)=C4)+0)
🇺🇸🇬🇧=SUMPRODUCT((MONTH($E$11:$E$70)=C4)+0)
Zählen wie viele Personen an einem bestimmten Wochentag geboren sind:
=SUMMENPRODUKT((WOCHENTAG($E$11:$E$70;2)=C5)+0)
🇺🇸🇬🇧=SUMPRODUCT((WEEKDAY($E$11:$E$70;2)=C5)+0)
Formel zählt mit Montag = 1 bis Sonntag = 7 – das ist das ;2 in der Formel
Auf einem Tag in einem Monat fallen so viele Geburtstage:
=SUMMENPRODUKT((TAG($E$11:$E$70)=C7)+0)
🇺🇸🇬🇧 =SUMPRODUCT((DAY($E$11:$E$70)=C7)+0)
Und schließlich in eine bestimmte Kalenderwoche
=SUMMENPRODUKT((ISOKALENDERWOCHE($E$11:$E$70)=C8)+0)
🇺🇸🇬🇧=SUMPRODUCT((ISOWEEKNUM($E$11:$E$70)=C8)+0)
Kalenderwochen sind nicht überall auf der Welt gleich. In der Formel wird europäisch/deutsch und nicht US-amerikanisch gezählt über die Funktion ISOKALENDERWOCHE
Mehrere Bedingungen verknüpfen 🤩
Um mehrere Bedingungen zu verknüpfen, gehören die alle in eine Formel in dieser Form:
=SUMMENPRODUKT((JAHR($E$11:$E$70)=C3)+0;(MONAT($E$11:$E$70)=C4)+0;(WOCHENTAG($E$11:$E$70;2)=C5)+0)🇺🇸🇬🇧
=SUMPRODUCT((YEAR($E$11:$E$70)=C3)+0;(MONTH($E$11:$E$70)=C4)+0;(WEEKDAY($E$11:$E$70;2)=C5)+0)Da schaut Excel jetzt in jeder Zeile alle Bedingungen durch. Überall wo es passt kommt eine 1 heraus und wo nicht eine 0. Das wird für jede Zeile dann multipliziert, so dass immer nur 1 insgesamt rauskommt, wenn alle Bedingungen zutreffen. Am Ende addiert Excel dann über alle Zeilen das Ergebnis. 🤩
Zusammenfassung und Download Beispiel
Mit Hilfe von SUMMENPRODUKT können leicht auch Zählungen auf Datumsangaben mit Hilfe von Formeln durchgeführt werden. Dabei können auch mehrere Bedingungen verknüpft werden.
Keine Lust alles abzuschreiben? Hier gibt es die Datei im Download:
Im-Datum-zaehlen-Download.xlsx (89 Downloads)
Interesse an individueller Beratung oder Coaching in Excel? Hier entlang.
Noch keine Excel oder Office-Lizenz? Einfach hier bei Amazon kaufen (Affiliate Link)