Fórmula genérica
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Resumen
Para sumar la cantidad total de tiempo en 30 minutos, dado un conjunto de tiempos que representan la duración, puede usar las funciones SUMPRODUCT y TIME. En el ejemplo que se muestra, la fórmula en G5 es:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
donde "tiempos" es el rango con nombre C5: C14.
Explicación
Esta fórmula usa la función SUMPRODUCTO para sumar el resultado de dos expresiones que producen matrices. El objetivo es sumar solo el tiempo superior a 30 minutos, el tiempo "sobrante" o "extra". La primera expresión resta 30 minutos de cada tiempo en el rango nombrado "tiempos":
times-TIME(0,30,0)
Esto da como resultado una matriz como esta:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
La segunda expresión es una prueba lógica para todos los tiempos superiores a 30 minutos:
times>TIME(0,30,0)
Esto crea una matriz de valores VERDADERO FALSO:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Dentro de SUMPRODUCT, estas dos matrices se multiplican juntas para crear esta matriz:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Observe que los valores negativos en la primera matriz ahora son ceros. Durante la multiplicación, los valores VERDADERO FALSO se convierten a 1 y cero, por lo que los valores FALSOS "cancelan" los tiempos que no superan los 30 min. Finalmente, SUMPRODUCT devuelve la suma de todos los valores de la matriz, 1 hora y 4 minutos (1:04).
Alternativa con SUMIFS y COUNTIFS
Por sí mismo, SUMIFS no puede sumar el delta de valores de tiempo superiores a 30 minutos. SUMIFS y COUNTIFS se pueden usar juntos para obtener el mismo resultado que SUMPRODUCT anterior:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Tiempos superiores a 24 horas
Si el tiempo total puede exceder las 24 horas, use este formato de tiempo personalizado como este:
(h):mm:ss
La sintaxis de los corchetes le dice a Excel que no "pase" veces más de 24 horas.
Con una columna de ayuda
Como se muestra en el ejemplo, también puede agregar una columna auxiliar para calcular y sumar deltas de tiempo. La fórmula en D5, copiada, es:
=MAX(C5-"00:30",0)
Aquí, MAX se usa para deshacerse de los deltas de tiempo negativos, causados por tiempos en la columna C que son menos de 30 minutos. Observe que el resultado en D15 es el mismo que el resultado en G5.
