Formula umum
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Ringkasan
Untuk menjumlahkan jumlah total waktu selama 30 menit, dengan serangkaian waktu yang mewakili durasi, Anda dapat menggunakan fungsi SUMPRODUCT dan TIME. Pada contoh yang ditunjukkan, rumus di G5 adalah:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
di mana "waktu" adalah rentang bernama C5: C14.
Penjelasan
Rumus ini menggunakan fungsi SUMPRODUCT untuk menjumlahkan hasil dari dua ekspresi yang menghasilkan array. Tujuannya adalah untuk menjumlahkan hanya waktu yang lebih dari 30 menit, waktu "surplus" atau "ekstra". Ekspresi pertama mengurangi 30 menit dari setiap waktu dalam rentang bernama "kali":
times-TIME(0,30,0)
Ini menghasilkan array seperti ini:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Ekspresi kedua adalah tes logika untuk semua waktu yang lebih dari 30 menit:
times>TIME(0,30,0)
Ini membuat larik nilai TRUE FALSE:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Di dalam SUMPRODUCT, kedua larik ini dikalikan bersama untuk membuat larik ini:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Perhatikan nilai negatif dalam larik pertama sekarang adalah nol. Selama perkalian, nilai TRUE FALSE diubah menjadi 1 dan nol, sehingga nilai FALSE "membatalkan" waktu yang tidak lebih dari 30 menit. Terakhir, SUMPRODUCT mengembalikan jumlah semua nilai dalam larik, 1 jam dan 4 menit (1:04).
Alternatif dengan SUMIFS dan COUNTIFS
Dengan sendirinya, SUMIFS tidak dapat menjumlahkan delta nilai waktu lebih dari 30 menit. SUMIFS 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
Jika total waktu mungkin melebihi 24 jam, gunakan format waktu kustom seperti ini:
(h):mm:ss
Sintaks tanda kurung siku memberi tahu Excel untuk tidak "menggulung" kali lebih dari 24 jam.
Dengan kolom pembantu
Seperti yang diperlihatkan dalam contoh, Anda juga dapat menambahkan kolom pembantu untuk menghitung dan menjumlahkan delta waktu. Rumus di D5, disalin, adalah:
=MAX(C5-"00:30",0)
Di sini, MAX digunakan untuk menghilangkan delta waktu negatif, yang disebabkan oleh waktu di kolom C yang kurang dari 30 menit. Perhatikan hasil di D15 sama dengan hasil di G5.
