Rumus Excel SUMPRODUCT - Satu Fungsi, Multi Guna.

Apa sebenarnya kegunaan fungsi SUMPRODUCT? Mengapa fungsi ini dikatakan sebagai fungsi multi guna?. 

Mari kita lihat jawabannya dalam catatan pelajaran excel berikut ini. 

Belajar excel kali ini memang khusus dipersembahkan untuk membahas mengenai rumus excel SUMPRODUCT dan kegunaan praktisnya. 

Bahasan dimulai yang paling sederhana yaitu pengenalan basic syntax dan cara penggunaan fungsi tersebut. Kemudian pada bagian selanjutnya akan dibahas contoh-contoh kegunaan praktis fungsi SUMPRODUCT, termasuk kegunaannya sebagai alternative rumus SUMIFS, COUNTIFS, AVERAGEIFS, Lookup 2 dimensi, dan Rumus Array CSE.

• Syntax dan Cara Penggunaan Fungsi SUMPRODUCT
• Contoh-Contoh Formula Bagaimana Menggunakan Fungsi SUMPRODUCT Pada Excel
• Rumus SUMPRODUCT Perhitungan Dengan Satu Kondisi
• Cara Kerja Rumus SUMPRODUCT Satu Kondisi
• Rumus SUMPRODUCT Perhitungan Dengan Kondisi Ganda
• Cara Kerja Rumus SUMPRODUCT Dua Kondisi
• Rumus SUMPRODUCT Untuk Menghitung Frekuensi Data, Menjumlah dan Merata-Rata Dengan Kriteria.
• Rumus SUMPRODUCT Untuk Menghitung Berat Rata-Rata
• Rumus SUMPRODUCT Sebagai Alternative Lookup 2 Dimensi
• Rumus SUMPRODUCT Sebagai Alternative Rumus Array

Apa yang terbayangkan oleh anda sewaktu pertama kali mendengar istilah rumus SUMPRODUCT? Mungkin anda mengenali fungsi ini sebagai gabungan antara SUM dan PRODUCT.

Mengenai fungsi SUM, saya kira mayoritas pengguna excel sudah sangat familiar dengannya.

Betapa tidak, fungsi penjumlahan yang satu ini merupakan salah satu fungsi yang paling banyak digunakan. Bahkan termasuk oleh pengguna yang masih awam sekalipun.

Pembahasan lebih rinci mengenai fungsi SUM dapat dibaca dalam artikel Fungsi SUM dan Rumus Penjumlahan Pada Excel

Bagaimana dengan fungsi PRODUCT?

Untuk yang satu ini, mungkin masih ada pengguna excel yang belum familiar. Padahal kenyataannya fungsi ini memang tersedia pada microsoft excel.

Fungsi PRODUCT digunakan untuk mendapatkan hasil perkalian antara dua buah bilangan atau lebih.

Contoh:

Rumus =PRODUCT(bil1,bil2,bil3) sama saja hasilnya dengan rumus =bil1*bil2*bil3.

Itulah mengapa, nampaknya fungsi PRODUCT kurang diminati dan jarang digunakan. Hal ini karena rumus perkalian lebih familiar dan lebih mudah difahami oleh kebanyakan user dibandingkan fungsi PRODUCT.

Bagaimana dengan SUMPRODUCT?

SUMPRODUCT sungguh merupakan sebuah fungsi excel yang multiguna.

Jika anda pengguna excel 2003 atau versi yang lebih lama, maka anda dapat menggunakan fungsi SUMPRODUCT untuk melakukan penjumlahan / perhitungan bersyarat ganda.

Sedangkan pada Excel 2007 dan versi yang lebih baru, tugas tersebut juga dapat dilakukan oleh fungsi SUMIFS dan COUNTIFS. Meskipun demikian, Fungsi SUMPRODUCT juga tetap dapat digunakan pada versi ini.

Fungsi SUMPRODUCT juga dapat digunakan untuk melakukan VLOOKUP 2 dimensi, yaitu rumus untuk mencari nilai dalam sel yang merupakan titik temu antara dua kriteria yang terletak dalam baris dan kolom.

Harap diperhatikan setiap contoh-contoh rumus yang akan dibahas. Jika anda sudah mengenal dan memahami rumus array, anda pasti akan menemukan kemiripan antara rumus array dengan SUMPRODUCT.

Dan memang SUMPRODUCT bekerja terhadap array, meskipun dalam aplikasinya tidak perlu menggunakan shortcut CSE seperti biasanya jika kita menggunakan rumus array.

Syntax dan Cara Penggunaan Fungsi SUMPRODUCT

Pada dasarnya fungsi SUMPRODUCT bekerja untuk melakukan perkalian antara bilangan dalam array, dan kemudian menjumlahkan hasil perkalian tersebut.

Adapun syntax fungsi SUMPRODUCT sangat simple dan sangat jelas.

SUMPRODUCT(array1, [array2], [array3], …)

Array1, array2 dan seterusnya adalah range sel atau array yang masing-masing elemennya ingin kita kalikan kemudian hasil perkaliannya dijumlahkan.

Jumlah maksimum argumen array yang dapat digunakan dalam fungsi SUMPRODUCT adalah 255 pada excel 2007,2010,2013 dan 2016. Sedangkan excel 2003 atau versi yang lebih lama, hanya mengizinkan maksimal 30 array.

Meskipun SUMPRODUCT bekerja dengan data array, tetapi fungsi ini tidak memerlukan penggunan shortcut array (CTR + Shift + Enter). Kita cukup menekan Enter setelah mengetikan formula, seperti halnya rumus biasa.

Hal-hal yang perlu diperhatikan seputar penggunaan Fungsi SUMPRODUCT:

• Semua aray dalam rumus SUMPRODUCT harus memiliki jumlah baris dan kolom yang sama. Jika tidak, maka rumus SUMPRODUCT akan menghasilkan nilai error.
• Jika array mengandung elemen yang bukan bilangan, maka elemen tersebut akan diperlakukan sebagai angka Nol.
• Jika elemen array berupa test logika yang bernilai TRUE atau FALSE, maka dalam kebanyakan kasus, kita perlu mengkonversinya menjadi 1 atau 0 menggunakan double unary operator (--), atau menggunakan fungsi SIGN
• Fungsi SUMPRODUCT tidak mendukung penggunaan karakter wildcard.

Dasar-Dasar Penggunaan Fungsi SUMPRODUCT pada Excel.

Untuk membantu memahami cara kerja fungsi SUMPRODUCT, mari kita perhatikan contoh kasus dibawah ini:

Anggaplah anda memiliki daftar belanja buah-buahan yang dibuat menggunakan microsoft excel.

• Kolom A berisi nama daftar belanja buah-buahan
• Kolom B berisi Kuantitas belanjaan
• Kolom C berisi data harga per satuan.

Pertanyannya: Bagaimana cara kita menghitung (tanpa menggunakan kolom bantu) banyaknya biaya yang diperlukan?

Mari kita lihat gambar berikut:

Perhatikan:

Untuk mendapatkan jumlah biaya yang diperlukan, kita bisa menghitungnya menggunakan rumus perkalian dan penambahan biasa.

=B2*C2+B3*C3+B4*C4+B5*C5

Atau jika referensi tersebut diganti dengan bilangan, maka rumus dapat dituliskan sebagai berikut:

=3*10000+4*5000+5*8000+3*6000
=108.000

Rumus diatas memang sangat mudah untuk difahami. Cara kerjanya sederhana, cukup dengan cara mengalikan quantity produk dengan harganya, kemudian hasilnya dijumlahkan dengan hasil perkalian quantity dan harga produk lainnya.

Bayangkan…

Seandaiya kita memiliki ratusan sampai ribuan barang yang harus dihitung..

Rumus model diatas, sepertinya bukanlah solusi yang tepat, kecuali jika anda memiliki kesabaran tingkat dan ketelitian tinggi untuk mengetikan rumusnya.

Solusinya: Saatnya beralih ke penggunaan fungsi SUMPRODUCT.

=SUMPRODUCT(B2:B5,C2:C5)

Dengan rumus tersebut, sebanyak apapun baris data yang digunakan, anda tidak perlu repot mengetikan rumus yang panjang. Yang diperlukan hanya penyesuaian referensi range datanya saja.

Misalnya: jika baris data mencapai 1000, maka atur saja batas akhir range yang digunakan, misalnya B2:B1000 dan C2:C1000.

Menurut saya, cara ini sangat Simple dan Mudah.

Bagaimana menurut anda?

Contoh-Contoh Rumus – Bagaimana Menggunakan Fungsi SUMPRODUCT pada Excel

Melakukan perkalian pasangan elemen-elemen antara dua atau lebih range dan kemudian menjumlahkan hasil perkaliannya merupakan bentuk paling mendasar dan sederhana dari penggunaan fungsi SUMPRODUCT pada Excel.

Akan tetapi, keanggunan dan kehebatan fungsi SUMPRODUCT tidak terbatas pada definisi dasarnya saja. Ada banyak tugas kompleks yang dapat diselesaikan menggunakan fungsi multiguna ini.

Kehebatan apa saja yang dapat diperoleh menggunakan fungsi SUMPRODUCT?

Mari kita bongkar satu persatu dalam contoh-contoh rumus yang akan digali lebih jauh dalam belajar excel kali ini.

Silahkan dibaca terus artikel ini ya 😉

Fungsi SUMPRODUCT Dengan Satu Kondisi

Semakin lama dan sering menggunakan excel, maka kita akan menyadari sebenarnya ada banyak jalan untuk menyelesaikan tugas excel tertentu.

Namun untuk kaitan membandingkan dua atau lebih array, terutama yang berhubungan dengan kalkulasi yang bersyarat ganda, maka hanya ada dua cara yang paling efektif, yaitu: Menggunakan Rumus Array Atau menggunakan rumus SUMPRODUCT..

Mengenai rumus array sudah dibahas dalam artikel Memahami Rumus Array Untuk Pemula. Sedangkan untuk rumus SUMPRODUCT, inilah inti pembahasan belajar excel kali ini.

Anggaplah kita memiliki tabel dalam lembar kerja excel dengan susunan sebagai berikut:

- Kolom A berisi list produk
- Kolom B berisi target penjualan (Rp)
- Kolom C berisi aktual penjualan (Rp)
- Untuk contoh, baris data dibatasi s.d baris 10.

Pertanyaan: Bagaimana cara menghitung banyaknya item produk yang tidak mencapai target penjualan?

Jawaban: Gunakan fungsi SUMPRODUCT

Caranya: Ketikan rumus dibawah ini:

=SUMPRODUCT(--(C2:C10<B2:B10))

atau

=SUMPRODUCT((C2:C10<B2:B10)*1)

Bagaimana Cara Kerja Rumus SUMPRODUCT dengan Satu Kondisi

Mari kita lihat kembali lebih dalam, contoh rumus SUMPRODUCT untuk menghitung berapa kali nilai sel pada range C2:C10 lebih kecil dibandingkan dengan nilai sel sejajar pada range B2:B10.

=SUMPRODUCT(--(C2:C10<B2:B10))

Bagaimana rumus tersebut bisa memberikan nilai akhir = 4 ?

Dengan menggunakan mouse, seleksi porsi  (--(C2:C10<B2:B10) pada formula bar, kemudian tekan F9. Untuk lebih jelasnya perhatikan gambar berikut:

Apa yang kita dapatkan?

Kita akan melihat array yang berisi nilai boolean TRUE dan FALSE.

Perhatikan kembali logika menggunakan operator kurang dari (<).

Operator ini akan mengecek apakah nilai sel pada range C2:C10 kurang dari nilai sel sejajar pada range B2:B10. Jika iya maka akan bernilai TRUE (benar), dan jika kondisi sebaliknya maka akan bernilai FALSE (salah)

Selanjutnya, apa guna tanda minus kembar (--) atau yang secara tehnik disebut double unary operator?

Operator ini digunakan untuk mengkorversi nilai TRUE dan FALSE menjadi 1 dan 0.

Melanjutkan langkah berikutnya, pada formula bar seleksi bagian rumus berikut,
--{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE}

Kemudian tekan F9. Akan lebih mudah memahami dengan memperhatikan ilustrasi berikut:

Dari ilustrasi diatas, kita dapat melihat bahwa nilai TRUE berubah menjadi 1, sedangkan FALSE berubah menjadi 1 dan 0.  Inilah gunanya tanda minus kembar, atau double unary operator, merubah nilai logika menjadi numerik sehingga bisa dikalkulasi lebih lanjut.

Tahap akhir; Bilangan 1 dan 0 dijumlahkan sehingga menghasilkan nilai akhir = 4


Masih ingin tahu alternative lainnya?

Double unary operator ternyata dapat digantikan oleh fungsi SIGN

Cobalah: buat rumus =SIGN(TRUE) atau =SIGN(FALSE) dan perhatikan hasilnya.   Bandingkan dengan rumus =--(TRUE) atau =--(FALSE)

Hasilnya sama saja kan ?

Sehingga formula =SUMPRODUCT(--(C2:C10<B2:B10)) dapat diganti menjadi =SUMPRODUCT(SIGN(C2:C10<B2:B10))


Ternyata masih ada Alternative Lainnya:

Selain menggunakan double unary operator dan fungsi SIGN, kita juga dapat mengkonversi nilai logika TRUE dan FALSE dengan cara mengalikannya dengan bilangan 1.

=SUMPRODUCT((C2:C10<B2:B10)*1)

Jika tidak percaya, silahkan lakukan ujicoba rumus =TRUE*1  atau =FALSE*0, dan lihat hasilnya.

Sampai pada tahap ini, mudahan-mudahan cara kerja perhitungan dengan satu kondisi sudah bisa difahami.

Rumus SUMPRODUCT dengan Dua Kondisi

Selanjutnya mari kita pelajari lebih jauh dengan contoh rumus untuk menghitung banyaknya data yang memenuhi beberapa kondisi tertentu atau bersyarat ganda.

Kita ambil saja 2 kondisi sebagai contoh, karena sebanyak apapun kondisi yang digunakan, prinsip kerjanya sama saja.

Anggaplah kita sebagai penjual buah-buahan. Misalnya  ingin menghitung berapa kali penjualan buah mangga yang  tidak mencapai target.

Untuk tujuan tersebut, kita dapat menggunakan rumus berikut:

=SUMPRODUCT(--(A2:A10="mangga"),--(C2:C10<B2:B10))

Atau

=SUMPRODUCT((A2:A10="mangga")*(C2:C10<B2:B10))

Bagaimana Cara Kerja Rumus SUMPRODUCT Dengan Kriteria Ganda

Sebagaimana kita ketahui pada pembahasan awal, bahwa fungsi SUMPRODUCT digunakan untuk melakukan perkalian elemen sejajar dalam array kemudian menjumlahkan hasilnya.

Mari kita perhatikan kembali rumus untuk menghitung berapa kali penjulan buah mangga yang tidak mencapai target sesuai contoh sebelumnya.

=SUMPRODUCT(--(A2:A10="mangga"),--(C2:C10<B2:B10))

Bagaimana bisa diperoleh hasil = 2 ?

• Seleksi sel dimana rumus tersebut ditempatkan
• Pada formula bar, seleksi bagian rumus (A2:A10="mangga"), kemudian tekan F9, maka kita akan dapatkan {TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}
• Nilai boolean TRUE dan false tersebut harus dikonversi menjadi data numerik, salah satunya adalah menggunakan double unary operator (--), cobalah seleksi hasil tahap sebelumnnya, tetapi termasuk 2 tanda negatif yang mendahuluinya yaitu --{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE} , kemudian tekan F9, maka hasilnya adalah {1;0;0;0;1;0;0;0;1}
• Dengan cara dan tahapan yang sama, lakukan pada porsi rumus (C2:C10<B2:B10) 
• Secara keseluruhan, rumus menjadi =SUMPRODUCT({1;0;0;0;1;0;0;0;1},{0;1;0;0;1;0;0;1;1})
• Ingat kembali cara kerja SUMPRODUCT untuk mengalikan elemen sejajar, kemudian menambahkan masing-masing hasilnya. Maka formula tersebut dapat dijelaskan dengan rumus =(1*0)+(0*1)+(0*0)+(0*0)+(1*1)+(0*0)+(0*0)+(0*1)+(1*1)
• Maka hasilnya adalah 2

Menghitung Banyak data, Menjumlah dan Merata-rata Dengan Kriteria Ganda

Mulai excel versi 2007, kita mengenal adanya fungsi COUNTIFS, SUMIFS dan AVERAGEIFS untuk menghitung banyak (frekuensi) data, menjumlahkan dan merata-rata dengan kriteria ganda.

Pada excel 2003 dan versi yang lebih lama, ketiga fungsi tersebut belum tersedia.

Namun sebenarnya tugas ketiga fungsi tersebut dapat dikerjakakan oleh fungsi SUMPRODUCT. Inilah salah satu alasan kenapa SUMPRODUCT disebut sebagai fungsi Multiguna.

Jadi jangan khawatir meskipun komputer anda sendiri atau komputer di kantor masih menggunakan excel 2003, karena SUMPRODUCT tersedia di semua versi excel, setidaknya pada versi 2003 s.d 2016 yang sudah saya gunakan. Untuk versi excel yang lebih tua dari 2003 silahkan dicek sendiri ya, kebetulan saya tidak punya versinya. 😓

1. SUMPRODUCT dengan Logika AND

Yang dimaksud logika AND adalah dimana kombinasi dua pernyataan atau lebih akan bernilai benar (TRUE) jika semua pernyataan bernilai (Benar)

Anggaplah kita memiliki data berikut yang merupakan data penjualan buah-buahan pada berbagai daerah dari tanggal 1 s.d 12 Desember.

Bagaimana cara mengetahui jumlah penjualan di daerah Ciamis, Berapa kali penjualannya dan Berapa rata-ratanya?

Disini kita menggunakan logika AND, yaitu ada dua kenyataan yang harus sama sama benar (TRUE) yaitu:

• Lokasi penjualan = Ciamis
• Nama Buah = Mangga

Kedua hal tersebut harus terpenuhi sebagai syarat kalkulasi.

Seperti halnya dalam rumus array CSE, logika AND dalam rumus SUMPRODUCT  juga dapat dipenuhi menggunakan bantuan operator asterisk (*)

Perhatikan contoh rumus berikut dengan asumsi baris terakhir data adalah baris 13.

• Berapa Jumlah Penjualan buah mangga di daerah  Ciamis?

=SUMIFS(D2:D13,B2:B13,"Ciamis",C2:C13,"Mangga")

Atau

=SUMPRODUCT((B2:B13="Ciamis")*(C2:C13="Mangga")*(D2:D13))

• Berapa kali penjualan buah mangga di daerah Ciamis?

=COUNTIFS(B2:B13,"Ciamis",C2:C13,"Mangga",D2:D13,">0")

Atau

=SUMPRODUCT((B2:B13="Ciamis")*(C2:C13="Mangga")*(D2:D13>0))

• Berapa rata-rata Penjualan  buah mangga di daerah Ciamis?

=AVERAGEIFS(D2:D13,B2:B13,"Ciamis",C2:C13,"Mangga"))

Atau

=SUMPRODUCT((B2:B13="Ciamis")*(C2:C13="Mangga")*(D2:D13))/SUMPRODUCT((B2:B13="Ciamis")*(C2:C13="Mangga")*(D2:D13>0))

Dari contoh cotoh rumus diatas, sudah sangat jelas bahwa  fungsi SUMPRODUCT dapat menggantikan fungsi SUMIFS, COUNTIFS dan AVERAGEIFS, meskipun sebagai pengganti AVERAGEIFS, perlu rumus yang lebih panjang.

Supaya lebih fleksible, kita bisa menaruh kriteria perhitungan dalam sebuah sel

Misalnya 

- Kriteria daerah pemasaran disimpan di sel C16

- Kriteria nama buah disimpan di sel C17

Maka rumus penjumlahan, frekuensi dan rata-rata dapat dituliskan sebagai berikut:

Jumlah    : =SUMPRODUCT((B2:B13=C16)*(C2:C13=C17)*(D2:D13))

Frekuensi:  =SUMPRODUCT((B2:B13=C16)*(C2:C13=C17)*(D2:D13>0))

Rata-rata: =SUMPRODUCT((B2:B13=C16)*(C2:C13=C17)*(D2:D13))/SUMPRODUCT((B2:B13=C16)*(C2:C13=C17)*(D2:D13>0))

Untuk menghitung rata-rata, karena merupakan hasil pembagian jumlah dengan frekuensi maka rumusnya dapat menggunakan referensi lokasi rumus jumlah dan frekuensi. Perhatikan ilustrasi berikut:

CONTOH2: Rumus SUMPRODUCT Dengan Logika OR

Logika OR akan bernilai benar jika kombinasi pernyataan memiliki minimal satu pernyataan yang bernilai benar (TRUE). 

Misalnya:

Untuk menghitung berapa banyak penjualan buah mangga dan buah pisang pada tabel contoh sebelumnya.

Mangga dan pisang sama-sama terletak dalam satu kolom dan kedua nya harus diperhitungkan dalam proses kalkulasi.

Ini artinya kita harus membuat rumus yang menghasilkan nilai TRUE apabila salah satu kondisi terpenuhi.

Kondisi tersebut adalah:

• Nama buah = "mangga" atau  "pisang"

Seperti halnya juga dalam rumus array, logika OR dalam rumus SUMPRODUCT dapat dibantu menggunakan operator plus (+).

Perhatikan contoh rumus berikut:

Berapa kali (frekuensi) buah mangga dan  pisang yang terjual tanpa memandang lokasi penjualannya

=SUMPRODUCT((C2:C13="mangga")+(C2:C13="pisang"))

Berapa nilai total penjualan buah mangga dan pisang?

=SUMPRODUCT((C2:C13="mangga")+(C2:C13="pisang"))

Atau

=SUMPRODUCT(((C2:C13="mangga")+(C2:C13="pisang"))*D2:D13)

Contoh 3: Formula SUMPRODUCT Dengan Kombinasi Logika AND dan OR

Dalam beberapa kasus, mungkin kita perlu memperhitungkan beberapa kondisi dengan logika AND dan OR sekaligus.

Sayangnya bahkan dalam versi excel terbaru pun, Satu fungsi SUMIFS dan COUNTIFS tidak bisa menghandle kasus ini. Salah satu cara yang dapat dilakukan jika tetap mau menggunakan kedua fungsi tersebut adalah menggunakan kombinasi dua atau lebih dari fungsi SUMIFS +  SUMIFS, atau COUNTIFS+COUNTIFS

Untungnya hal ini bisa dihandle oleh hanya Satu Fungsi SUMPRODUCT saja.

Melanjutkan contoh tabel penjualan buah-buahan sebelumnya, Misalnya kita ingin menghitung berapa kali frekuensi penjualan dan nilai penjualan buah mangga dan buah pisang di daerah ciamis.

Perhatikan syarat yang harus dipenuhi

• Lokasi penjualan  : Ciamis
• Nama Buah          : Mangga atau Pisang

Secara sederhana, kriteria ini dapat dituliskan dalam logika AND dan OR sebagai berikut:

AND(lokasi="ciamis",OR(buah="mangga",buah="pisang"))

Ekspresi atau pernyataan tersebut akan bernilai benar (TRUE) jika lokasi ="ciamis" dan buah ="mangga" atau buah="pisang"

Mari kita lihat dalam contoh di bawah ini:

Berapa kali frekuensi penjualan buah mangga dan pisang di daerah Ciamis?

Cara1: Menggunakan rumus COUNTIFS, diperlukan 2 fungsi:

=COUNTIFS(B2:B13,"ciamis",C2:C13,"mangga")+COUNTIFS(B2:B13,"ciamis",C2:C13,"pisang")

Cara2: Menggunakan rumus SUMPRODUCT, hanya diperlukan 1 fungsi:

=SUMPRODUCT((B2:B13="ciamis")*((C2:C13="mangga")+(C2:C13="pisang")))

Berapa Total nilai penjualan buah mangga dan pisang di daerah Ciamis?

Cara1: Menggunakan rumus SUMIFS , diperlukan 2 fungsi

=SUMIFS(D2:D13,B2:B13,"ciamis",C2:C13,"mangga")+SUMIFS(D2:D13,B2:B13,"ciamis",C2:C13,"pisang")

Cara 2: Menggunakan rumus SUMPRODUCT, hanya diperlukan 1 fungsi

=SUMPRODUCT((B2:B13="ciamis")*((C2:C13="mangga")+(C2:C13="pisang"))*D2:D13)

Lebih jelasnya perhatikan ilustrasi dalam screenshot dibawah ini:

Formula SUMPRODUCT Untuk Menghitung Berat Rata-Rata

Masih berbicara dengan contoh buah-buahan. Kita bisa menggunakan rumus SUMPRODUCT untuk menghitung berat buah rata-rata tanpa harus menggunakan kolom bantu.

Saya mencontohkan dengan buah kelapa sawit. Satuan hitung diukur dengan jumlah tandan atau sering disebut juga janjang kelapa sawit, sedangkan berat rata-ratanya diistilahkan Berat Janjang Rata-rata atau disingkat BJR.

Bagaimana menghitung BJR jika diketahui data sebagai berikut:

Tanpa menggunakan kolom bantu, sebenarnya kita juga bisa menghitungnya dengan rumus matematika biasa yaitu:

=(C2*D2+C3*D3+C4*D4+C5*D5+C6*D6)/SUM(C2:C6)

Namun rumus tersebut tidak efesien, terutama jika digunakan pada baris data yang banyak.

Oleh karenanya disarankan menggunakan SUMPRODUCT

Rumusnya seperti ini:

=SUMPRODUCT(jjg_blok, bjr_blok) / SUM(jjg_blok)

Dan jika diterapkan dalam lembar excel menjadi

=SUMPRODUCT(C2:C6,D2:D6)/SUM(C2:C6)

Cukup mudah bukan?

Atau bertele-tele?

Tergantung anda dan dari sudut mana anda memandangnya.

Rumus SUMPRODUCT Sebagai Alternative LOOKUP 2 Dimensi.

LOOKUP 2 dimensi disini artinya adalah mencari titik temu antara baris dan kolom sesuai syarat atau kriteria tertentu.

Contohnya:

Sebuah tabel data berisi penjualan buah-buahan per bulan.

• Baris menunjukan jenis buah
• Kolom menunjukan bulan per bulannya
• Data berisi nilai penjualan buah.

Bagaimana rumus untuk mendapatkan nilai penjualan buah tertentu pada bulan tertentu.

Perhatikan contoh dalam prinscreen berikut:

Perhatikan bahwa jumlah penjualan pisang pada bulan Maret adalah 7.500.000. 

Jika kita baca dalam lembar kerja excel, data tersebut merupakan titik temu antara baris pisang dan kolom maret. Inilah yang disebut dengan lookup 2 dimensi.

Rumus tersebut dapat dituliskan sebagai berikut:

=SUMPRODUCT((A2:A4="pisang")*(B1:G1="maret")*B2:G4)

Dan supaya lebih fleksible, maka nama buah dan bulan kita simpan dalam sel:

Nama buah : sel B10

Nama bulan : sel B11

Sehingga rumus menjadi:

=SUMPRODUCT((A2:A4=B10)*(B1:G1=B11)*B2:G4)

Sampai pada tahap ini mudah-mudahan dapat difahami.

SUMPRODUCT sebagai alternatif rumus array.

Jika kita sudah memahami rumus array dan memperhatikan contoh-contoh rumus SUMPRODUCT dari awal pembahasan sampai bagian akhir, kita bisa melihat bahwa ada banyak tugas yang dapat diselesaikan menggunakan fungsi SUMPRODUCT, juga dapat diselesaikan menggunakan rumus array (CSE).

Supaya masih hangat dalam benak pembaca, saya menggunakan contoh rumus SUMPRODUCT yang terakhir dibahas yaitu rumus alternative Lookup 2 dimensi.

=SUMPRODUCT((A2:A4=B10)*(B1:G1=B11)*B2:G4)

Rumus tersebut ternyata akan menghasilkan nilai yang sama dengan rumus array berikut:

{=SUM((A2:A4=B10)*(B1:G1=B11)*B2:G4)}

Terbukti….

Silahkan dicoba dengan rumus-rumus yang lainnya.

....

Demikian pembahasan mengenai fungsi SUMPRODUCT. Dimulai dari pembahasan syntax dan basic penggunaanya, Dilanjutkan dengan pembahasan contoh-contoh rumus dan penjelasan cara kerjanya. Hingga pada bagian akhir, kita bisa mengetahui bahwa rumus SUMPRODUCT ternyata bisa menjadi alternatif rumus-rumus dan fungsi lainnya termasuk rumus array.

Tidak salah jika dikatakan bahwa SUMPRODUCT sebagai Rumus Multi Guna

Demikian semoga bermanfaat.

Salam..

Artikel Terkait:

• Fungsi SUM dan Rumus Penjumlahan Pada Excel
• Fungsi SUMIF dan Rumus Penjumlahan Bersyarat
• Fungsi COUNTIF: Menghitung Dengan Syarat
• Memahami Rumus VLOOKUP Dengan Studi Kasus
• Rumus Excel Tidak Berfungsi? Inilah Penyebabnya!

Referensi:

• https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/