Serba Serbi SUMPRODUCT

Hari ini saya mencoba posting topik tentang Fungsi SUMPRODUCT, kelebihan Fungsi ini adalah SUMPRODUCT dapat meng-handel Array tanpa kita menekan tombol CSE (CTRL-SHIFT-ENTER), hanya dengan tekan ENTER saja maka Fungsi ini akan melakukan kalkulasi. Kelebihan yang lainnya dibanding Fungsi SUM adalah, SUMPRODUCT dapat mengkalkulasi suatu data berdasarkan multi kriteria.

Contoh 1:

Rumus Alternatif:
=SUM((B2:B4)*(C2:C4))


Contoh 2:

Pada contoh diatas SUMPRODUCT melakukan kalkulasi berdasarkan 1 kriteria yaitu "Jawa" yang muncul sebanyak 2 kali pada Tabel, coba perhatikan rumusnya, disana ditambah 0, apa maksudnya?

Saya akan jelaskan langkah demi langkah, cobalah anda blok range B11:B15=B17 sesuai contoh diatas, kemudian tekanlah tombol F9 untuk mengetahui kalkulasi yang dilakukan oleh Excel.

Excel melakukan kalkulasi berupa kode Boolean yaitu True dan False, langkah selanjutnya bloklah seperti contoh dibawah

Kemudian tekanlah F9, apa yang akan terjadi yaaa........?

So dengan fungsi ditambah dengan 0 adalah merubah kode Boolean "True dan :False" menjadi kode 1 dan 0, dengan demikian SUMPRODUCT dapat melakukan kalkulasi data sesuai kriteria tertentu.

Ada beberapa cara untuk merubah kode Boolean menjadi kode 1 dan 0, yaitu:
1. Dengan karakter dobel negatif "--"
2. Dengan ditambah angka 0
3. Dikurangi dengan angka 0
4. Dengan dikalikan angka 1
5. Atau dibagi angka 1

Saya akan beri contoh penggunaan masing2xnya.

Rumus Alternatif:
=SUM(--(B11:B15=B17)) --> Rumus Array
=SUM((B11:B15=B17)*1) --> Rumus Array
=SUM((B11:B15=B17)+0) --> Rumus Array
=COUNTIFS(B11:B15;B17)
=COUNTIF(B11:B15;B17)
=SUMPRODUCT(--(B11:B15=B17))
=SUMPRODUCT((B11:B15=B17)/1)
=SUMPRODUCT((B11:B15=B17)*1)
=SUMPRODUCT((B11:B15=B17)-0)


Contoh 3:

Pada contoh diatas SUMPRODUCT melakukan kalkulasi sesuai 2 kriteria "Jawa" dan "Yogyakarta", dan disana ada tanda dobel negatif pada  --(B22:B28=B30) dan --(C22:C28=C30), inilah contoh merubah kode Boolean menjadi angka 1 dan 0 sesuai keterangan saya diatas.

Rumus Alternatif:
=SUMIFS(D22:D28;B22:B28;B30;C22:C28;C30)
=SUM((B22:B28=B30)*(C22:C28=C30)*(D22:D28)) --> Rumus Array
=SUMPRODUCT((B22:B28=B30)*(C22:C28=C30);(D22:D28))
=SUMPRODUCT((B22:B28=B30)*(C22:C28=C30)*(D22:D28))
=SUMPRODUCT(--(B22:B28=B30);--(C22:C28=C30);--(D22:D28))


Contoh 4:

Rumus Alternatif:
=SUM((B35:B41=B43)*(D35:D41))+SUM((C35:C41=C43)*(D35:D41)) --> Rumus Array
=SUMIFS(D35:D41;B35:B41;B43)+SUMIFS(D35:D41;C35:C41;C43)
=SUMIF(B35:B41;B43;D35:D41)+SUMIF(C35:C41;C43;D35:D41)


Contoh 5:

Contoh diatas merupakan variasi formula SUMPRODUCT digunakan melakukan kalkulasi berdasarkan 2 kriteria.

Rumus Alternatif:
=SUM((B47:B52>=C54)*(B47:B52<=C55)*(C47:C52)) --> Rumus Array
=SUM(IF(B47:B52>=C54;IF(B47:B52<=C55;C47:C52;0);0)) --> Rumus Array
=SUMIFS(C47:C52;B47:B52;">="&C54;B47:B52;"<="&C55)
=SUMIF(B47:B52;">="&C54;C47:C52)-SUMIF(B47:B52;">"&C55;C47:C52)


Contoh 6:

Contoh 7:

Contoh disini SUMPRODUCT melakukan perhitungan berdasarkan 4 kriteria dipadukan dengan COUNTIF.

Berlatihlah menggunakan contoh diatas supaya anda semakin mahir menggunakan fungsi ini.

Download File Latihan