Pengguna SUMIF atau SUMIFS mungkin tahu bahwa kedua fungsi ini tidak bisa mengolah array.
Dengan demikian, rumus berikut tidak bisa dijalankan di Excel karena fungsi DAYS menghasilkan array.
=SUMIF(DAYS(C2:C6; B2:B6); ">14"; D2:D6)
Masalah di atas bisa diselesaikan dengan rumus SUMPRODUCT pada Excel, seperti yang akan kita pelajari di artikel kali ini.
File: fungsi-sumproduct-excel.xlsx.
Apakah Fungsi SUMPRODUCT Pada Excel Itu?
Fungsi SUMPRODUCT pada Excel adalah fungsi yang menjumlahkan angka pada suatu array atau range berdasarkan array atau range yang berkesesuaian.
Sintaks
SUMPRODUCT(array1; [array2]; [array3]; …)
- array1 (wajib) adalah array atau range pertama yang anggota-anggotanya akan dijumlahkan.
- array2, array3, sampai array255 adalah array-array atau range-range yang anggota-anggotanya dikalikan dengan array1, array2, sampai array254, lalu dijumlahkan.
Secara default, operasi pada fungsi SUMPRODUCT menggunakan operasi perkalian antar array. Excel memungkinkan operasi lain seperti penjumlahan atau pembagian dengan mengganti pemisah daftar dengan operator seperti tanda tambah (+).
SUMPRODUCT akan menjumlahkan array terakhir dari operasi apa pun yang terjadi di antara array1, array2, dan seterusnya.
Mengenai Rumus SUMPRODUCT Pada Excel
- Array-array harus memiliki dimensi yang sama. Jika tidak, SUMPRODUCT mengembalikan pesan kesalahan #VALUE!. Dimensi sama artinya setiap array harus sama lebar dan tinggi.
- Entri bukan angka akan diperlakukan sebagai nol oleh SUMPRODUCT.
- Berhubung sifat dasar SUMPRODUCT adalah perkalian, semakin banyak baris atau kolom yang diolah, semakin berat kerja Excel.
Cara Menggunakan Rumus SUMPRODUCT Excel
Seperti yang kita ketahui, rumus SUMPRODUCT pada Excel menerima paling tidak satu parameter array. Dengan menggunakan satu parameter tersebut, kita membuat rumus SUMPRODUCT dalam bentuk paling dasar.
=SUMPRODUCT({7, 4, 3, 5, 3})
Rumus di atas mengembalikan 15 yang merupakan penjumlahan dari 7, 4, 3, 5, dan 3.
Bila menggunakan range, rumus akan terlihat seperti di bawah. Rumus menjumlahkan semua nilai pada range B2:B6.
=SUMPRODUCT(B2:B6)
Bila menggunakan dua array/range atau lebih, maka rumus akan tampak seperti berikut.
=SUMPRODUCT({7, 4, 3, 5, 3}; {5000, 5000, 10000, 8000, 5000})
=SUMPRODUCT(B2:B6; C2:C6)
Rumus di atas menghasilkan nilai 140,000. Angka ini didapat dari perhitungan berikut.
=(7*5000)+(4*5000)+(3*10000)+(5*8000)+(3*5000)
Ingat, secara default, operasi antara array di SUMPRODUCT adalah perkalian, lalu penjumlahan.
Rumus SUMPRODUCT dengan Kriteria
Ada beberapa cara untuk menggunakan rumus SUMPRODUCT dengan kriteria, salah satunya seperti rumus berikut.
=SUMPRODUCT((A2:A11=F2)*C2:C11)
Rumus di atas mencari Eko (F2) pada range A2:A11. Bagi baris-baris yang bernilai Eko, array akan memiliki nilai TRUE, sebaliknya bernilai FALSE. Array ini kemudian dikalikan dengan range C2:C11. Dalam operasi aritmetika, Excel akan mengubah nilai Boolean TRUE menjadi 1 dan FALSE menjadi 0. Hasil perkalian ini menjadi array terakhir yang semua anggotanya dijumlahkan dan mendapatkan 10,390,000.
Perhatian: Perhatikan penggunaan tanda kurung bila menggunakan rumus SUMPRODUCT dengan kriteria, terutama bila ada lebih dari satu kriteria.
Hasil rumus bisa dilihat pada gambar berikut.
Rumus di atas dapat ditulis dengan cara yang berbeda, yaitu:
=SUMPRODUCT((A2:A11=F2)*1; C2:C11)
=SUMPRODUCT(--(A2:A11=F2); C2:C11)
Perkalian dengan 1 dan operator unari (dua minus) memaksa nilai-nilai Boolean hasil A2:A11=F2 menjadi 1 atau 0. Nilai-nilai ini yang kemudian dikalikan dengan nilai-nilai pada C2:C11.
Catatan: Bila teman-teman mencoba
=SUMPRODUCT((A2:A11=F2); C2:C11), rumus ini mengembalikan nol. Excel mengubah TRUE dan FALSE menjadi 1 dan 0 untuk operasi arimetika yang dilakukan oleh pengguna sendiri. Tapi, untuk alasan yang kami belum tahu, konversi ini tidak berlaku bila operasi aritmetika ini terjadi di belakang layar.
Rumus SUMPRODUCT dengan Banyak Kriteria
Sebelum melanjutkan ke bagian ini, kami harap teman-teman paham penggunaan rumus SUMPRODUCT dengan satu kriteria.
Dalam kondisi di mana rumus menggunakan dua kriteria atau lebih, semua kriteria tersebut tentunya harus digabungkan.
Ada tiga kondisi penggabungan yang mungkin, yaitu penggabungan dengan ekspresi And, Or, dan gabungan keduanya.
Kriteria dengan Kondisi And
Kondisi And pada rumus SUMPRODUCT dicapai dengan menggunakan operasi perkalian.
=SUMPRODUCT((A2:A11=F2)*(B2:B11=F3)*(C2:C11))
Rumus di bawah mencari total (C2:C11) penjualan tanah (B2:B11=F3) oleh Eko (A2:A11=F2).
Ingat bahwa baik B2:B11=F3 dan A2:A11=F2 masing-masing akan menghasilkan array berupa Boolean. Dalam operasi aritmetika, Excel akan mengubah setiap TRUE dan FALSE menjadi 1 dan 0. Hasil perkalian ini kemudian dikalikan lagi dengan nilai-nilai pada C2:C11.
Rumus mengembalikan 4,960,000 yang merupakan total penjualan tanah oleh Eko.
Berhubung operasi SUMPRODUCT secara default adalah perkalian, kita dapat menulis rumus di atas dengan cara lain, yaitu:
=SUMPRODUCT((A2:A11=F2)*(B2:B11=F3); (C2:C11))
=SUMPRODUCT(--(A2:A11=F2); --(B2:B11=F3); (C2:C11))
Mengapa perkalian untuk kondisi And? Karena perkalian apa pun akan menghasilkan nol bila salah satu saja komponen bernilai nol. Sama dengan kriteria And, kan?
Kriteria dengan Kondisi Or
Kriteria dengan kondisi Or dicapai dengan penjumlahan. Bila satu saja ekspresi (kriteria) bernilai satu (atau lebih besar), maka keseluruhan operasi bernilai benar. Bila semua ekspresi bernilai nol, maka keseluruhan penjumlahan juga bernilai nol atau FALSE.
=SUMPRODUCT((A2:A11=F2)+(A2:A11=F3); C2:C11)
Rumus di atas mencari penjualan (C2:C11) Eko ((A2:A11=F2)) atau Jenny ((A2:A11=F3)). Setelah hasil kriteria (berupa array 1 atau 0) diketahui, Excel mengalikan array ini dengan array penjualan C2:C11 dan menambahkan setiap baris-barisnya.
Tips: Bila teman-teman bingung pecah rumus dan jalankan tiap bagian sebagai rumus tersendiri, seperti yang kami lakukan pada Gambar 03.
Kriteria dengan Kondisi And dan Or
Kondisi And dan Or dapat digabungkan di dalam satu rumus.
Saat menggunakan rumus gabungan seperti ini, perhatikan penggunaan tanda kurung.
=SUMPRODUCT(((A2:A11=F2)+(A2:A11=F3))*(B2:B11=F4); C2:C11)
Rumus di atas mencari penjual-penjual Eko atau Jenny ((A2:A11=F2)+(A2:A11=F3)). Bagian rumus ini mencari kedua penjual tersebut. Hasilnya lalu dikalikan (operasi And) dengan evaluasi terhadap unit tanah (B2:B11=F4). Hasil akhir semua evaluasi dikalikan lagi dengan C2:C11. Array terakhir dari semua perkalian ini kemudian dijumlahkan dan mendapatkan 12,360,000 sebagai hasil.
