Featured Post

Lookup Gambar Dengan INDEX MATCH

Apakah Pencarian Gambar bisa dilakukan di excel? Pertanyaan ini sangat menarik sekali untuk dibahas. Jika pembaca mengikuti blog ini, pada ...

Saturday, December 24, 2016

Rumus Excel SUMPRODUCT - Satu Fungsi, Multi Guna.

Fungsi Excel SUMPRODUCTApa 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:

Cara Menggunakan Fungsi SUMPRODUCT


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)


Fungsi SUMPRODUCT Satu Kondisi


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:

Menganalisa Fungsi SUMPRODUCT



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:

Kegunaan Shortcut F9 Excel

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))


Fungsi SUMPRODUCT Dua Kondisi



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.

Fungsi SUMPRODUCT Logika AND


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:


Logika AND dalam Fungsi SUMPRODUCT



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)


Fungsi SUMPRODUCT Logika OR




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:

Logika OR dan AND Dalam Fungsi SUMPRODUCT


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:


Menghitung Berat Rata-Rata Dengan Fungsi SUMPRODUCT




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:

Lookup 2 Dimensi Menggunakan Rumus SUMPRODUCT


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:

20 comments:

  1. dibanding yang lain cukup lengkap..mungkin kalau jurus2 sumproduct yang lebih advance lagi bisa dishare karena sangat berguna bagi pengguna spt saya yang baru mulai mencoba pakai sumproduct

    ReplyDelete
  2. Sumproduct... it's so Powerful... especially, if combine wiith others formulas !!! OMG..

    nice article !!

    ReplyDelete
  3. Assalamuálaikum selamat malam mas
    mohon maaf mas, mohon bantuan penjelasannya...
    di bagian "1. SUMPRODUCT dengan Logika AND",
    dijelaskan bagaimana cara hitung Berapa Jumlah Penjualan buah mangga di daerah Ciamis.

    apa bisa kita hitung berapa macam buah yang dijual di daerah Ciamis?

    Mohon bantuan jawaban dan solusinya mas, terima kasih banyak

    Sandi

    ReplyDelete
    Replies
    1. Wa'alaikumussalam

      Bisa Mas, tetapi harus menggunakan kolom bantu berisi gabungan text atau CONCATENATE dari kolom "Daerah" dan kolom "Produk", misalnya kolom E sebagai kolom bantu, maka di sel E2 ketikan rumus =B2&C2 , copy rumus tersebut sampai baris akhir data (contoh sampai baris ke-13)
      Selanjutnya, rumus untuk menghitung berapa macam buah di jual di daerah Ciamis:
      =SUMPRODUCT(--(B2:B13="Ciamis"),1/COUNTIF(E2:E13,E2:E13))

      Delete
  4. Halo mas mau tanya apa ada rumus sumifs menghitung disheet yg berbeda. Mksih

    ReplyDelete
    Replies
    1. Sebenarnya, logika rumus SUMIFS sama saja, baik menghitung di sheet yang sama maupun sheet berbeda atau bahkan workbook yang berbeda. Kita tinggal mainkan mouse untuk memilih parameter rumus berupa range cells apakah dalam satu sheet, beda sheet, atau beda worbook.

      SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

      Delete

      Delete
  5. Tapi ga bsa mas hasilnya 0, apakah ada rumus tambahan sepetri kita menghitung sum disheet berbeda sum(sheet1!a1:b1)

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Coba mas penjualan mangga di daerah ciamis menggunakan sumifs beda sheet. Mksih mas

    ReplyDelete
    Replies
    1. Bisa dikirimkan contoh file nya ke email sy

      Delete
  8. Mas Je J, tolong bantu hitung penjumlahan baris dengan syarat tidak melebihi nilai tertentu. Contoh soal sudah saya kirim ke email. Ditunggu jawaban dan balasannya. Terimakasih.

    ReplyDelete
  9. mohon bantuannya untuk statement ini:
    Jika array mengandung elemen yang bukan bilangan, maka elemen tersebut akan diperlakukan sebagai angka Nol.

    rumus excel apa yang sebaiknya digunakan jika array mengandung elemen text/huruf?

    ReplyDelete
    Replies
    1. Tergantung untuk apa tujuannya mas. Fungsi SUMPRODUCT digunakan untuk mengalikan elemen sejajar antara beberapa range atau array, kemudian menjumlahkan hasil perkalian tersebut.
      Klw elemen array berupa text atau huruf, tentu saja tidak dapat dilakukan operasi matematika, termasuk operasi perkalian.

      Delete
  10. saya mau jumlah kan laki perempuan yang di filter? pakai rumus apa...?countif tidak bisa....tidak bisa menghitung bagian di filter saja

    ReplyDelete
    Replies
    1. Coba dengan rumus dengan pola seperti ini mas:

      =SUMPRODUCT((kolomKriteria=kriteria)*SUBTOTAL(103,OFFSET(headerKriteria,ROW(kolomKriteria)-ROW(headerKriteria),0)))

      kriteria = data yang akan dihitung jumlahnya: misalnya "laki-laki"
      kolomKriteria = referensi kolom bagian tabel yang berisi kriteria yang dicari, dimulai baris pertma setelah header sampai baris terakhir tabel ditambah 1
      headerKriteria = referensi sel yang merupakan header kolom kriteria (satu sel diatas referensi kolomKritera

      Delete
  11. This comment has been removed by the author.

    ReplyDelete
  12. terima kasih ilmunya thor..
    kalau mau hitung "berapa produk" di "contoh rumus sumproduct dgn 2 kondisi" bagaimana mas? terima kasih

    ReplyDelete
  13. tolong solusinya mas!
    misalnya membuat angka berurut dalam 1 kolom dengan lebih 1 variable misal(mangga1, mangga2 dst, apel1 apel2 dst, anggur1 anggur2 dst) berdasarkan isi kolom lainya? terima kasih

    ReplyDelete
  14. Mau tanya untuk rumus sumproduct dapat digunakan walau ada beberapa data yg sell nya blank

    ReplyDelete

Terimakasih sudah berkunjung dan membaca blog ini. Silahkan berkomentar.