Cara menggunakan sumproduct di excel

Ringkasan

Fungsi SUMPRODUCT Excel mengalikan jarak atau array bersamaan dan memunculkan jumlah dari produk. Terdengar membosankan, tetapi rumus SUMPRODUCT adalah fungsi yang sangat luar biasa, untuk menghitung dan menjumlahkan seperti COUNTIFS atau SUMIFS, tetapi dengan banyak penyesuaian. Fungsi lain bisa digunakan dengan mudah di dalam SUMPRODUCT untuk memperluas fungsionalitas lebih jauh.

Tujuan

Mengalikan, kemudian menjumlahkan array.

Nilai Kembalian

Hasil dari perkalian dan penjumlahan array.

Syntax

=SUMPRODUCT(array1,[array2],…)

Uraian

Array1 – array atau jarak yang pertama yang ingin dikalikan, kemudian ditambahkan.

Array2 – [pilihan] arra atau jarak kedua yang ingin dikalikan, kemudian ditambahkan.

Catatan Penggunaan

Fungsi SUMPRODUCT bekerja dengan array, tetapi tidak memerlukan syntax array normal (Ctrl + Shift + Enter) untuk dimasukkan. Tujuan dari fungsi SUMPRODUCT adalah mengalikan, kemudian menjumlahkan, array. Jika hanya satu array yang tersedia, SUMPRODUCT akan menjumlahkan item di dalam array. Sampai 30 array bisa disediakan.

Ketika anda pertama kali menemukan SUMPRODUCT, ini akan terlihat membosankan, kompleks, dan bahkan tidak berarti. Tetapi SUMPRODUCT adalah fungsi yang sangat luar biasa dengan banyak kegunaan. Karena ini akan menangani array dengan anggun tanpa keluhan, anda bisa menggunakan ini untuk memproses jarak dari sel dengan cara yang bagus (lihat tautan contoh rumus di halaman).

Untuk mengilustrasikan bagaimana SUMPRODUCT bekerja, di sini ada beberapa contoh umum.

SUMPRODUCT untuk sums dan counts bersyarat

Anggap anda memiliki beberapa susunan data di A2:B6, dengan State di dalam kolom A, Sales di dalam kolom B:

A                     B

1          State                Sales

2          UT                   75

3          CO                  100

4          TX                   125

5          CO                  125

6          TX                   150

Menggunakan SUMPRODUCT, anda bisa menghitung total penjualan untuk Texas (“TX”) dengan rumus ini:

=SUMPRODUCT(A2:A6=”TX”))

Dan anda bisa menjumlahkan total penjualan untuk Texas (“TX”) dengan rumus ini:

=SUMPRODUCT(- -(A2:A6=”TX”),B2:B6)

Catatan: jangan bingung dengan negatif ganda. Ini adalah cara umum digunakan di dalam rumus Excel yang lebih tinggi untuk memaksa nilai BENAR atau SALAH ke dalam 1 dan 0. Lihat ke bawah lebih lanjut…

Untuk contoh penjumlahan di atas, di sini ada perwakilan virtual dari dua array sebagai yang diproses pertama oleh SUMPRODUCT:

Array1             array2

SALAH                      75

SALAH                      100

BENAR                      125

SALAH                      125

BENAR                      150

Setiap array memiliki 5 item. Array yang pertama berisi nilai BENAR/SALAH yang berasal dari pernyataan A2:A6=”TX”, dan array yang kedua adalah isi dari B2:B6. Setiap item pada array yang pertama akan dikalikan dengan penyesuaian item di array yang kedua. Bagaimanapun juga, pada kondisi saat ini, hasil dari SUMPRODUCT akan menjadi nol karena nilai BENAR dan SALAH  akan diperlakukan sebagai nol. Kita membutuhkan item di array1 menjadi numerik – mereka harus “dipaksa” ke dalam satu dan nol. Disini lah darimana negatif ganda berasal.

Dengan menggunakan negatif ganda – – (unary ganda, untuk jenis teknikal) kita dapat memaksa BENAR/SALAH menjadi nilai numerik satu atau nol, seperti yang ditunjukkan pada perwakilan virtual di bawah ini. Kolom terakhir “Produk” mewakili hasil dari perkalian dua array bersamaan. Hasil penjumlahan, 275, adalah nilai yang dimunculkan oleh SUMPRODUCT.

Array1             array2              Produk

0          *          75        =          0

0          *          100      =          0

1          *          125      =          125

0          *          125      =          0

1          *          150      =          150

Jumlah           275

Gunakan kurung kurawal syntax untuk array, rumus nya terlihat seperti ini setelah pemaksaan:

=SUMPRODUCT({0,0,1,0,1},{75,100,125,125,150})

Dan seperti ini setelah perkalian:

=SUMPRODUCT({0,0,125,0,150})

SUMPRODUCT dengan fungsi lain

SUMPRODUCT bisa menggunakan fungsi lain secara langsung. Anda bisa melihat SUMPRODUCT digunakan dengan fungsi LEN untuk menghitung total karakter di dalam jarak, atau dengan fungsi ISBLANK, ISTEXT, dll. tidak ada fungsi array secara normal, tetapi ketika mereka diberikan jarak, mereka akan membuat “hasil array”. Karena SUMPRODUCT dibentuk untuk bekerja dengan array, ini dapat menunjukkan perhitungan pada array secara langsung. Ini dapat menjadi cara yang bagus untuk menghemat lembar dalam lembat kerja, dengan mengurangi kolom “bantuan” yang dibutuhkan.

Contohnya, anggap anda memiliki 10 nilai teks yang berbeda di A1:A10 dan anda mau untuk menghitung total karakter untuk semua nilai 10. Anda bisa menambahkan kolom bantuan di dalam kolom B yang memakai rumus ini :LEN(A1) untuk menghitung karakter pada setiap sel.

Kemudian anda dapat menggunakan SUM untuk menambahkan semua angka 10. Namun, menggunakan SUMPRODUCT, anda bisa menulis rumus seperti ini:

=SUMPRODUCT(LEN(A1:A10))

Ketikan digunakan dengan jarak seperti A1:A10, LEN akan memunculkan array dari nilai 10. Kemudian SUMPRODUCT dengan sederhana akan menjumlahkan semua nilai dan memunculkan hasil, tanpa kolom bantuan yang dibutuhkan.

Lihat contoh di bawah banyak cara lain untuk menggunakan SUMPRODUCT.

Catatan

1. SUMPRODUCT memperlakukan item yang bukan numerik pada array sebagai nol.
2. Argumen array harus ukuran yang sama. Jika tidak, SUMPRODUCT akan menghasilkan nilai kesalahan VALUE!
3. Tes logical di dalam array akan menciptakan nilai BENAR dan SALAH. Dalam banyak kasus, anda akan memaksa mereka menjadi 1 dan 0.
4. SUMPRODUCT sering menggunakan hasil dari fungsi lain secara langsung.

Rumus sumproduct untuk apa?

Rumus apa saja yang ada di excel?

Bagaimana cara menggunakan rumus SUM?

Perkalian excel rumusnya apa?