Generisk formel
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Sammendrag
For å oppsummere den totale tiden over 30 minutter, gitt et sett med tider som representerer varighet, kan du bruke SUMPRODUCT og TIME-funksjonene. I eksemplet vist er formelen i G5:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
hvor "tider" er det navngitte området C5: C14.
Forklaring
Denne formelen bruker SUMPRODUCT-funksjonen for å summere resultatet av to uttrykk som gir matriser. Målet er å oppsummere bare tid som er større enn 30 minutter, "overskudd" eller "ekstra" tid. Det første uttrykket trekker fra 30 minutter fra hver gang i det nevnte området "ganger":
times-TIME(0,30,0)
Dette resulterer i en matrise som dette:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Det andre uttrykket er en logisk test for alle ganger mer enn 30 minutter:
times>TIME(0,30,0)
Dette skaper en rekke TRUE FALSE verdier:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Inne i SUMPRODUCT multipliseres disse to matriser sammen for å lage denne matrisen:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Legg merke til negative verdier i den første matrisen er nå nuller. Under multiplikasjon blir de SANNE FALSE-verdiene konvertert til 1 og null, så FALSE-verdier "avbryter" tider som ikke er større enn 30 min. Til slutt returnerer SUMPRODUCT summen av alle verdiene i matrisen, 1 time og 4 minutter (1:04).
Alternativ med SUMIFS og COUNTIFS
I seg selv kan SUMIFS ikke summere deltaet av tidsverdier som er større enn 30 minutter. SUMIFS og COUNTIFS kan brukes sammen for å få det samme resultatet som SUMPRODUCT ovenfor:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Ganger over 24 timer
Hvis totale tider kan overstige 24 timer, bruk dette et tilpasset tidsformat som dette:
(h):mm:ss
Syntaksen med firkantet parentes forteller Excel at den ikke skal "velte" ganger større enn 24 timer.
Med en hjelpesøyle
Som vist i eksemplet, kan du også legge til en hjelpekolonne for å beregne og summere tidsdeltaer. Formelen i D5, kopiert ned, er:
=MAX(C5-"00:30",0)
Her brukes MAX for å kvitte seg med negative tidsdeltaer, forårsaket av tider i kolonne C som er mindre enn 30 minutter. Legg merke til at resultatet i D15 er det samme som resultatet i G5.
