Rob führt Statistiken über jeden Job, den er jeden Tag erledigt. Zum Beispiel verfolgt er die genaue Anzahl der Stunden und Minuten, die für jeden Job aufgewendet wurden, indem er eine Startzeit (Spalte B) und eine Endzeit (Spalte C) für jeden Job im Format 05/11/20 11:25 Uhr eingibt. In einer separaten Spalte (Spalte G) hat er das Datum für jeden Tag des Monats. Rechts von jedem Datum (in Spalte H) möchte er anzeigen, wie viele Aufträge er an jedem der calendar -Daten ausgeführt hat. Rob fragt sich, mit welcher Formel er diese Zählung herausfinden würde.

Diese Aufgabe ist nicht so einfach, wie es zunächst scheinen mag. Der Grund liegt in der Art und Weise, wie die Start- und Endzeiten gespeichert werden.

Spalte B enthält sowohl das Startdatum als auch die Startzeit in derselben Spalte im Format „05/11/20 11:25 am“ und Spalte C enthält sowohl das Enddatum als auch die Endzeit im selben Format. Sie könnten denken, Sie können die COUNTIF-Funktion in Spalte G folgendermaßen verwenden:

=COUNTIF(C$2:C$1000,G2)

Dies wird jedoch nicht funktionieren. Der Grund ist einfach: Spalte G (in diesem Fall Zelle G2) enthält ein Datum ohne Uhrzeit. Beispielsweise könnte es das Datum 05/11/20 enthalten. Wenn dies mit Zelle C2 verglichen wird, die möglicherweise den 11.05.20, 11:25 Uhr enthält, sind sie nicht gleich. Denken Sie daran, dass Excel den Vergleich basierend auf der in jeder Zelle gespeicherten Seriennummer von Datum und Uhrzeit durchführt. Bei den gerade erwähnten Beispielwerten würde Zelle G2 eine Seriennummer von 39217 und Zelle C2 eine Seriennummer von 39217,47569 enthalten. Da diese beiden Werte nicht identisch sind, zählt COUNTIF sie nicht als gleich.

Eine Lösung wäre natürlich, eine weitere Spalte hinzuzufügen, die nur die Enddaten für jeden Job ohne Zeitangabe enthält. Dann könnten Sie die COUNTIF-Funktion in Ihrer Formel verwenden, da Sie sozusagen „Äpfel mit Äpfeln“ vergleichen würden. Es gibt jedoch eine Lösung, bei der kein Zwischenergebnis in einer neuen Spalte verwendet werden muss. Dieser Ansatz verwendet die COUNTIFS-Funktion, die zwei Kriterien auf alle Zellen in einem Bereich anwendet:

=COUNTIFS(C$2:C$1000,">=" & G2,C$2:C$1000,"<" & (G2+1))

Diese Formel verwendet zwei Kriterien, um effektiv zu sehen, ob das, was sich in den Zellen in Spalte C befindet, größer oder gleich dem Wert in G2 ist, jedoch kleiner als G2 + 1. Mit anderen Worten, es zählt alles, was während des in G2 angegebenen Tages auftritt.

Ein anderer Ansatz besteht darin, sicherzustellen, dass der ganzzahlige Wert von allem, was in Spalte C steht, mit den in Spalte G gespeicherten Daten verglichen wird. Dies kann mithilfe der Funktion SUMPRODUCT auf folgende Weise erfolgen:

=SUMPRODUCT((INT(C$2:C$1000)=G2)*1)

Die Ganzzahl jedes der Werte im Bereich C2: C1000 wird mit dem Datum in G2 verglichen, wodurch ein Array von Wahr- und Falschwerten erhalten wird. Wenn Sie diese Werte jeweils mit 1 multiplizieren, werden die Werte True und False zu 1 bzw. 0. Die Formel summiert dann diese Produkte und gibt die gewünschte Anzahl an.

Wenn Sie möchten, können Sie auch die folgende Formel verwenden, die mit SUMPRODUCT im Wesentlichen dieselbe Aufgabe ausführt:

=SUMPRODUCT(--(INT(C$2:C$1000)=G2))

Einer der Vorteile der Verwendung der COUNTIFS-Funktion oder der SUMPRODUCT-Funktion zur Ermittlung eines Ergebnisses besteht darin, dass Sie keine Array-Formel verwenden müssen. Einige Leute bevorzugen jedoch die Verwendung von Array-Formeln. Wenn Sie sie verwenden möchten, können Sie eine der folgenden Optionen verwenden:

=COUNT(IF(INT(C$2:C$1000)=INT(G2),1,FALSE))

=SUM(IF(ROUNDDOWN(C$2:C$1000,0)=G2,1,0))

Denken Sie daran, dass Array-Formeln mit Strg + Umschalt + Eingabetaste in eine Zelle eingegeben werden müssen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (10422) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Zählen von Jobs, die an einem Datum abgeschlossen wurden.