Penjumlahan Jika Text Dimulai Kata Tertentu

Kali ini kita akan membahas contoh rumus excel untuk melakukan penjumlahan bersyarat dengan kriteria text jika dimulai dengan frase atau kata tertentu. Rumus ini sangat berguna jika kita kita memiliki sejumlah data text dengan pola kata awalan tertentu yang dapat dijadikan sebagai kategori. Misalnya terdapat list data produk beserta jumlahnya. Sebuah produk dapat dikenali dari awalan kata dalam nama produk tersebut. Nah awalan kata inilah yang dapat kita jadikan sebagai acuan dalam perhitungan. Formula untuk menjumlahkan dengan syarat text dimulai kata tertentu dapat dibuat dengan menggunakan fungsi SUMIF.

Contoh Rumus:

=SUMIF(A5:A14,"buah*",B5:B14)

Contoh rumus di atas dapat digunakan untuk menjumlahkan nilai yang ada pada range B5:B14 jika sel sejajar pada range A5:A14 berupa text yang diawali kata "buah"

Rumus Umum:

=SUMIF(rangeKriteria,kriteria,rangeJumlah)

Penjelasan:

Fungsi SUMIF berguna untuk menjumlahkan nilai pada rangeJumlah jika sel sejajar pada rangeKriteria memenuhi kriteria yang ditentukan.

• rangeKriteria = sebuah range berupa kolom dalam tabel, berisi text yang menjadi acuan atau syarat dalam penjumlahan. Contoh: range A5:A14
• kriteria = text yang harus ada supaya penjumlahan bisa dieksekusi. Kata yang menjadi kriteria harus diakhiri dengan tanda / karakter bintang ( * ) yang menandakan bahwa kata tersebut harus berada di awal text. Contoh: text "buah*" menandakan bahwa kita ingin menjumlahkan semua nilai jika text dalam rangeKriteria berawalan kata "buah"
• rangeJumlah = sebuah range berupa kolom dalam tabel yang ingin kita jumlahkan nilai yang terdapat dalam sel-sel penyusunya jika sel sejajar dalam rangeKriteria berawalan kata yang ditentukan. Contoh: Range B5:B14

Cara Kerja Rumus

Perhatikan contoh rumus:

=SUMIF(A5:A14,"buah*",B5:B14)

Sebagaimana kita ketahui bahwa fungsi SUMIF berguna untuk melakukan penjumlahan bersyarat, yaitu proses penjumlahan jika syarat tertentu terpenuhi. Kunci utama dalam proses penjumlahan jika text dimulai awalan kata tertentu adalah cara penulisan kriteria dimana harus diakhiri dengan karakter bintang atau tanda asterisk (*).  Karakter asterik ini merupakan salah satu dari karakter wildcard.

Jika karakter bintang  (*) dihilangkan, maka rumus SUMIF hanya akan menjumlahkan jika text pada rangeKriteria berisi text “buah” saja. Ini artinya rumus akan menghasilkan nilai nol karena pada range kriteria A5:A14 tidak ada sel yang hanya berisi text “buah”.

Hasil dari contoh formula di atas adalah 6 yaitu penjumlahan buah apel, buah durian dan buah nangka, yaitu semua produk yang diawali kata "buah"

Untuk lebih memahami silahkan dicoba gunakan kata lainnya sebagai kriteria, misalnya “baju*”  dan “sayur”, kemudian perhatikan hasilnya.

Sampai tahap ini diharapkan contoh rumus penjumlahan bersyarat dengan kriteria text diawali kata tertentu dapat difahami dengan baik dan dapat diterapkan dalam pekerjaan sehari-hari menggunakan microsoft excel.

Contoh Rumus Excel Lainnya

• Rumus Penjumlahan Setiap Baris ke-N
• Rumus Penjumlahan Setiap N Baris
• Rekapitulasi jumlah berdasarkan nama hari

Rumus Penjumlahan Setiap Baris ke-N

Dalam artikel sebelumnya sudah dibahas mengenai contoh rumus untuk menjumlahkan nilai setiap beberapa baris konstan atau setiap N Baris. Masih ada kaitan dengan pembahasan tersebut, dalam kesempatan ini akan dibahas contoh rumus untuk menjumlahkan nilai setiap kelipatan baris tertentu atau setiap baris ke-N dari sebuah range (harap dibedakan: pembahasan sebelumnya mengenai penjumlahan setiap N baris, sedangkan pada pembahasan kali ini mengenai penjumlahan setiap baris ke-N)

Untuk membuat rumus penjumlahan setiap baris ke-N diperlukan kombinasi beberapa fungsi, antara lain: SUMPRODUCT, MOD, dan ROW.

Contoh Rumus:

=SUMPRODUCT((MOD(ROW(B5:B14)-9,5)=0)*B5:B14)

Contoh rumus tersebut digunakan untuk menjumlahkan setiap kelipatan baris ke-5 pada range B5:B14, dimulai dari baris ke-9.

Rumus Umum:

=SUMPRODUCT((MOD(ROW(range)-barisAwal,N)=0)*range)

Penjelasan:

• Range = range berisi data nilai yang akan dijumlahkan setiap baris ke-N nya, contoh range B5:B14
• barisAwal = baris start untuk memulai penjumlahan, contoh baris 9, karena bilangan pertama yang ingin kita jumlahkan terletak pada baris ke-9, dan selanjutnya akan dijumlahkan dengan baris ke-5 berikutnya yaitu baris (9+5) = baris ke-14 (perhatikan gambar di atas)
• N = Nilai kelipatan baris yang ingin kita jumlahkan.  Contoh : setiap baris ke-5

Cara Kerja Rumus

Perhatikan struktur contoh rumus:

=SUMPRODUCT((MOD(ROW(B5:B14)-9,5)=0)*B5:B14)

Silahkan dilihat bagian rumus ROW(B5:B14)-9

Bagian rumus tersebut berguna untuk menghitung nomor urut baris pada range B5:B14 relative terhadap baris ke-9. Untuk melihat nilainya, dalam spreadsheet, silahkan diseleksi bagian rumus ROW(B5:B14)-9   dan tekan F9, maka rumus akan menjadi:

=SUMPRODUCT((MOD({-4;-3;-2;-1;0;1;2;3;4;5},5)=0)*B5:B14)

Selanjutnya fungsi MOD berguna untuk menghitung sisa pembagian. Jika bilangan dalam array habis di bagi lima maka fungsi MOD akan menghasilkan bilangan Nol. Bilangan dalam array ({-4;-3;-2;-1;0;1;2;3;4;5} yang memenuhi kriteria habis di bagi 5 adalah bilangan 0 dan 5. 

Selanjutnya fungsi MOD dikompare dengan bilangan 0 untuk mendapatkan nilai boolean TRUE dan FALSE.

Silahkan di seleksi bagian rumus (MOD({-4;-3;-2;-1;0;1;2;3;4;5},5)=0) kemudian tekan F9, sehingga rumus menjadi sebagai berikut:

=SUMPRODUCT({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}*B5:B14)

Perhatikan nilai TRUE dalam array berada tepat setiap urutan kelipatan ke-5.

Dengan cara yang sama, seleksi bagian rumus B5:B14 dan kemudian tekan F9 untuk analisa rumus lebih lanjut:

=SUMPRODUCT({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}*{2;1;2;3;8;2;3;4;4;13})

Sebagaimana kita ketahui, dalam operasi perkalian, nilai boolean TRUE akan diperlakukan sebagai bilangan 1, sedangkan nilai FALSE sebagai bilangan 0. 

Karena nilai TRUE terletak pada urutan  ke-5 dan ke-10, maka hasil akhirnya akan berupa penjumlahan elemen ke-5 dan ke-10 pada array {2;1;2;3;8;2;3;4;4;13} yaitu 8 + 13 = 21.

Sampai disini pembahasan mengenai contoh rumus untuk penjumlahan setiap baris ke-N. Mudah-mudahan penjelasan yang sangat singkat ini dapat mudah difahami.

Contoh Rumus Excel Lainnya

• Rumus Penjumlahan Setiap N Baris
• Rekapitulasi jumlah berdasarkan nama hari
• Rekapitulasi Jumlah Mingguan

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

Rekapitulasi Jumlah Berdasarkan Hari

Rumus excel untuk rekapitulasi jumlah nilai pada hari tertentu dapat dibuat menggunakan fungsi SUMPRODUCT dan dibantu dengan fungsi WEEKDAY. Sebagai alternative, fungsi SUMPRODUDUCT juga dapat digantikan oleh fungsi SUM, namum rumus harus dituliskan dalam bentuk formula array.

Contoh:

=SUMPRODUCT((WEEKDAY($A$5:$A$14)=D5)*$B$5:$B$14)

Untuk mempersingkat penulisan dan memudahkan pembacaan rumus, kita hilangkan tanda dolarnya:

=SUMPRODUCT((WEEKDAY(A5:A14)=D5)*B5:B14)

 

Rumus Umum:

=SUMPRODUCT((WEEKDAY(rgTanggal)=hari)*rgJumlah)

Penjelasan:

• rgTanggal = range berupa kolom dalam tabel berisi data ber-type tanggal. Contoh: range A5:A14
• rgJumlah = range berupa kolom berisi data jumlah per masing-masing tanggal. Contoh: range B5:B14
• hari = sel berisi data berupa bilangan 1 s.d 7 yang diberi format number "[$-421]dddd" sehingga secara nampak secara visual sebagai nama hari. Angka 1 mewakili hari Minggu, Angka 2 mewakili hari Senin, demikian seterusnya sampai angka 7 mewakili hari Sabtu. Contoh : Sel D5 memimiliki konten angka 1 yang diformat "[$-421]dddd" sehingga terbaca sebagai hari Minggu.

Cara Kerja Rumus

Perhatikan contoh rumus pada sel E5 pada gambar di atas, untuk rekapitulasi jumlah berdasarkan nama hari 

=SUMPRODUCT((WEEKDAY(A5:A14)=D5)*B5:B14)

Fungsi WEEKDAY berguna unguk mengkonversi data tanggal yang ada pada range A5:A14 menjadi bilangan 1 s/d 7 yang mewakili hari Minggu s/d hari Sabtu. Kemudian hasil fungsi WEEKDAY terebut dicompare dengan nilai yang ada pada sel D5. Sel D5 berisi angka 1 dengan number format "[$-421]dddd" sehingga sel menampakan text “Minggu”.

Hasil komparasi atau perbandingan antara nilai WEEKDAY atas data tanggal pada range A5:A14 dengan bilangan pada sel D5 akan menghasilkan nilai boolean TRUE atau FALSE. TRUE menunjukan nilai komparasi yang sama, sedangkan FALSE, berarti sebaliknya yaitu tidak sama.

Pada lembar kerja excel, silahkan diseleksi bagian rumus yang di-highlight

=SUMPRODUCT((WEEKDAY(A5:A14)=D5)*B5:B14)

Kemudian tekan F9, maka rumus akan berubah menjadi seperti berikut ini.

=SUMPRODUCT(({FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE})*B5:B14)

Selanjutnya silahkan diseleksi kembali bagian rumus B5:B14, kemudian tekan F9, sehingga berubah menjadi array bilangan yang adalam dalam range B5:B14

=SUMPRODUCT(({FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE})*{2;1;2;3;4;2;3;4;4;4})

Nilai boolean TRUE dan FALSE kemudian diperkalikan dengan data jumlah yang ada pada range B5:B14.

Sebagaimana kita ketahui, pada proses perkalian, TRUE akan diperlakukan sebagai bilangan 1, sedangkan FALSE diperlakukan sebagain bilangan 0. Dengan demikian rumus dapat dituliskan menjadi seperti di bawah ini: (diperoleh dengan cara seleksi bagian rumus yang merupakan semua parameter fungsi SUMPRODUCT, kemudian tekan F9)

=SUMPRODUCT({0;0;0;3;0;0;0;0;4;0})

Terakhir, fungsi SUMPRODUCT menyelesaikan tugasnya dengan menjumlahkan semua bilangan yang ada dalam array. Hasilnya adalah 7.

Alternative Rumus Rekapitulasi Jumlah Berdasarkan Hari

Selain menggunakan fungsi SUMPRODUCT seperti yang sudah dijelaskan di atas,  Kita juga bisa mengganti fungsi SUMPRODUCT dengan fungsi SUM yang dibuat dalam bentuk rumus array. Rumus array dibuat dengan cara menekan Ctrl + Shift + Enter begitu kita selesai mengetikan rumus.

Berikut contoh rumusnya:

{=SUM((WEEKDAY(A5:A14)=D5)*B5:B14)}

Demikian pebahasan singkat mengenai contoh rumus excel untuk rekapitulasi data berdasarkan nama hari.

Contoh Rumus Excel Lainnya

• Rekapitulasi Jumlah Mingguan
• Rumus Penjumlahan Berdasarkan Bulan
• Rumus Penjumlahan Berdasarkan Grup

Rekapitulasi Jumlah Mingguan

Rumus excel untuk rekapitulasi jumlah mingguan atau penjumlahan berdasarkan data mingguan dapat dilakukan menggunakan bantuan fungsi SUMIFS. Fungsi ini bekerja dengan cara menjumlahkan data pada tanggal ditentukan sampai dengan kurang dari 7 hari kedepan. Sebagai alternative, kita juga bisa menggunakan fungsi SUMPRODUCT untuk membuat summary data mingguan. Selain itu, fungsi SUM juga dapat digunakan untuk keperluan ini, namun harus dalam bentuk formula array.

Contoh:

=SUMIFS(B5:B14,A5:A14,">="&D5,A5:A14,"<"&D5+7)

Rumus Umum:

=SUMIFS(rgJumlah,rgTanggal,">="&mingguan,rgTanggal,"<="& mingguan+7)

Penjelasan:

rgTanggal = range berupa kolom dalam tabel berisi data ber-type tanggal. Contoh: range A5:A14

rgJumlah = range berupa kolom berisi data jumlah per masing-masing tanggal. Contoh: range B5:B14

mingguan = sel berisi data bertype tanggal, disusun dalam rentang waktu seminggu sekali. Contoh: sel D5, rekapitulasi jumlah mingguan ditentukan setiap hari Selasa sampai dengan hari Senin berikutnya.

Cara Kerja Rumus

Perhatikan contoh rumus untuk rekapitulasi jumlah mingguan pada sel E5 dalam gambar di atas

=SUMIFS(B5:B14,A5:A14,">="&D5,A5:A14,"<"&D5+7)

Dapat kita lihat dari rumus di atas. Ternyata kita hanya memerlukan satu fungsi SUMIFS dengan 2 kondisi untuk membuat rumus rekapitulasi mingguan.

• Kondisi 1 = Tanggal >= Tanggal pertama yang ditentukan dalam mingguan.
• Kondisi 2 = Tanggal < tanggal pertama yang ditentukan dalam minggu berikutnya.

Fungsi SUMIFS dengan 2 kondisi dapat dituliskan dengan syntax berikut:

=SUMIFS(sum_range,criteria_range1,criteria1, criteria_range2,criteria2)

Sum_range = rgJumlah ⇒ adalah range yang akan dijumlahkan nilainya yaitu B5:B14

Criteria_range1 = criteria_range2 = rgTanggal ⇒ adalah range yang dijadikan sebagai dasar penjumlahan jika kondisi atau kriterianya terpenuhi, yaitu range A5:B14

Criteria1 ⇒ adalah kondisi 1 yang harus terpenuhi, yaitu tanggal harus ">=mingguan" , dituliskan “>=”&D5 karena tanggal mingguan terletak di sel D5

Criteria2 ⇒ adalah kondisi 2 yang harus terpenuhi, yaitu tanggal harus "<(mingguan+7)", dituliskan "<"&D5+7 karena tanggal mingguan terladak pada sel D5

Dengan demikian rumus tersebut di atas akan menghitung berapa jumlah data selama 7 hari dimulai dari tanggal ditentukan. Penjumlahan data selama 7 hari dapat dikatakan sebagai penjumlahan arau rekapitulasi mingguan.

Alternative Rumus Rekapitulasi Mingguan

Selain menggunakan fungsi SUMIFS seperti yang sudah dijelaskan di atas.  Kita juga bisa menggunakan rumus alternative lainnya.

Rekapitulasi mingguan menggunakan fungsi SUMPRODUCT

=SUMPRODUCT((A5:A14>=D5)*(A5:A14<D5+7)*B5:B14)

Rekapitulasi mingguan menggunakan fungsi SUM dalam bentuk rumus array.

{=SUM((A5:A14>=D5)*(A5:A14<D5+7)*B5:B14)}

Keterangan: cara kerja dan cara mengetik parameter rumus SUMPRODUCT dan rumus array SUM hampir sama. Hanya saja, pada saat selesai mengetik rumus SUM, kita harus menekan Ctrl + Shift + Enter untuk menjadikannya sebagai rumus array.

Contoh Rumus Excel Lainnya

• Rumus Penjumlahan Berdasarkan Bulan
• Rumus Penjumlahan Berdasarkan Grup
• Menjumlahkan Nilai Terendah Dengan Kriteria

Rumus Penjumlahan Berdasarkan Grup Bulan

Menjumlahkan nilai berdasarkan grup bulan yang akan dibahas dalam kesempatan ini tidak mempertimbangkan tahun. Artinya data pada bulan yang sama namun tahun berbeda akan turut terjumlah. Oleh karena itu cara ini disarankan hanya digunakan terhadap satu tahun periode data saja. Adapun fungsi yang digunakan adalah SUMPRODUCT dan MONTH. Sebagai alternative, juga boleh menggunakan kombinasi SUM dan MONTH dalam bentuk formula array.

Contoh:

 =SUMPRODUCT((MONTH(A5:A14)=MONTH(D5))*B5:B14)

Rumus Umum:

=SUMPRODUCT((MONTH(rgTanggal)=MONTH(bulan))*rgJumlah)

Penjelasan:

rgTanggal = range berupa sebuah kolom dalam tabel berisi data ber-type tanggal (date)

bulan = sebuah sel berisi type data tanggal dengan format “mmmm” nampak nama bulannya saja. Contoh: tgl 1/1/2018 dengan format "mmmm" akan nampak sebagai bulan "January"

rgJumlah = range berupa sebuah kolom yang sejajar dengan rgTangal berisi nilai – niai yang akan dijumlahkan

Cara Kerja Rumus

Perhatikan contoh rumus sesuai gambar pada sel E5 sesuai gambar di atas:

=SUMPRODUCT((MONTH(A5:A14)=MONTH(D5))*B5:B14)

Mari kita pecah rumus tersebut menjadi beberapa langkah:

Langkah 1:  

=(MONTH(A5:A14)=MONTH(D5) )

Expresi ini berguna untuk mengecek nilai bulan pada masing-masing sel di dalam range A5:A14 apakah sama atau tidak dengan nilai bulan pada data tanggal di sel D5. Proses ini menghasilkan array berisi  data boolean TRUE dan FALSE.  Pada formula bar silahkan diseleksi bagian rumus (MONTH(A5:A14)=MONTH(D5)), kemudian tekan F9, maka kita akan memperoleh sebuah array berisi TRUE dan FALSE

{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Langkah 2:  

=(MONTH(A5:A14)=MONTH(D5) * B5:B14

= hasilLangkah1 * B5:B14

Langkah ke-2 ini akan mengalikan hasil dari langkah ke-1 dengan range B5:B14

Nilai dalam range B5:B14 jika dituliskan dalam bentuk array adalah:

{2;1;2;3;4;2;3;4;4;4}

Sehingga rumus =hasilLangkah1*B5:B14 dapat dijabarkan sebagai berikut:

={TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*{2;1;2;3;4;2;3;4;4;4}

Sebagai mana  kita ketahui TRUE sepadan dengan nilai 1, sedangkan FALSE sepadan dengan nol. Sehingga perkalian tersebut akan menghasilkan array berikut:

={2;1;0;3;0;0;0;0;0;0}

Hasil dari langkah 2 ini jika berdiri sendiri hanya akan menghasilkan nilai 2 yaitu elemen pertama dalam array. Untuk menjumlahkan semua elemen dalam array, maka diperlukan bantuan fungsi SUMPRODUCT

Langkah 3:  

=SUMPRODUCT((MONTH(A5:A14)=MONTH(D5))*B5:B14)

=SUMPRODUCT(hasilLangkah2)

Fungsi SUMPRODUCT berguna untuk menjumlahkan semua elemen array hasil langkah 2, sehingga rumus dapat dijabarkan sebagai berikut:

=SUMPRODUCT({2;1;0;3;0;0;0;0;0;0})

Dan hasil akhirnya adalah 6. Hasil ini merupakan penjumlahan semua nilai pada bulan Januari.

Catatan: 

Pada penjelasan di atas semua alamat range menggunakan referensi relative dengan tujuan supaya lebih mudah membacanya. Agar referensi rgTanggal dan rgJumlah tidak bergeser pada saat rumus di copy paste ke sel di bawahnya, maka disarankan menggunakan referensi absolute. Oleh karenanya contoh rumus di atas dapat diedit kembali menjadi sebagai berikut:

=SUMPRODUCT((MONTH($A$5:$A$14)=MONTH(D5))*$B$5:$B$14)

Alternative Rumus Penjumlahan Berdasarkan Grup Bulan.

Selain menggunakan SUMPRODUCT, kita juga bisa memanfaatkan fungsi SUM dalam bentuk formula array.

Caranya:

Ketik rumus berikut di sel E5

=SUM((MONTH(A5:A14)=MONTH(D5))*B5:B14)

Begitu selesai mengetik rumus, lanjutkan dengan menekan Ctrl + Shift + Enter untuk menjadikannya sebagai formula array, maka pada formula Bar kita akan melihat rumus tadi berada dalam tanda kurung kurawal {}.

{=SUM((MONTH(A5:A14)=MONTH(D5))*B5:B14)}

Cara kerja rumus array ini hampir sama dengan rumus SUMPRODUCT.

Demikian pembahasan singkat mengenai contoh rumus excel untuk rekapitulasi penjumlahan nilai berdasarkan grup bulan.

Contoh Rumus Excel Lainnya

• Rumus Penjumlahan Berdasarkan Grup
• Menjumlahkan Nilai Terendah Dengan Kriteria
• Menjumlahkan Nilai Tertinggi Dengan Kriteria

Rumus Penjumlahan Berdasarkan Grup

Menjumlahkan nilai berdasarkan grup sangat berguna untuk mengetahui total nilai terkait kelompok kategori data tertentu dalam sebuah tabel. Contoh rumus yang akan dijelaskan dalam kesempatan ini dapat diterapkan pada sebuah tabel dimana data sudah disortir berdasarkan kriteria grup, sehingga item grup mengelompok, tidak acak. Dengan demikian kita bisa membuat rumus total jumlah nilai grup tertentu setiap baris pertama dalam grup.

Contoh:

  

Rumus Umum:

=IF(A2<>A1,SUMIF(rangeKriteria,Kriteria,rangeJumlah),"")

Penjelasan:

A2<>A1 merupakan expresi untuk mengecek konten sebuah sel apakah tidak sama dengan konten sel di atasnya. Jika tidak sama makan proses penjumlahan nilai grup  akan dilakukan.

rangeKriteria = range grup yang dijadikan sebagai kriteria dalam proses penjumlahan. Dalam hal adalah kolom warna (range A5:A14)

Kriteria = grup yang ingin dijumlahkan nilainya. Kriteria grup ini akan diambil dari konten pada salah satu sel dalam rangeKriteria (A5:A14) 

rangeJumlah = range pada kolom jumlah (range B5:B14) yang akan dijumlahkan nilainya per masing-masing grup.

Cara Kerja Rumus

Perhatikan contoh rumus excel di atas untuk menghitung jumlah nilai masing-masing grup warna.

=IF(A5<>A4,SUMIF(A5:A14,A5,B5:B14),"")

Rumus tersebut menggunakan kombinasi fungsi IF dan SUMIF.

Fungsi IF berguna untuk mengecek apakah konten sel A5 sama atau tidak dengan sel di atasnya (sel A4).  Jika berbeda maka  hasilnya adalah TRUE, sebaliknya jika sama maka hasilnya adalah FALSE.

Fungsi SUMIF berperan untuk menindaklanjuti jika A5<>A4 bernilai TRUE. Dengan kata lain konten sel A5 berbeda dengan konten A4. Fungsi SUMIF ini menjumlahkan nilai pada rangeJumlah (B5:B14) dengan mengacu pada kriteria warna (sel A5) yang ditemukan pada konten pada rangeKriteria (A5:A14) 

Jika A5<>A4 bernilai FALSE atau konten A5 sama dengan konten A4 maka rumus akan menghasilkan nilai blank ""

Supaya referensi rangeKriteria (A5:A14) dan rangeJumlah (B5:B14) tidak bergeser ketika rumus  di copy-paste ke sel di bawahnya maka kita perlu menerapkan referensi absolute. Sehingga rumus dapat dituliskan sebagai berikut:

=IF(A5<>A4,SUMIF($A$5:$A$14,A5,$B$5:$B$14),"")

Alternative Rumus Untuk Menjumlahkan Berdasarkan Grup

Selain menggunakan fungsi SUMIF, kita juga bisa menggunakan fungsi lainnya untuk proses penjumlahan berdasarkan grup. Misalnya fungsi SUMIFS dan SUMPRODUCT.

Penjumlahan Grup Menggunakan Kombinasi IF dan SUMIFS

=IF(A5<>A4,SUMIFS(B5:B14,A5:A14,A5))

Penjumlahan Grup Menggunakan Kombinasi IF dan SUMPRODUCT

=IF(A5<>A4,SUMPRODUCT((A5:A14=A5)*B5:B14),"")

Demikian pembahasan singkat mengenai contoh rumus excel untuk menjumlahkan nilai per masing-masing grup. Semoga bermanfaat.

Contoh rumus excel lainnya:

• Menjumlahkan nilai terendah dengan kriteria
• Menjumlahkan nilai tertinggi dengan kriteria
• Menjumlahkan nilai terendah di excel