Generička formula
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Sažetak
Da biste zbrojili ukupnu količinu vremena tijekom 30 minuta, s obzirom na skup vremena koja predstavljaju trajanje, možete koristiti funkcije SUMPRODUCT i TIME. U prikazanom primjeru formula u G5 je:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
gdje je "puta" imenovani raspon C5: C14.
Obrazloženje
Ova formula koristi funkciju SUMPRODUCT za zbrajanje rezultata dva izraza koji daju nizove. Cilj je zbrojiti samo vrijeme veće od 30 minuta, "višak" ili "dodatno" vrijeme. Prvi izraz oduzima 30 minuta od svakog vremena u imenovanom rasponu "puta":
times-TIME(0,30,0)
Rezultat je ovakav niz:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Drugi izraz je logičan test za sva vremena veća od 30 minuta:
times>TIME(0,30,0)
Ovo stvara niz TRUE FALSE vrijednosti:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Unutar SUMPRODUCT-a, ova dva polja množe se zajedno da bi se stvorio ovaj niz:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Primijetite da su negativne vrijednosti u prvom polju sada nule. Tijekom množenja vrijednosti TRUE FALSE pretvaraju se u 1 i nulu, pa se FALSE vrijednosti "poništavaju" vremena koja nisu veća od 30 min. Napokon, SUMPRODUCT vraća zbroj svih vrijednosti u polju, 1 sat i 4 minute (1:04).
Alternativa sa SUMIFS i COUNTIFS
Sama po sebi SUMIFS ne može zbrajati deltu vremenskih vrijednosti veću od 30 minuta. SUMIFS i COUNTIFS mogu se koristiti zajedno da bi se dobio isti rezultat kao gore navedeni SUMPRODUCT:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Puta preko 24 sata
Ako ukupno vrijeme može premašiti 24 sata, upotrijebite ovaj prilagođeni format vremena poput ovog:
(h):mm:ss
Sintaksa kvadratnih zagrada govori Excelu da se ne "prevrće" puta dulje od 24 sata.
S pomoćnom kolonom
Kao što je prikazano u primjeru, također možete dodati pomoćni stupac za izračunavanje i zbrajanje delta vremena. Kopirana formula u D5 je:
=MAX(C5-"00:30",0)
Ovdje se MAX koristi za uklanjanje negativnih vremenskih delta, uzrokovanih vremenima u stupcu C manjim od 30 minuta. Primijetite da je rezultat u D15 jednak rezultatu u G5.
