SUMPRODUCT di Excel

Fungsi SUMPRODUCT di Excel

Fungsi excel SUMPRODUCT mengalikan jumlah dua atau lebih larik dan menjumlahkan produk yang dihasilkan. Array adalah rentang sel yang disediakan sebagai argumen untuk fungsi SUMPRODUCT. Produk adalah hasil perkalian dua bilangan.

Misalnya, lembar kerja terdiri dari nilai numerik di kolom A dan B. Nilai tersebut dicantumkan sebagai berikut:

Kolom A

• Sel A1 berisi 3
• Sel A2 berisi 4
• Sel A3 berisi 5

Kolom B

• Sel B1 berisi 2
• Sel B2 berisi 6
• Sel B3 berisi 1

Rumus ‘=SUMPRODUCT(A1:A3,B1:B3)’ mengembalikan 35. Rumus yang diberikan telah menghitung output sebagai (3*2)+(4*6)+(5*1)=35. Oleh karena itu, SUMPRODUCT mengalikan masing-masing nilai dari larik yang ditentukan dan merangkum produk yang dihasilkan.

Seandainya hanya ada satu rentang, A1:A3, rumus ‘=SUMPRODUCT(A1:A3)’ akan mengembalikan 12. Ini karena, dalam kasus satu larik, fungsi excel SUMPRODUCT mengembalikan jumlah nilai dari satu larik Himpunan.

Meskipun SUMPRODUCT bekerja pada array, tidak memerlukan pintasan CSE (Ctrl+Shift+Enter) untuk beroperasi. Sebaliknya, tombol ‘Enter’ ditekan untuk melakukan perhitungan.

Fungsi SUMPRODUCT dikategorikan dalam fungsi Matematika dan Trigonometri Excel. Untuk memperluas fungsionalitas fungsi SUMPRODUCT, dapat digabungkan dengan beberapa fungsi Excel lainnya.

Sintaks Rumus SUMPRODUCT di Excel

Sintaks fungsi excel SUMPRODUCT ditunjukkan pada gambar berikut:

Fungsi SUMPRODUCT menerima argumen berikut:

• array1: Ini adalah larik (rentang) pertama yang nilainya akan dikalikan dan kemudian ditambahkan.
• array2: Ini adalah larik (rentang) kedua yang nilainya akan dikalikan dan kemudian ditambahkan.
• array3: Ini adalah array (rentang) ketiga yang nilainya akan dikalikan dan kemudian ditambahkan.

Argumen ‘array1’ adalah wajib. Argumen ‘array2’, ‘array3’, dan array berikutnya bersifat opsional.

Catatan: Secara default, fungsi SUMPRODUCT di excel mengalikan angka. Namun, itu juga dapat melakukan penambahan, pengurangan, dan pembagian. Untuk operasi aritmetika seperti itu, tempatkan operator yang relevan seperti ‘plus’ (+), ‘minus’ (-), atau ‘forward slash’ (/) di antara argumen array yang diberikan ke fungsi SUMPRODUCT.

Contoh Fungsi Sumproduct Excel

Mari kita perhatikan beberapa contoh untuk memahami cara kerja fungsi SUMPRODUCT Excel.

Contoh #1–Perkalian dan Tambahkan Angka

Gambar berikutnya menunjukkan harga (dalam $ di kolom C) dan jumlah unit yang terjual (kolom B) dari 10 produk. Hitung total pendapatan penjualanPendapatan PenjualanPendapatan penjualan mengacu pada pendapatan yang dihasilkan oleh entitas bisnis mana pun dengan menjual barangnya atau menyediakan layanannya selama operasi normalnya. Dilaporkan setiap tahun, triwulanan, atau bulanan sesuai kasus dalam laporan laba rugi/laporan laba rugi entitas bisnis.baca lebih lanjut dihasilkan oleh produk A hingga J.

Langkah 1: Masukkan rumus berikut di sel B15.

‘=SUMPRODUCT($B$5:$B$14,$C$5:$C$14)’

Langkah 2: Tekan tombol ‘Enter’. Keluarannya adalah $200.600. Oleh karena itu, total pendapatan penjualan yang dihasilkan oleh produk tertentu (A ke J) adalah $200.600.

Untuk mempermudah, kami telah menampilkan rumus di sel B15 dan memberikan hasilnya di sel C15.

Catatan: Absolute referencesAbsolute ReferencesAbsolute references di excel adalah jenis referensi sel dimana sel yang dirujuk tidak berubah, seperti yang terjadi pada referensi relatif. Dengan menekan f4, kita dapat membuat formula untuk referensi absolut. Baca lebih lanjut (ditunjukkan dengan simbol dolar) pada formula SUMPRODUCT pastikan formula tidak berubah saat disalin.

Penjelasan: Rumus SUMPRODUCT yang diberikan mengalikan nilai kolom B dengan nilai kolom C yang berkorespondensi. Setelah itu, produk yang diperoleh dijumlahkan.

Rumus SUMPRODUCT (dimasukkan pada langkah 1) berfungsi sebagai berikut:

(20*1300)+(10*1500)+(17*1000)+(14*1300)+(15*1700)+(14*1200)+(15*1100)+(18*1300)+(16 *1500)+(13*1400)

=26000+15000+17000+18200+25500+16800+16500+23400+24000+18200

=200600

Oleh karena itu, keluaran rumus SUMPRODUCT adalah $200.600.

Contoh #2 – Kalikan dan Tambahkan Berdasarkan Kriteria (Kondisi)

Gambar berikutnya menunjukkan hasil penjualan (dalam $ pada kolom D) yang dihasilkan oleh berbagai wilayah (kolom C) selama dua belas bulan. Hitung total pendapatan penjualan yang dihasilkan oleh wilayah Barat untuk periode tertentu.

Langkah 1: Masukkan rumus berikut di sel C32.

‘=SUMPRODUCT(–($C$20:$C$31=”Barat”),$D$20:$D$31)’

Langkah 2: Tekan tombol ‘Enter’. Keluarannya adalah $1.500. Oleh karena itu, total pendapatan penjualan yang dihasilkan oleh wilayah Barat untuk periode dua belas bulan adalah $1.500.

Untuk mempermudah, rumus SUMPRODUCT ditampilkan di sel C32 dan hasilnya diberikan di sel D32.

Penjelasan: Rumus $C$20:$C$31=”West” mengembalikan serangkaian nilai logika, benar atau salah. Mengembalikan nilai true untuk semua sel (dari rentang C20:C31) yang berisi ‘Barat’. Untuk sel yang berisi nilai selain ‘Barat’ (seperti Utara atau Selatan), rumus mengembalikan salah.

Operator unary atau simbol negatif ganda (–) mengubah nilai benar dan salah masing-masing menjadi 1 dan 0. Seandainya kita tidak menggunakan operator unary, fungsi SUMPRODUCT di excel akan memperlakukan nilai non-numerik (kolom C) sebagai nol.

Larik pertama fungsi SUMPRODUCT dihasilkan dari rumus $C$20:$C$31=”Barat”. Array ini hanya terdiri dari satu (1) dan nol (0). Array kedua dari fungsi SUMPRODUCT adalah $D$20:$D$31.

Karena semua sel (dari rentang C20:C31) yang berisi ‘Barat’ bernilai 1, fungsi excel SUMPRODUCT berfungsi sebagai berikut:

(1*100)+(0*500)+(0*300)+(1*400)+(0*100)+(1*500)+(0*300)+(0*400)+(1 *100)+(0*500)+(0*300)+(1*400)

=100+0+0+400+0+500+0+0+100+0+0+400

=1500

Oleh karena itu, hasil dari rumus SUMPRODUCT adalah $1.500.

Contoh #3–Mutliply dan Add untuk Menemukan Rata-Rata Tertimbang

Gambar berikutnya menunjukkan persentase penyelesaian (kolom C) dari 10 proyek organisasi. Dengan persentase ini, seseorang dapat mengetahui sejauh mana proyek masing-masing telah diselesaikan. Peringkat prioritas (kolom D) adalah bobot yang diberikan pada suatu proyek.

Hitung rata-rata tertimbang dari proyek yang diberikan dengan bantuan fungsi excel SUMPRODUCT.

Langkah 1: Masukkan rumus SUMPRODUCT berikut.

‘=SUMPRODUK(C36:C46,D36:D46)/SUM(D36:D46)’

Langkah 2: Tekan tombol ‘Enter’. Outputnya adalah 55,8%. Oleh karena itu, rata-rata tertimbang adalah 55,8%.

Penjelasan: Untuk menghitung rata-rata tertimbang, perhitungan berikut dilakukan dalam urutan yang diberikan:

1. Persentase penyelesaian (C36:C46) dikalikan dengan bobot (D36:D46).
2. Produk yang dihasilkan (diperoleh pada langkah a) ditambahkan.
3. Jawaban yang diperoleh (pada langkah b) dibagi dengan jumlah bobot [SUM(D36:D46)].

Rumus SUMPRODUCT (dimasukkan pada langkah 1) berfungsi sebagai berikut:

[(0*0)+(0,5*2)+(0,6*3)+(0,65*1)+(0,4*1)+(0,5*2)+(0,6*4)+(0,5*1)+( 0,6*4)+(0,65*4)+(0,4*3)]/(0+2+3+1+1+2+4+1+4+4+3)

=(0+1+1,8+0,65+0,4+1+2,4+0,5+2,4+2,6+1,2)/25

=13,95/25

=0,558 atau 55,8%

Oleh karena itu, rata-rata tertimbang adalah 55,8%.

Catatan: Nilai non-numerik dalam sel C36 dan D36 diperlakukan sebagai nol dengan rumus SUMPRODUCT yang diberikan.

Contoh #4 – Kalikan dan Tambahkan Berdasarkan Berbagai Kriteria (Kondisi)

Gambar berikutnya menunjukkan pendapatan penjualan (dalam $ di kolom D) yang dihasilkan oleh karyawan dari berbagai daerah (kolom C). Pada setiap penjualan, persentase komisi (kolom E) diterima oleh karyawan.

Selanjutnya, karyawan bekerja dalam dua shift, shift siang dan shift malam. Seluruh kumpulan data terkait dengan bulan Januari pada tahun tertentu.

Hitung total komisi (dalam $) yang dibayarkan kepada karyawan wilayah Barat yang bekerja dalam shift siang.

Langkah 1: Masukkan rumus berikut di sel C65.

‘=SUMPRODUCT((D53:D64*E53:E64)*(B53:B64=”Pergeseran hari”)*(C53:C64=”Barat”))’

Langkah 2: Tekan tombol ‘Enter’. Outputnya adalah $10.

Penjelasan: Rumus B53:B64=”Pergeseran hari” membantu menemukan sel (dalam rentang B53:B64) yang berisi kata ‘pergeseran hari.’ Jika sel kolom B berisi ‘pergeseran hari’, hasilnya benar. Kalau tidak, hasilnya salah.

Demikian pula, rumus C53:C64=”West” mengembalikan benar atau salah bergantung pada nilai sel dalam rentang yang diberikan (C53:C64). Mengembalikan nilai true untuk semua sel kolom C yang berisi ‘Barat’. Jika sel berisi nilai selain ‘Barat’ (seperti Utara atau Selatan), rumus ini mengembalikan salah.

Karena tidak ada operator unary (–) sebelum rumus ini, nilai benar dan salah tidak diubah menjadi digit biner (0 dan 1).

Rumus SUMPRODUCT (dimasukkan pada langkah 1) terdiri dari beberapa kriteria. Kriteria untuk kolom B dan C masing-masing adalah ‘pergeseran hari’ dan ‘Barat’. Dari dataset yang diberikan, hanya baris 53 dan 61 yang memenuhi kriteria tersebut.

Oleh karena itu, rumus excel SUMPRODUCT mengalikan dan menjumlahkan nilai kolom D dan E yang sesuai hanya untuk baris 53 dan 61.

Baris 53 masing-masing berisi 100 dan 5% di sel D53 dan E53. Demikian pula, baris 61 juga berisi 100 dan 5% masing-masing di sel D61 dan E61. Angka-angka ini dikalikan dan kemudian ditambahkan.

Rumus SUMPRODUCT berfungsi sebagai berikut:

(100*5%)+(100*5%)

=10

Oleh karena itu, karyawan yang bekerja dalam shift siang dan berasal dari wilayah Barat dibayar total komisi sebesar $10.

Properti dari Fungsi SUMPRODUCT Excel

Fitur fungsi excel SUMPRODUCT tercantum sebagai berikut:

• Jumlah minimum larik yang akan diberikan ke fungsi SUMPRODUCT adalah 1. Jika satu larik disediakan, fungsi mengembalikan jumlah semua item dalam larik ini.
• Jumlah maksimum larik yang dapat ditentukan adalah 255 di Excel 2007 dan versi yang lebih baru. Di versi Excel yang lebih lama, batas maksimum array ditetapkan pada 30.
• Semua argumen array harus memiliki jumlah baris dan kolom yang sama. Jika tidak, fungsi SUMPRODUCT mengembalikan #VALUE error#VALUE Error#VALUE! Kesalahan di Excel menyatakan bahwa sel referensi yang telah dimasukkan pengguna dengan rumus yang salah atau menggunakan tipe data yang salah (kebanyakan data numerik). Terkadang, sulit untuk mengidentifikasi jenis kesalahan di balik kesalahan ini.baca lebih lanjut.
• Fungsi excel SUMPRODUCT memperlakukan entri non-numerik dari array sebagai nol.
• Tes logika Tes logika di Excel menghasilkan output analitik, benar atau salah. Setara dengan operator, ‘=,’ adalah tes logis yang paling umum digunakan.Baca lebih lanjut dalam array mengevaluasi nilai Boolean, benar atau salah. Nilai-nilai ini dapat dikonversi menjadi 1 dan 0 dengan bantuan operator unary (–).

Catatan: SUMPRODUCT dapat digunakan sebagai fungsi VBAFungsi VBAFungsi VBA melayani tujuan utama untuk melakukan perhitungan tertentu dan mengembalikan nilai. Oleh karena itu, di VBA, kami menggunakan sintaks untuk menentukan parameter dan tipe data saat mendefinisikan fungsi. Fungsi semacam itu disebut fungsi yang ditentukan pengguna.Baca lebih lanjut. Misalnya, kode dapat dibuat sebagai berikut:

Sub ABC()
MsgBox Evaluate(“=SUMPRODUCT(($A$1:$A$5=””Tanuj””)*($B$1:$B$5))”)End Sub

Nilai larik A1:A5 yang bernilai ‘Tanuj’ dikalikan dengan nilai larik B1:B5 yang sesuai. Produk yang dihasilkan kemudian dijumlahkan.

Pertanyaan yang Sering Diajukan

1. Tentukan fungsi SUMPRODUCT dari Excel.

Fungsi SUMPRODUCT mengalikan nilai numerik dari dua larik atau lebih. Produk yang dihasilkan kemudian ditambahkan. Selanjutnya, adalah mungkin untuk menentukan satu atau lebih kondisi (kriteria) berdasarkan fungsi yang mengembalikan output.

Selain perkalian, fungsi excle SUMPRODUCT juga dapat melakukan penjumlahan, pengurangan dan pembagian.

Sintaks fungsi excel SUMPRODUCT dinyatakan sebagai berikut:

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

Argumen ‘array1,’ ‘array2,’ dan ‘array3’ adalah rentang nilai yang harus dikalikan lalu dijumlahkan. ‘Array1’ adalah wajib, sedangkan argumen array berikutnya bersifat opsional.

Dalam versi modern n Excel, minimal 1 dan maksimal 255 larik dapat diberikan ke fungsi SUMPRODUCT. Dalam kasus array tunggal, fungsi mengembalikan jumlah rentang yang disediakan.

2. Bagaimana cara kerja fungsi excel SUMPRODUCT dan kapan digunakan di Excel?

Fungsi SUMPRODUCT di excel berfungsi sebagai berikut:

a. Array, dipisahkan dengan koma, dipasok ke fungsi.
b. Fungsi mengevaluasi kondisi, jika ada.
c. Setelah tombol ‘Enter’ ditekan, fungsi melakukan perhitungan dan mengembalikan output numerik.

Fungsi SUMPRODUCT digunakan dalam situasi berikut:

a. Ini digunakan ketika dua angka atau lebih perlu dikalikan dan ditambahkan dengan satu rumus.
b. Ini digunakan untuk menghitung nilai yang memenuhi kriteria yang ditentukan.
c. Ini digunakan untuk menghitung rata-rata tertimbang.
d. Ini digunakan ketika operasi perkalian dan penjumlahan perlu dilakukan setelah satu atau lebih kondisi terpenuhi.
e. Ini digunakan ketika operasi aritmatika yang berbeda seperti penjumlahan, pembagian, dan pengurangan harus dilakukan.

Catatan: Untuk melakukan penjumlahan, pembagian, dan pengurangan, tempatkan masing-masing operator seperti ‘+,’ ‘/’ atau ‘-‘ di antara argumen larik yang diberikan ke rumus SUMPRODUCT Excel.

Misalnya, rumus ‘=SUMPRODUCT(A2:A5- B2:B5) mengurangkan angka dalam rentang pertama (A2:A5) dari angka dalam rentang kedua (B2:B5). Perbedaan yang diperoleh kemudian ditambahkan.

3. Bagaimana cara menggunakan fungsi SUMPRODUCT dengan berbagai kondisi?

Ketika beberapa kondisi dimasukkan dalam fungsi excel SUMPRODUCT, fungsi ini akan mengevaluasi setiap kondisi sebelum menampilkan output. Fungsi SUMPRODUCT bekerja dengan logika AND dan OR.

Misalnya, seorang pembuat kue di Amerika Serikat ingin mengetahui jumlah pembeli di Chicago dan Houston. Untuk ini, dataset yang terdiri dari dua rasa kue (nanas dan coklat) dipelajari. Lembar kerja berisi entri berikut:

Teks ‘rasa’ ditampilkan di sel A1. Daftar ini berisi item berikut:

• Sel A2, A5, A6, dan A7 berisi Nanas.
• Sel A3 dan A4 berisi Cokelat.

Teks ‘kota’ ditampilkan di sel B2. Daftar ini berisi item berikut:

• Sel B2, B4, B6, dan B7 berisi Chicago. • Sel B3 dan B5 berisi Houston.

Teks ‘jumlah pembeli’ ditampilkan di sel C3. Daftar ini berisi item berikut:

• Sel C2 berisi 11.020. • Sel C3 berisi 10.900. • Sel C4 berisi 20.765. • Sel C5 berisi 15.083. • Sel C6 berisi 21.032. • Sel C7 berisi 12.098.

SUMPRODUCT dengan logika

AND Logika AND bekerja dengan menggunakan tanda bintang (*). Mari kita hitung jumlah pembeli kue nanas di Chicago.

sebuah. Masukkan rumus ‘=SUMPRODUCT((A2:A7=”nanas”)*(B2:B7=”chicago”)*C2:C7)’ di sel mana pun, misalnya E2.
b. Tekan tombol ‘Enter’.

Outputnya adalah 44.150. Fungsi SUMPRODUCT mencari ‘nanas’ di kolom A (A2:A7) dan ‘Chicago’ di kolom B (B2:B7). Entri yang sama dengan ‘nanas’ dan ‘Chicago’ dirangkum.

Jadi, 11020+21032+12098=44150

Oleh karena itu, kue nanas dibeli oleh 44.150 pembeli di Chicago.

SUMPRODUCT dengan logika

OR Logika OR bekerja dengan menggunakan tanda plus (+). Mari kita hitung jumlah pembeli yang membeli kue nanas atau milik Chicago.

sebuah. Masukkan rumus ‘=SUMPRODUCT((A2:A7=”nanas”)+(B2:B7=”chicago”),C2:C7)’ di sel F2.
b. Tekan tombol ‘Enter’.

Outputnya adalah 124.148. Fungsi SUMPRODUCT mencari ‘nanas’ di rentang pertama (A2:A7) dan ‘Chicago’ di rentang kedua (B2:B7). Entri yang sama dengan ‘nanas’ atau ‘Chicago’ dirangkum.

Dengan kata lain, fungsi SUMPRODUCT menghitung entri yang salah satu kondisinya (A2:A7=”nanas” atau B2:B7=”chicago”) bernilai true.

Jadi, (2*11020)+(1*20765)+(1*15083)+(2*21032)+(2*12098)=124.148.

Fungsi excel SUMPRODUCT menghitung ‘nanas’ sebagai satu percobaan dan ‘Chicago’ sebagai entri lainnya. Jika baris berisi ‘nanas’ dan ‘Chicago’, baris tersebut dihitung sebagai dua entri. Ada total 8 sel yang memiliki ‘nanas’ atau ‘Chicago.’

Baris 2, 6, dan 7 memiliki kata ‘nanas’ dan ‘Chicago’. Jadi, untuk baris-baris ini , angka yang sesuai dari kolom C dikalikan dengan 2. Baris 4 dan 5 masing-masing memiliki ‘Chicago’ dan ‘nanas’. Jadi, untuk baris-baris ini, angka masing-masing kolom C dikalikan dengan 1.

Jadi, total 124.148 pembeli membeli kue pin eapple atau milik Chicago.

Catatan: Fungsi SUMPRODUCT juga dapat bekerja untuk logika AND dan OR secara bersamaan.

SUMPRODUCT Video Fungsi Excel

Artikel yang Direkomendasikan

Ini telah menjadi panduan untuk SUMPRODUCT di Excel. Di sini kita membahas cara menggunakan Fungsi SUMPRODUCT beserta contoh langkah demi langkah excel.. Anda juga dapat melihat fungsi Excel-

• SUMPRODUCT dengan Beberapa KriteriaSUMPRODUCT Dengan Beberapa KriteriaDi Excel, menggunakan SUMPRODUCT dengan beberapa Kriteria memungkinkan Anda untuk membandingkan array yang berbeda menggunakan beberapa kriteria.baca lebih lanjut
• Fungsi SUBTOTAL ExcelFungsi SUBTOTAL ExcelFungsi excel SUBTOTAL melakukan operasi aritmatika yang berbeda seperti rata-rata, produk, jumlah, standar deviasi, varians, dll., pada rentang yang ditentukan.baca lebih lanjut
• Fungsi AGREGAT Fungsi AGREGAT Fungsi AGREGAT di excel mengembalikan agregat dari tabel data atau daftar data yang diberikan.baca lebih lanjut
• Fungsi TANDA ExcelFungsi TANDA ExcelFungsi tanda di excel adalah fungsi Matematika/Trig yang mengembalikan tanda (-1, 0 atau +1) dari argumen numerik yang diberikan padanya.baca lebih lanjut
• Ulangi Pintasan di ExcelUlangi Pintasan Di ExcelDi Excel, kami memiliki opsi bernama ‘Undo’ yang dapat kami gunakan dengan menekan Ctrl + Z. Saat kami membatalkan tindakan tetapi kemudian menyadari bahwa itu bukan kesalahan, kami dapat membatalkan tindakan membatalkan dan kembali ke poin aslinya, menggunakan “Redo.”baca lebih lanjut