Belajar Microsoft Excel: Rumus Excel Sumproduct – Satu Fungsi| Multi Guna.

Fungsi Excel SUMPRODUCTApa bergotong-royong kegunaan fungsi SUMPRODUCT? Mengapa fungsi ini dibilang selaku fungsi multi guna?. 

Mari kita lihat jawabannya dalam catatan pelajaran excel berikut ini. 

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

Bahasan dimulai yang paling sederhana yakni pengenalan basic syntax dan cara penggunaan fungsi tersebut. Kemudian pada belahan selanjutnya akan dibahas contoh-contoh kegunaan gampang fungsi SUMPRODUCT , tergolong manfaatnya selaku 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 di saat pertama kali mendengar perumpamaan rumus SUMPRODUCT? Mungkin anda mengetahui fungsi ini selaku adonan antara SUM dan PRODUCT.

Mengenai fungsi SUM , saya kira dominan pengguna excel sudah sungguh familiar dengannya.

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

Pembahasan lebih rinci perihal fungsi SUM sanggup dibaca dalam postingan 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 dipakai untuk mendapat hasil perkalian antara dua buah bilangan atau lebih.

Contoh:

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

Itulah mengapa , nampaknya fungsi PRODUCT kurang disukai dan jarang digunakan. Hal ini alasannya merupakan rumus perkalian lebih familiar dan lebih gampang difahami oleh pada biasanya user dibandingkan fungsi PRODUCT.

Bagaimana dengan SUMPRODUCT?

SUMPRODUCT sungguh merupakan suatu fungsi excel yang multiguna.

Jika anda pengguna excel 2003 atau model yang lebih usang , maka anda sanggup menggunakan fungsi SUMPRODUCT untuk melakukan penjumlahan / perkiraan bersyarat ganda.

Sedangkan pada Excel 2007 dan model yang lebih gres , kiprah tersebut juga sanggup dilaksanakan oleh fungsi SUMIFS dan COUNTIFS. Meskipun demikian , Fungsi SUMPRODUCT juga tetap sanggup dipakai pada model ini.

Fungsi SUMPRODUCT juga sanggup dipakai untuk melakukan VLOOKUP 2 dimensi , yakni rumus untuk mencari nilai dalam sel yang merupakan titik temu antara dua persyaratan yang terletak dalam baris dan kolom.

Harap diamati setiap contoh-contoh rumus yang hendak dibahas. Jika anda sudah mengenal dan memahami rumus array , anda niscaya akan menerima kemiripan antara rumus array dengan SUMPRODUCT.

Dan memang SUMPRODUCT melakukan pekerjaan kepada array , walaupun dalam aplikasinya tidak perlu menggunakan shortcut CSE seumpama biasanya jikalau kita menggunakan rumus array.

Syntax dan Cara Penggunaan Fungsi SUMPRODUCT

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

Adapun syntax fungsi SUMPRODUCT sungguh simple dan sungguh jelas.

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

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

Jumlah maksimum argumen array yang sanggup dipakai dalam fungsi SUMPRODUCT merupakan 255 pada excel 2007 ,2010 ,2013 dan 2016. Sedangkan excel 2003 atau model yang lebih usang , cuma membolehkan optimal 30 array.

Meskipun SUMPRODUCT melakukan pekerjaan dengan data array , tapi fungsi ini tidak membutuhkan penggunan shortcut array (CTR + Shift + Enter). Kita cukup menekan Enter setelah mengetikan formula , seumpama halnya rumus biasa.

Hal-hal yang perlu diamati seputar penggunaan Fungsi SUMPRODUCT:

  • Semua aray dalam rumus SUMPRODUCT mesti memiliki jumlah baris dan kolom yang sama. Jika tidak , maka rumus SUMPRODUCT akan menciptakan nilai error.
  • Jika array mengandung elemen yang bukan bilangan , maka elemen tersebut akan diperlakukan selaku angka Nol.
  • Jika elemen array berupa test logika yang bernilai TRUE atau FALSE , maka dalam pada biasanya urusan , kita perlu mengkonversinya menjadi 1 atau 0 menggunakan double unary operator (–) , atau menggunakan fungsi SIGN
  • Fungsi SUMPRODUCT tidak mendukung penggunaan aksara wildcard.
Baca Juga  Belajar Microsoft Excel: Cara Cepat Isi Sel Kosong

Dasar-Dasar Penggunaan Fungsi SUMPRODUCT pada Excel.

Untuk menolong mengerti cara kerja fungsi SUMPRODUCT , mari kita amati teladan urusan dibawah ini:

Anggaplah anda memiliki daftar belanja buah-buahan yang dibentuk 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 menjumlah (tanpa menggunakan kolom bantu) banyaknya ongkos yang diperlukan?

Mari kita lihat gambar berikut:

Cara Menggunakan Fungsi SUMPRODUCT


Perhatikan:

Untuk mendapat jumlah ongkos yang dikehendaki , kita sanggup menghitungnya menggunakan rumus perkalian dan penambahan biasa.

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

Atau jikalau acuan tersebut diganti dengan bilangan , maka rumus sanggup dituliskan selaku berikut:

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

Rumus diatas memang sungguh gampang untuk difahami. Cara kerjanya sederhana , cukup dengan cara mengalikan quantity produk dengan harganya , lalu akhirnya dijumlahkan dengan hasil perkalian quantity dan harga produk lainnya.

Bayangkan…

Seandaiya kita memiliki ratusan hingga ribuan barang yang mesti dihitung..

Rumus model diatas , kelihatannya bukanlah penyelesaian yang sempurna , kecuali jikalau anda memiliki keteguhan tingkat dan kecermatan tinggi untuk mengetikan rumusnya.

Solusinya: Saatnya beralih ke penggunaan fungsi SUMPRODUCT.

=SUMPRODUCT(B2:B5 ,C2:C5)

Dengan rumus tersebut , sebanyak apapun baris data yang dipakai , anda tidak perlu repot mengetikan rumus yang panjang. Yang dikehendaki cuma pembiasaan acuan range datanya saja.

Misalnya: jikalau baris data meraih 1000 , maka atur saja batas simpulan range yang dipakai , misalnya B2:B1000 dan C2:C1000.

Menurut saya , cara ini sungguh 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 lalu menjumlahkan hasil perkaliannya merupakan bentuk paling fundamental dan sederhana dari penggunaan fungsi SUMPRODUCT pada Excel.

Akan tapi , keanggunan dan kedigdayaan fungsi SUMPRODUCT tidak terbatas pada definisi dasarnya saja. Ada banyak kiprah kompleks yang sanggup dituntaskan menggunakan fungsi multiguna ini.

Kehebatan apa saja yang sanggup diperoleh menggunakan fungsi SUMPRODUCT?

Mari kita bongkar satu persatu dalam contoh-contoh rumus yang hendak digali lebih jauh dalam mencar ilmu excel kali ini.

Silahkan dibaca terus postingan ini ya 😉

Fungsi SUMPRODUCT Dengan Satu Kondisi

Semakin usang dan sering menggunakan excel , maka kita akan menyadari bergotong-royong ada banyak jalan untuk mengakhiri kiprah excel tertentu.

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

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

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

– Kolom A berisi list produk
– Kolom B berisi sasaran penjualan (Rp)
– Kolom C berisi faktual penjualan (Rp)
– Untuk teladan , baris data dibatasi s.d baris 10.

Pertanyaan: Bagaimana cara menjumlah banyaknya item produk yang tidak meraih sasaran penjualan?

Jawaban: Gunakan fungsi SUMPRODUCT

Caranya: Ketikan rumus dibawah ini:

=SUMPRODUCT(–(C2:C10

atau

=SUMPRODUCT((C2:C10

Fungsi SUMPRODUCT Satu Kondisi

Bagaimana Cara Kerja Rumus SUMPRODUCT dengan Satu Kondisi

Mari kita lihat kembali lebih dalam , teladan rumus SUMPRODUCT untuk menjumlah berapa kali nilai sel pada range C2:C10 lebih kecil daripada nilai sel sejajar pada range B2:B10.

=SUMPRODUCT(–(C2:C10

Bagaimana rumus tersebut sanggup menampilkan nilai simpulan = 4 ?

Dengan menggunakan mouse , seleksi takaran  (–(C2:C10 pada formula kafe , lalu tekan F9. Untuk lebih jelasnya amati gambar berikut:

Menganalisa Fungsi SUMPRODUCT

Apa yang kita dapatkan?

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

Perhatikan kembali logika menggunakan operator kurang dari (<). Operator ini akan memeriksa apakah nilai sel pada range C2:C10 kurang dari nilai sel sejajar pada range B2:B10. Jika iya maka akan bernilai TRUE (benar) , dan jikalau keadaan sebaliknya maka akan bernilai FALSE (salah) Selanjutnya , apa guna tanda minus kembar (–) atau yang secara tehnik disebut double unary operator? Operator ini dipakai untuk mengkorversi nilai TRUE dan FALSE menjadi 1 dan 0. Melanjutkan langkah selanjutnya , pada formula kafe seleksi belahan rumus berikut ,
–{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE}

Kemudian tekan F9. Akan lebih gampang mengerti dengan memperhatikan ilustrasi berikut:

Kegunaan Shortcut F9 Excel

Dari ilustrasi diatas , kita sanggup menyaksikan bahwa nilai TRUE bermetamorfosis 1 , sedangkan FALSE bermetamorfosis 1 dan 0.  Inilah gunanya tanda minus kembar , atau double unary operator , mengganti nilai logika menjadi numerik sehingga sanggup dikalkulasi lebih lanjut.

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


Masih ingin tahu alternative lainnya?

Double unary operator ternyata sanggup digantikan oleh fungsi SIGN

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

Hasilnya sama saja kan ?

Sehingga formula =SUMPRODUCT(–(C2:C10 sanggup diganti menjadi =SUMPRODUCT(SIGN(C2:C10

Ternyata masih ada Alternative Lainnya:

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

=SUMPRODUCT((C2:C10

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

Baca Juga  Belajar Microsoft Excel: Mengganti Warna Sel Menurut Value Dan Formula.

Sampai pada tahap ini , mudahan-mudahan cara kerja perkiraan dengan satu keadaan sudah sanggup difahami.

Rumus SUMPRODUCT dengan Dua Kondisi

Selanjutnya mari kita pelajari lebih jauh dengan teladan rumus untuk menjumlah banyaknya data yang menyanggupi beberapa keadaan tertentu atau bersyarat ganda.

Kita ambil saja 2 keadaan selaku teladan , alasannya merupakan sebanyak apapun keadaan yang dipakai , prinsip kerjanya sama saja.

Anggaplah kita selaku pedagang buah-buahan. Misalnya  ingin menjumlah berapa kali penjualan buah mangga yang  tidak meraih target.

Untuk tujuan tersebut , kita sanggup menggunakan rumus berikut:

=SUMPRODUCT(–(A2:A10=”mangga”) ,–(C2:C10

Atau

=SUMPRODUCT((A2:A10=”mangga”)*(C2:C10

Fungsi SUMPRODUCT Dua Kondisi

Bagaimana Cara Kerja Rumus SUMPRODUCT Dengan Kriteria Ganda

Sebagaimana kita pahami pada pembahasan permulaan , bahwa fungsi SUMPRODUCT dipakai untuk melakukan perkalian elemen sejajar dalam array lalu menjumlahkan hasilnya.
Mari kita amati kembali rumus untuk menjumlah berapa kali penjulan buah mangga yang tidak meraih sasaran sesuai teladan sebelumnya.
=SUMPRODUCT(–(A2:A10=”mangga”) ,–(C2:C10
Bagaimana sanggup diperoleh hasil = 2 ?
  • Seleksi sel dimana rumus tersebut ditempatkan
  • Pada formula kafe , seleksi belahan rumus (A2:A10=”mangga”) , lalu tekan F9 , maka kita akan dapatkan {TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}
  • Nilai boolean TRUE dan false tersebut mesti dikonversi menjadi data numerik , salah satunya merupakan menggunakan double unary operator (–) , cobalah seleksi hasil tahap sebelumnnya , tapi tergolong 2 tanda negatif yang mendahuluinya yakni {TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE} , lalu tekan F9 , maka akhirnya merupakan {1;0;0;0;1;0;0;0;1}
  • Dengan cara dan tahapan yang serupa , lakukan pada takaran rumus (C2:C10 
  • 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 , lalu menyertakan masing-masing hasilnya. Maka formula tersebut sanggup diterangkan dengan rumus =(1*0)+(0*1)+(0*0)+(0*0)+(1*1)+(0*0)+(0*0)+(0*1)+(1*1)
  • Maka akhirnya merupakan 2

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

Mulai excel model 2007 , kita mengenal adanya fungsi COUNTIFS , SUMIFS dan AVERAGEIFS untuk menjumlah banyak (frekuensi) data , menjumlahkan dan merata-rata dengan persyaratan ganda.
Pada excel 2003 dan model yang lebih usang , ketiga fungsi tersebut belum tersedia.
Namun bergotong-royong kiprah ketiga fungsi tersebut sanggup dikerjakakan oleh fungsi SUMPRODUCT. Inilah salah satu argumentasi kenapa SUMPRODUCT disebut selaku fungsi Multiguna.
Jadi jangan kalut walaupun komputer anda sendiri atau komputer di kantor masih menggunakan excel 2003 , alasannya merupakan SUMPRODUCT tersedia di semua model excel , setidaknya pada model 2003 s.d 2016 yang sudah saya gunakan. Untuk model excel yang lebih bau tanah dari 2003 silahkan dicek sendiri ya , kebetulan saya tak punya versinya. 😓

1. SUMPRODUCT dengan Logika AND

Yang dimaksud logika AND merupakan dimana variasi dua pernyataan atau lebih akan bernilai benar (TRUE) jikalau semua pernyataan bernilai (Benar)
Anggaplah kita memiliki data berikut yang merupakan data penjualan buah-buahan pada banyak sekali tempat dari tanggal 1 s.d 12 Desember.
Fungsi SUMPRODUCT Logika AND
Bagaimana cara mengetahui jumlah penjualan di tempat Ciamis , Berapa kali penjualannya dan Berapa rata-ratanya?
Disini kita menggunakan logika AND , yakni ada dua realita yang mesti sama sama benar (TRUE) yaitu:
  • Lokasi penjualan = Ciamis
  • Nama Buah = Mangga
Kedua hal tersebut mesti tercukupi selaku syarat kalkulasi.
Seperti halnya dalam rumus array CSE , logika AND dalam rumus SUMPRODUCT  juga sanggup dipenuhi menggunakan derma operator asterisk (*)
Perhatikan teladan rumus berikut dengan perkiraan baris terakhir data merupakan baris 13.
Berapa Jumlah Penjualan buah mangga di tempat  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 tempat 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 tempat 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 teladan cotoh rumus diatas , sudah sungguh terang bahwa  fungsi SUMPRODUCT sanggup mengambil alih fungsi SUMIFS , COUNTIFS dan AVERAGEIFS , walaupun selaku pengganti AVERAGEIFS , perlu rumus yang lebih panjang.
Supaya lebih fleksible , kita sanggup meletakkan persyaratan perkiraan dalam suatu sel
Misalnya 
Kriteria tempat penjualan disimpan di sel C16
Kriteria nama buah disimpan di sel C17
Maka rumus penjumlahan , frekuensi dan rata-rata sanggup dituliskan selaku 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 menjumlah rata-rata , alasannya merupakan merupakan hasil pembagian jumlah dengan frekuensi maka rumusnya sanggup menggunakan acuan lokasi rumus jumlah dan frekuensi. Perhatikan ilustrasi berikut:

Logika AND dalam Fungsi SUMPRODUCT

CONTOH2: Rumus SUMPRODUCT Dengan Logika OR

Logika OR akan bernilai benar jikalau variasi pernyataan memiliki minimal satu pernyataan yang bernilai benar (TRUE). 
Misalnya:
Untuk menjumlah berapa banyak penjualan buah mangga dan buah pisang pada tabel teladan sebelumnya.
Mangga dan pisang sama-sama terletak dalam satu kolom dan kedua nya mesti dipertimbangkan dalam proses kalkulasi.
Ini artinya kita mesti menciptakan rumus yang menciptakan nilai TRUE apabila salah satu keadaan terpenuhi.
Kondisi tersebut adalah:
  • Nama buah = “mangga” atau  “pisang”
Seperti halnya juga dalam rumus array , logika OR dalam rumus SUMPRODUCT sanggup dibantu menggunakan operator plus (+).
Perhatikan teladan rumus berikut:
Berapa kali (frekuensi) buah mangga dan  pisang yang terjual tanpa menatap 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 urusan , mungkin kita perlu memperhitungkan beberapa keadaan dengan logika AND dan OR sekaligus.

Baca Juga  Belajar Microsoft Excel: 3 Fitur Mutakhir Excel Menyebabkan Pekerjaan Lebih Mudah
Sayangnya bahkan dalam model excel modern pun , Satu fungsi SUMIFS dan COUNTIFS tidak sanggup menghandle urusan ini. Salah satu cara yang sanggup dilaksanakan jikalau tetap mau menggunakan kedua fungsi tersebut merupakan menggunakan variasi dua atau lebih dari fungsi SUMIFS +  SUMIFS , atau COUNTIFS+COUNTIFS

Untungnya hal ini sanggup dihandle oleh cuma Satu Fungsi SUMPRODUCT saja.
Melanjutkan teladan tabel penjualan buah-buahan sebelumnya , Misalnya kita ingin menjumlah berapa kali frekuensi penjualan dan nilai penjualan buah mangga dan buah pisang di tempat ciamis.

Perhatikan syarat yang mesti dipenuhi

  • Lokasi penjualan  : Ciamis
  • Nama Buah          : Mangga atau Pisang
Secara sederhana , persyaratan ini sanggup dituliskan dalam logika AND dan OR selaku berikut:

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

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

Mari kita lihat dalam teladan di bawah ini:

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

Cara1: Menggunakan rumus COUNTIFS , dikehendaki 2 fungsi:

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

Cara2: Menggunakan rumus SUMPRODUCT , cuma dikehendaki 1 fungsi:

=SUMPRODUCT((B2:B13=”ciamis”)*((C2:C13=”mangga”)+(C2:C13=”pisang”)))
Berapa Total nilai penjualan buah mangga dan pisang di tempat Ciamis?

Cara1: Menggunakan rumus SUMIFS , dikehendaki 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 , cuma dikehendaki 1 fungsi

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

Lebih jelasnya amati ilustrasi dalam screenshot dibawah ini:
Logika OR dan AND Dalam Fungsi SUMPRODUCT

Formula SUMPRODUCT Untuk Menghitung Berat Rata-Rata

Masih mengatakan dengan teladan buah-buahan. Kita sanggup menggunakan rumus SUMPRODUCT untuk menjumlah berat buah rata-rata tanpa mesti 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 menjumlah BJR jikalau dikenali data selaku berikut:

Menghitung Berat Rata-Rata Dengan Fungsi SUMPRODUCT
Tanpa menggunakan kolom bantu , bergotong-royong kita juga sanggup menghitungnya dengan rumus matematika biasa yaitu:

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

Namun rumus tersebut tidak efesien , khususnya jikalau dipakai pada baris data yang banyak.
Oleh karenanya diusulkan menggunakan SUMPRODUCT

Rumusnya seumpama ini:

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

Dan jikalau dipraktekkan dalam lembar excel menjadi

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

Cukup gampang bukan?

Atau bertele-tele?

Tergantung anda dan dari sudut mana anda memandangnya.

Rumus SUMPRODUCT Sebagai Alternative LOOKUP 2 Dimensi.

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

Contohnya:

Sebuah tabel data berisi penjualan buah-buahan per bulan.

  • Baris pertanda jenis buah
  • Kolom pertanda bulan per bulannya
  • Data berisi nilai penjualan buah.
Bagaimana rumus untuk mendapat nilai penjualan buah tertentu pada bulan tertentu.
Perhatikan teladan dalam prinscreen berikut:
Lookup 2 Dimensi Menggunakan Rumus SUMPRODUCT
Perhatikan bahwa jumlah penjualan pisang pada bulan Maret merupakan 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 sanggup dituliskan selaku berikut:

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

Dan agar 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 semoga sanggup difahami.

SUMPRODUCT selaku alternatif rumus array.

Jika kita sudah mengerti rumus array dan memperhatikan contoh-contoh rumus SUMPRODUCT dari permulaan pembahasan hingga belahan simpulan , kita sanggup menyaksikan bahwa ada banyak kiprah yang sanggup dituntaskan menggunakan fungsi SUMPRODUCT , juga sanggup dituntaskan menggunakan rumus array (CSE).

Supaya masih hangat dalam pikiran pembaca , saya menggunakan teladan rumus SUMPRODUCT yang terakhir dibahas yakni rumus alternative Lookup 2 dimensi.

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

Rumus tersebut ternyata akan menciptakan nilai yang serupa dengan rumus array berikut:

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

Terbukti….
Silahkan dicoba dengan rumus-rumus yang lainnya.

….

Demikian pembahasan perihal fungsi SUMPRODUCT. Dimulai dari pembahasan syntax dan basic penggunaanya , Dilanjutkan dengan pembahasan contoh-contoh rumus dan klarifikasi cara kerjanya. Hingga pada belahan simpulan , kita sanggup mengetahui bahwa rumus SUMPRODUCT ternyata sanggup menjadi alternatif rumus-rumus dan fungsi yang lain tergolong rumus array.

Tidak salah jikalau dibilang bahwa SUMPRODUCT selaku Rumus Multi Guna

Demikian biar bermanfaat.
Salam..
Artikel Terkait: