Generisk formel
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Sammanfattning
För att summera den totala tiden över 30 minuter, givet en uppsättning tider som representerar varaktighet, kan du använda SUMPRODUKT- och TID-funktionerna. I exemplet som visas är formeln i G5:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
där "tider" är det namngivna intervallet C5: C14.
Förklaring
Denna formel använder funktionen SUMPRODUCT för att summera resultatet av två uttryck som ger matriser. Målet är att bara summera tid som är längre än 30 minuter, "överskott" eller "extra" tid. Det första uttrycket subtraherar 30 minuter från varje gång i det angivna intervallet "tider":
times-TIME(0,30,0)
Detta resulterar i en array som denna:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Det andra uttrycket är ett logiskt test för alla gånger längre än 30 minuter:
times>TIME(0,30,0)
Detta skapar en matris med SANT FALSKA värden:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Inuti SUMPRODUCT multipliceras dessa två matriser tillsammans för att skapa denna array:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Observera att negativa värden i den första matrisen nu är nollor. Under multiplikationen omvandlas de SANNA FALSKA värdena till 1 och noll, så FALSE-värden "avbryter" tider som inte är längre än 30 minuter. Slutligen returnerar SUMPRODUCT summan av alla värden i matrisen, 1 timme och 4 minuter (1:04).
Alternativ med SUMIFS och COUNTIFS
I sig kan SUMIFS inte summera delta av tidsvärden som är större än 30 minuter. SUMIFS och COUNTIFS kan användas tillsammans för att få samma resultat som SUMPRODUCT ovan:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Tider över 24 timmar
Om den totala tiden kan överstiga 24 timmar, använd detta ett anpassat tidsformat så här:
(h):mm:ss
Syntaxen med hakparentes säger till Excel att inte "rulla över" gånger mer än 24 timmar.
Med en hjälparpelare
Som visas i exemplet kan du också lägga till en hjälpkolumn för att beräkna och summera tidsdelta. Formeln i D5, kopierad, är:
=MAX(C5-"00:30",0)
Här används MAX för att bli av med negativa tidsdeltor, orsakade av tider i kolumn C som är mindre än 30 minuter. Lägg märke till att resultatet i D15 är detsamma som resultatet i G5.
