Генеричка формула
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Резиме
Да бисте сумирали укупну количину времена током 30 минута, с обзиром на скуп времена која представљају трајање, можете користити функције СУМПРОДУЦТ и ТИМЕ. У приказаном примеру, формула у Г5 је:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
при чему је „пута“ именовани опсег Ц5: Ц14.
Објашњење
Ова формула користи функцију СУМПРОДУЦТ за сумирање резултата два израза који дају низове. Циљ је сумирање само времена дужег од 30 минута, „вишка“ или „додатног“ времена. Први израз одузима 30 минута од сваког времена у именованом опсегу "пута":
times-TIME(0,30,0)
Резултат је овакав низ:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Други израз је логичан тест за сва времена дужа од 30 минута:
times>TIME(0,30,0)
Ово ствара низ ТРУЕ ФАЛСЕ вредности:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Унутар СУМПРОДУЦТ-а, ова два низа множе се заједно да би се створио овај низ:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Приметите да су негативне вредности у првом низу сада нуле. Током множења, вредности ТРУЕ ФАЛСЕ се претварају у 1 и нулу, па се ФАЛСЕ вредности „поништавају“ пута која нису већа од 30 мин. Коначно, СУМПРОДУЦТ враћа збир свих вредности у низу, 1 сат и 4 минута (1:04).
Алтернатива са СУМИФС и ЦОУНТИФС
Сама по себи, СУМИФС не може да сумира делту временских вредности већу од 30 минута. СУМИФС и ЦОУНТИФС могу се користити заједно да би се добио исти резултат као горе наведени СУМПРОДУЦТ:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Пута преко 24 сата
Ако укупно време може премашити 24 сата, користите овај прилагођени формат времена попут овог:
(h):mm:ss
Синтакса квадратних заграда говори Екцел-у да се не „преврће“ пута дуже од 24 сата.
Са помоћном колоном
Као што је приказано у примеру, такође можете додати помоћни ступац за израчунавање и збрајање делта времена. Формула у Д5, копирана доле, је:
=MAX(C5-"00:30",0)
Овде се МАКС користи за уклањање негативних временских делта, узрокованих временима у колони Ц која су краћа од 30 минута. Приметите да је резултат у Д15 исти као резултат у Г5.
