Rumus SUMPRODUCT Untuk Penjumlahan Bersyarat

Ada beberapa alternatif rumus excel yang dapat digunakan untuk melakukan penjumlahan bersyarat, salah satunya adalah dengan menggunakan Fungsi SUMPRODUCT. Mengapa menggunakan SUMPRODUCT? karena fungsi ini tersedia, baik versi excel baru, maupun versi lama bersama dengan fungsi SUMIF. Dibandingkan dengan fungsi SUMIF, SUMPRODUCT lebih unggul dalam kaitan kemampuannya yaitu dapat menyelesaikan tugas penjumlahan bersyarat dengan kriteria lebih dari satu jenis.

Penjelasan mengenai cara menggunakan fungsi SUMPRODUCT sendiri sudah disampaikan dalam artikel sebelumnya: Cara Mudah Memahami Rumus SUMPRODUCT. Silahkan dilihat-lihat dulu jika anda belum begitu familiar dengan fungsi ini.

Jika anda sudah mulai familiar dengan fungsi SUMPRODUCT, mari kita lanjutkan ke pembahasan cara Menggunakan Fungsi SUMPRODUCT utuk Penjumlahan Bersyarat. Model penjumlahan seperti ini sering disebut juga Penjumlahan Dengan Kriteria atau Penjumlahan Dengan Kondisi (conditional sum)

Contoh Kasus.

Anggaplah kita memiliki data penjualan produk buah-buahan yang disimpan dalam sebuah tabel terdiri atas tiga kolom. Kolom pertama berisi data tanggal, kolom kedua berisi data Nama Barang, dan kolom Ketiga berisi data Nilai penjualan. Bentuk tabel digambarkan dalan screenshot di bawah ini.

Selanjutnya tugas kita adalah melakukan rekapitulasi penjumlahan dengan syarat atau kriteria sebagai berikut:

1. Menjumlahkan nilai penjualan buah mangga di semua tanggal (Kondisi Tunggal)

2. Menjumlahkan nilai penjualan buah mangga pada tanggal 2 dan 3 April 2018 (Beberapa Kondisi)

Mari kita intip sati per satu.

Contoh 1: Penjumlahan Dengan Kondisi / Kriteria Tunggal Untuk Menghitung Penjualan Buah Mangga.

Untuk kasus ini, hanya ada satu kriteria atau kondisi yang diperlukan, yaitu Buah Mangga. Secara umum rumus SUMPRODUCT ini dapat dituliskan sebagai berikut:

=SUMPRODUCT(--(nama_buah=”buah mangga”), penjualan)

Karena kita menggunakan tabel dimana nama buah terdapat pada range B2:B13 dan data nilai penjumlahan ada di range C2:C13, maka rumus untuk menghitung nilai penjumlahan buah mangga di semua tanggal adalah sebagai berikut:

=SUMPRODUCT(--(B2:B13="Buah Mangga"),C2:C13)

Cara Kerja :

• Rumus SUMPRODUCT akan mengecek konten setiap sel pada range B2:B3 apakah berisi text “Buah Mangga”. Jika iya maka akan menghasilkan nilai TRUE, jika sebaliknya maka bernilai FALSE.  Fungsi SUMPRODUCT juga akan mengambil konten nilai penjualan dari setiap sel pada range C2:C13.

• Jika dikonversi menjadi array maka rumus di atas akan nampak seperti di bawah ini.

SUMPRODUCT(--({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}),{1000000;1500000;2000000;1000000;2000000;2500000;1500000;3000000;2000000;1000000;2000000;2500000})

• Nilai TRUE dan FALSE, supaya bisa diproses selanjutnya maka harus dikonversi dahulu menjadi data numerik. Untuk tugas ini dilimpahkan ke double unary. Perhatikan 2 buah tanda minus (--) sebelum argumen pertama. Tanda inilah yang disebut double unary. Operator ini berguna untuk merubah TRUE menjadi 1, sedangkan FALSE menjadi 0, sehingga dalam tampilan array, rumus tadi akan nampak seperti ini.

=SUMPRODUCT({1;0;0;1;0;0;1;0;0;1;0;0},{1000000;1500000;2000000;1000000;2000000;2500000;1500000;3000000;2000000;1000000;2000000;2500000})

• Ingat kembali mengenai tugas fungsi SUMPRODUCT yaitu melakukan perkalian komponen-komponen terkait dalam array yang diberikan, kemudian mengembalikan jumlah dari setiap hasil perkalian tersebut. Sehingga jika dihitung dengan operasi matematika penjumlahan, rumus di atas dapat dituliskan seperti berikut:

= (1x1.000.000) + (0x1.500.000) + (0x2.000.000) + (1x1.000.000) + (0x2.000.000) + (0x2.500.000) + (1x1.500.000) + (0x3.000.000) + (0x2.000.000) + (1x1.000.000) + (0x2.000.000) + (0x2.500.000)

=1.000.000 + 0 + 0 + 1.000.000 + 0 + 0 + 1.500.000 + 0 + 0 + 1.000.000 + 0 + 0

= 4.500.000

Contoh 2: Penjumlahan dengan kriteria ganda untuk menghitung total penjualan buah mangga pada tanggal 2 s.d 3 April 2018

Untuk menglakukan penjumlahand alam contoh ke-2 ini, kita perlu mempertimbangkan 3 kondisi:

Kondisi ke-1 : Tanggal terbawah, dalam  hal ini tgl 2 April 2018

Kondisi ke-2 : Tanggal teratas, dalam hal ini adalah tanggal 3 April 2018

Kondisi ke-3 : Nama buah yang akan dijumlahkan nilai penjualannya.

Rumus Umum

=SUMPRODUCT(--(tanggal>=tanggal_terbawah),--(tanggal<=tanggal_teratas),--(nama_buah="Buah Mangga"),C2:C13)

Penting: 

• Penulisan tanggal pada kolom tanggal harus benar yaitu harus berupa tanggal, jangan berupa text yang nampak seperti tanggal.
• Untuk menuliskan tanggal dalam rumus SUMPRODUCT, kita bisa menggunakan bantuan fungsi DATE. Fungsi ini dapat dituliskan dengan syntax DATE(tahun,bulan,tanggal).

Setelah kedua poin diatas difahami, maka selanjutnya kita bisa membuat rumus SUMPRODUCT untuk menghitung jumlah nilai penjualan buah mangga pada tanggal 2 s.d 3 April 2018. 

Adapun rumusnya dapat dituliskan sebagai berikut:

=SUMPRODUCT(--(A2:A13>=DATE(2018,4,2)),--(A2:A13<=DATE(2018,4,3)),--(B2:B13="Buah Mangga"),C2:C13)

=2.500.000

Cara Kerja:

• Rumus sumproduct akan mengecek 3 kondisi yang harus terpenuhi, ketiga kondisi tersebut berupa array bernilai TRUE dan FALSE hasil pengecekan tanggal bawah, tanggal atas dan nama buah. TRUE dan FALSE tersebut kemudian dikonversi menjadi bilangan 1 dan 0 oleh operatur double unary (--). 
• Fungsi SUMPRODUCT kemudian melakukan perkalian masing-masing komponen array yang terkait (sebaris). Jika ada angka 0 dalam salah satu komponen yang dikalikan, maka akan dihasilkan bilangan 0.  Ini artinya semua syarat harus terpenuhi atau bernilai 1  supaya nilai penjumlahan dapat terhitung. Terakhir fungsi SUMPRODUCT menjumlahkan semua hasil perhitungan tadi. Dan hasilnya adalah 2.500.000

Tips: Anda dapat menggunakan tombol shortcut F9 untuk mengecek masing-masing elemen array dalam rumus SUMPRODUCT. Cukup seleksi salah satu argumen fungsi, kemudian tekan F9. Cara penggunakan shortcut F9 untuk mengecek array ini sudah dijelaskan pada pembahasan rumus SUMPRODUCT sebelumnya yang berjudul : Cara Mudah Memahami Rumus SUMPRODUCT.

Demikian pembahasan singkat mengenai cara menggunakan fungsi SUMPRODUCT untuk membuat rumus penjumlahan bersyarat. 

Semoga bermanfaat.

Silahkan dilihat juga contoh rumus excel lainnya:

Cara Mudah Memahami Rumus SUMPRODUCT

Fungsi SUMPRODUCT