Formula generica
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Sommario
Per sommare il tempo totale in 30 minuti, dato un insieme di tempi che rappresentano la durata, puoi usare le funzioni SUMPRODUCT e TIME. Nell'esempio mostrato, la formula in G5 è:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
dove "times" è l'intervallo denominato C5: C14.
Spiegazione
Questa formula utilizza la funzione SUMPRODUCT per sommare il risultato di due espressioni che producono matrici. L'obiettivo è sommare solo il tempo maggiore di 30 minuti, il tempo "surplus" o "extra". La prima espressione sottrae 30 minuti da ogni volta nell'intervallo denominato "volte":
times-TIME(0,30,0)
Ciò si traduce in una matrice come questa:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
La seconda espressione è un test logico per tutti i tempi maggiori di 30 minuti:
times>TIME(0,30,0)
Questo crea un array di valori TRUE FALSE:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
All'interno di SUMPRODUCT, questi due array vengono moltiplicati insieme per creare questo array:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Notare che i valori negativi nel primo array ora sono zeri. Durante la moltiplicazione, i valori TRUE FALSE vengono convertiti in 1 e zero, quindi i valori FALSE "annullano" i tempi che non sono maggiori di 30 min. Infine, SUMPRODUCT restituisce la somma di tutti i valori nella matrice, 1 ora e 4 minuti (1:04).
Alternativa con SUMIFS e COUNTIFS
Di per sé, SUMIFS non può sommare il delta di valori temporali maggiori di 30 minuti. SUMIFS e COUNTIFS possono essere usati insieme per ottenere lo stesso risultato di SUMPRODUCT sopra:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Tempi superiori a 24 ore
Se i tempi totali possono superare le 24 ore, utilizza un formato orario personalizzato come questo:
(h):mm:ss
La sintassi delle parentesi quadre indica a Excel di non "rollover" le volte superiori a 24 ore.
Con una colonna helper
Come mostrato nell'esempio, puoi anche aggiungere una colonna helper per calcolare e sommare i delta temporali. La formula in D5, copiata in basso, è:
=MAX(C5-"00:30",0)
Qui, MAX viene utilizzato per eliminare i delta temporali negativi, causati da tempi nella colonna C inferiori a 30 minuti. Notare che il risultato in D15 è lo stesso del risultato in G5.
