Formula generik
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Ringkasan
Untuk menjumlahkan jumlah masa lebih dari 30 minit, diberikan satu set masa yang mewakili jangka masa, anda boleh menggunakan fungsi SUMPRODUCT dan TIME. Dalam contoh yang ditunjukkan, formula dalam G5 adalah:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
di mana "kali" adalah julat bernama C5: C14.
Penjelasan
Rumus ini menggunakan fungsi SUMPRODUCT untuk menjumlahkan hasil dua ungkapan yang menghasilkan tatasusunan. Tujuannya adalah untuk menjumlahkan masa yang lebih besar daripada 30 minit, masa "lebihan" atau "tambahan". Ungkapan pertama mengurangkan 30 minit dari setiap masa dalam julat bernama "kali":
times-TIME(0,30,0)
Ini menghasilkan susunan seperti ini:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Ungkapan kedua adalah ujian logik untuk semua masa melebihi 30 minit:
times>TIME(0,30,0)
Ini menghasilkan susunan nilai TRUE FALSE:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Di dalam SUMPRODUCT, dua tatasusunan ini digandakan bersama untuk membuat susunan ini:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Perhatikan nilai negatif dalam larik pertama kini adalah nol. Semasa pendaraban, nilai BENAR BENAR ditukar menjadi 1 dan sifar, jadi nilai PALSU "batal" kali yang tidak lebih daripada 30 min. Akhirnya, SUMPRODUCT mengembalikan jumlah semua nilai dalam larik, 1 jam dan 4 minit (1:04).
Alternatif dengan SUMIFS dan COUNTIFS
Dengan sendirinya, SUMIFS tidak dapat menjumlahkan nilai delta masa yang melebihi 30 minit. SUMIF dan COUNTIFS dapat digunakan bersama untuk mendapatkan hasil yang sama seperti SUMPRODUCT di atas:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Kali lebih dari 24 jam
Sekiranya jumlah masa melebihi 24 jam, gunakan format waktu khusus seperti ini:
(h):mm:ss
Sintaks kurungan segiempat memberitahu Excel untuk tidak "berguling" kali lebih besar daripada 24 jam.
Dengan tiang penolong
Seperti yang ditunjukkan dalam contoh, anda juga boleh menambahkan lajur pembantu untuk mengira dan menjumlahkan delta masa. Rumus dalam D5, disalin, adalah:
=MAX(C5-"00:30",0)
Di sini, MAX digunakan untuk menghilangkan delta masa negatif, yang disebabkan oleh masa di lajur C yang kurang dari 30 minit. Perhatikan hasilnya di D15 sama dengan hasil di G5.
