Generische Formel
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Zusammenfassung
Um die Gesamtzeit über 30 Minuten zu summieren, können Sie bei einer Reihe von Zeiten, die die Dauer darstellen, die Funktionen SUMPRODUCT und TIME verwenden. Im gezeigten Beispiel lautet die Formel in G5:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
Dabei ist "times" der benannte Bereich C5: C14.
Erläuterung
Diese Formel verwendet die SUMPRODUCT-Funktion, um das Ergebnis zweier Ausdrücke zu summieren, die Arrays ergeben. Das Ziel ist es, nur eine Zeit von mehr als 30 Minuten, die "Überschuss" - oder "zusätzliche" Zeit, zu summieren. Der erste Ausdruck subtrahiert 30 Minuten von jedem Zeitpunkt im angegebenen Bereich "Zeiten":
times-TIME(0,30,0)
Dies führt zu einem Array wie dem folgenden:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Der zweite Ausdruck ist ein logischer Test für alle Zeiten, die länger als 30 Minuten dauern:
times>TIME(0,30,0)
Dadurch wird ein Array von TRUE FALSE-Werten erstellt:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
In SUMPRODUCT werden diese beiden Arrays miteinander multipliziert, um dieses Array zu erstellen:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Beachten Sie, dass negative Werte im ersten Array jetzt Nullen sind. Während der Multiplikation werden die TRUE FALSE-Werte in 1 und Null konvertiert, sodass FALSE-Werte Zeiten "aufheben", die nicht länger als 30 Minuten sind. Schließlich gibt SUMPRODUCT die Summe aller Werte im Array zurück, 1 Stunde und 4 Minuten (1:04).
Alternative mit SUMIFS und COUNTIFS
SUMIFS kann das Delta der Zeitwerte von mehr als 30 Minuten nicht summieren. SUMIFS und COUNTIFS können zusammen verwendet werden, um das gleiche Ergebnis wie oben zu erzielen: SUMPRODUCT:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Mal über 24 Stunden
Wenn die Gesamtzeit 24 Stunden überschreiten kann, verwenden Sie ein benutzerdefiniertes Zeitformat wie das folgende:
(h):mm:ss
Die Syntax in eckigen Klammern weist Excel an, nicht länger als 24 Stunden zu "rollen".
Mit einer Hilfssäule
Wie im Beispiel gezeigt, können Sie auch eine Hilfsspalte hinzufügen, um Zeitdeltas zu berechnen und zu summieren. Die kopierte Formel in D5 lautet:
=MAX(C5-"00:30",0)
Hier wird MAX verwendet, um negative Zeitdeltas zu beseitigen, die durch Zeiten in Spalte C verursacht werden, die weniger als 30 Minuten betragen. Beachten Sie, dass das Ergebnis in D15 mit dem Ergebnis in G5 übereinstimmt.
