Generisk formel
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Resumé
For at opsummere det samlede tidsrum over 30 minutter, givet et sæt tidspunkter, der repræsenterer varighed, kan du bruge SUMPRODUCT- og TID-funktionerne. I det viste eksempel er formlen i G5:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
hvor "tider" er det navngivne område C5: C14.
Forklaring
Denne formel bruger SUMPRODUCT-funktionen til at opsummere resultatet af to udtryk, der giver arrays. Målet er kun at opsummere tid større end 30 minutter, "overskud" eller "ekstra" tid. Det første udtryk trækker 30 minutter fra hver gang i det navngivne interval "gange":
times-TIME(0,30,0)
Dette resulterer i en matrix som denne:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Det andet udtryk er en logisk test, der er større end 30 minutter:
times>TIME(0,30,0)
Dette skaber en matrix med SANDE FALSKE værdier:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Inde i SUMPRODUCT ganges disse to arrays sammen for at skabe dette array:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Bemærk negative værdier i det første array er nu nuller. Under multiplikation konverteres de SANDE FALSE-værdier til 1 og nul, så FALSE-værdier "annullerer" tider, der ikke er større end 30 min. Endelig returnerer SUMPRODUCT summen af alle værdier i arrayet, 1 time og 4 minutter (1:04).
Alternativ med SUMIFS og COUNTIFS
I sig selv kan SUMIFS ikke sammenfatte deltaet af tidsværdier, der er større end 30 minutter. SUMIFS og COUNTIFS kan bruges sammen for at få det samme resultat som SUMPRODUCT ovenfor:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Gange over 24 timer
Hvis de samlede tider kan overstige 24 timer, skal du bruge dette til et tilpasset tidsformat som dette:
(h):mm:ss
Syntaksen med firkantede parenteser fortæller Excel, at den ikke skal "vælte" gange mere end 24 timer.
Med en hjælpekolonne
Som vist i eksemplet kan du også tilføje en hjælpekolonne til at beregne og summere tidsdeltaer. Formlen i D5, kopieret ned, er:
=MAX(C5-"00:30",0)
Her bruges MAX til at slippe af med negative tidsdeltaer forårsaget af tidspunkter i kolonne C, der er mindre end 30 minutter. Bemærk, at resultatet i D15 er det samme som resultatet i G5.
