Общая формула
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Резюме
Чтобы суммировать общее количество времени более 30 минут, учитывая набор значений времени, представляющих продолжительность, вы можете использовать функции СУММПРОИЗВ и ВРЕМЯ. В показанном примере формула в G5:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
где «раз» - именованный диапазон C5: C14.
Объяснение
В этой формуле используется функция СУММПРОИЗВ для суммирования результата двух выражений, которые дают массивы. Цель состоит в том, чтобы суммировать только время, превышающее 30 минут, «избыточное» или «дополнительное» время. Первое выражение вычитает 30 минут из каждого времени в указанном диапазоне «раз»:
times-TIME(0,30,0)
В результате получается такой массив:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Второе выражение - это логическая проверка для всех времен, превышающих 30 минут:
times>TIME(0,30,0)
Это создает массив значений TRUE FALSE:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Внутри SUMPRODUCT эти два массива умножаются вместе, чтобы создать этот массив:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Обратите внимание, что отрицательные значения в первом массиве теперь нули. Во время умножения значения ИСТИНА ЛОЖЬ преобразуются в 1 и ноль, поэтому значения ЛОЖЬ "компенсируют" времена, не превышающие 30 мин. Наконец, СУММПРОИЗВ возвращает сумму всех значений в массиве за 1 час и 4 минуты (1:04).
Альтернатива с СУММЕСЛИМН и СЧЁТЕСЛИМН
Сама по себе СУММЕСЛИМН не может суммировать дельту значений времени, превышающую 30 минут. СУММЕСЛИМН и СЧЁТЕСЛИМН могут использоваться вместе для получения того же результата, что и СУММПРОИЗВ выше:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Время более 24 часов
Если общее время может превышать 24 часа, используйте этот настраиваемый формат времени, например:
(h):mm:ss
Синтаксис квадратных скобок указывает Excel не «перематывать» время более 24 часов.
Со вспомогательной колонкой
Как показано в примере, вы также можете добавить вспомогательный столбец для вычисления и суммирования временных дельт. Скопированная формула в D5:
=MAX(C5-"00:30",0)
Здесь MAX используется для избавления от отрицательной разницы во времени, вызванной временами в столбце C, которые меньше 30 минут. Обратите внимание, что результат в D15 совпадает с результатом в G5.
