Rumus Penjumlahan Setiap N Baris

Rumus penjumlahan setiap beberapa baris tertentu atau N baris sangat berguna jika kita memiliki sejumlah data dengan struktur urutan yang tetap. Misalnya menjumlahkan data setiap 5 hari sekali, 6 hari sekali atau 7 hari sekali dan bahkan seminggu sekali. Contoh rumus excel yang akan dibahas kali ini digunakan untuk menjumlahkan nilai setiap seminggu sekali dimana jumlah hari dalam seminggu diambil 5 hari kerja saja. Dengan kata lain kita akan menjumlahkan data setiap 5 baris. Untuk keperluan ini diperlukan gabungan fungsi SUM, OFFSET dan ROW.

Contoh Rumus:

=SUM(OFFSET(B5,(ROW()-6)*5,0,5,1))

Rumus Umum:

=SUM(OFFSET(selAwal,(ROW()-barisRumus1)*N,0,N,1))

Penjelasan:

• selAwal = sel pertama atau sel paling atas dari kolom data yang akan dijumlahkan nilainya, contoh sel B5
• barisRumus1 = Lokasi baris sel tempat mengetikan rumus yang dapat menghitung jumlah N baris pertama. Contoh: Sel tempat mengetikan rumus untuk menghitung jumlah nilai minggu ke-1 (5 baris pertama) adalah sel E6. Sel ini terletak pada baris ke-6. Sehingga kita bisa menentukan parameter barisRumus1 = 6.
• N = Nilai yang kita inginkan untuk menjumlahkan nilai setiap N baris.

Cara Kerja Rumus

Menghitung jumlah nilai pada 5 baris pertama (minggu ke-1):

Perhatikan contoh rumus pada sel E6 yang digunakan untuk menghitung nilai pada minggu pertama (5 baris pertama)

=SUM(OFFSET(B5,(ROW()-6)*5,0,5,1))

Fungsi ROW berguna untuk mengambil index baris sel dimana rumus diketik.  Karena rumus di ketik di sel E6 maka ROW() menghasilkan nilai 6. Sehingga (6-6)*5 = 0

Sehingga rumus OFFSET dapat dituliskan sebagai berikut:

=OFFSET(B5,0,0,5,1)

Hasil rumus OFFSET di atas akan mengacu ke range B5:B9

Mungkin pembaca masih bertanya-tanya mengapa contoh rumus OFFSET di atas menghasilkan range B5:B9

Rumus =OFFSET(B5,0,0,5,1) dapat dibaca sebagai berikut:  sebuah range yang jaraknya 0 baris dan 0 kolom dari sel B5 dimana range tersebut terdiri atas 5 baris dan 1 kolom. Maka range yang memenuhi kriteria tersebut adalah range B5:B9

Selanjutnya fungsi SUM bertugas menjumlahkan nilai pada range B5:B9

=SUM(B5:B9)

Dan hasilnya adalah 12

Menghitung Jumlah Nilai pada 5 Baris ke-2 (Minggu ke-2)

Selanjutnya mari kita lihat bagaimana hasilnya jika rumus =SUM(OFFSET(B5,(ROW()-6)*5,0,5,1)) diletakan di sel E7 untuk menghitung nilai pada 5 baris berikutnya (minggu ke-2). Rumusnya sama dengan penjumlahan minggu ke-1, namun akan menghasilkan jumlah nilai yang berbeda.

Kunci dari perbedaan ini adalah rumus ROW() yang akan menghasilkan nilai sesuai lokasi baris dimana rumus ditempatkan. Karena rumus ditempatkan di sel E7 maka rumus ROW() akan menghasilkan nilai 7 sehingga rumus dapat dijabarkan sebagai berikut:

=SUM(OFFSET(B5,(ROW()-6)*5,0,5,1))

=SUM(OFFSET(B5,(7-6)*5,0,5,1))

=SUM(OFFSET(B5,5,0,5,1))

=SUM(B10:B14)

=17

Perhatikan bagian rumus OFFSET(B5,5,0,5,1). Rumus ini dapat diterjemahkan sebagai berikut: sebuah range yang jaraknya 5 baris dan 0 kolom dari sel B5 dimana range tersebut terdiri atas 5 baris dan 1 kolom. Range yang memenuhi kriteria tersebut adalah range B10:B14 yang merupakan 5 baris ke 2 dalam range data yang ingin kita hitung jumlah nilainya.

Demikian pembahasan singkat mengenai contoh rumus untuk menghitung jumlah nilai setiap beberapa baris di excel. Semoga penjelasannya mudah difahami dan bisa bermanfaat.

Contoh Rumus Excel Lainnya

• Rekapitulasi jumlah berdasarkan nama hari
• Rekapitulasi Jumlah Mingguan
• Rumus Penjumlahan Berdasarkan Bulan

Berbagi :