Splošna formula
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Povzetek
Za seštevanje skupnega časa v 30 minutah, glede na niz časov, ki predstavljajo trajanje, lahko uporabite funkciji SUMPRODUCT in TIME. V prikazanem primeru je formula v G5:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
kjer je "krat" imenovano območje C5: C14.
Pojasnilo
Ta formula uporablja funkcijo SUMPRODUCT za seštevanje rezultata dveh izrazov, ki dajeta polja. Cilj je vsota le časa, daljšega od 30 minut, "presežka" ali "dodatnega" časa. Prvi izraz odšteje 30 minut od vsakega časa v imenovanem obsegu "times":
times-TIME(0,30,0)
Rezultat je taka matrika:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Drugi izraz je logični test za vse, daljše od 30 minut:
times>TIME(0,30,0)
To ustvari niz TRUE FALSE vrednosti:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Znotraj SUMPRODUCT se ta dva polja pomnožijo skupaj, da ustvarijo to matriko:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Upoštevajte, da so negativne vrednosti v prvem polju zdaj ničle. Med množenjem se vrednosti TRUE FALSE pretvorijo v 1 in nič, zato so vrednosti FALSE "preklicane", ki niso daljše od 30 minut. Končno, SUMPRODUCT vrne vsoto vseh vrednosti v matriki, 1 uro in 4 minute (1:04).
Alternativa s SUMIFS in COUNTIFS
SUMIFS sam po sebi ne more sešteti delt časovnih vrednosti, večjih od 30 minut. SUMIFS in COUNTIFS lahko uporabimo skupaj, da dobimo enak rezultat kot zgoraj SUMPRODUCT:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Časi nad 24 ur
Če lahko skupni časi presežejo 24 ur, uporabite ta čas po meri, kot je ta:
(h):mm:ss
Sintaksa v oglatih oklepajih navaja Excelu, naj se ne prevrne več kot 24 ur.
S pomožnim stolpcem
Kot je prikazano v primeru, lahko dodate tudi pomožni stolpec za izračun in seštevanje časovnih delt. Kopirana formula v D5 je:
=MAX(C5-"00:30",0)
Tu se MAX uporablja za odpravo negativnih časovnih delt, ki jih povzročijo časi v stolpcu C manjši od 30 minut. Upoštevajte, da je rezultat v D15 enak rezultatu v G5.
